How would I make this program work? VBA

Discussion in 'OT Technology' started by Acoustyk, Jul 21, 2008.

  1. Acoustyk

    Acoustyk Jesus didn't save the dinosaurs lol

    Joined:
    May 31, 2007
    Messages:
    224
    Likes Received:
    0
    Location:
    The vicinity of Richmond, VA
    I am making a timesheet program and I want to convert a week on a monthly chart to a weekly chart. To do this I have coded in a module something similar to this:

    Sub Open_Excel_Wb()
    Workbooks.Open Filename:= _
    "C:\Documents and Settings\Garbo\My Documents\Book1.xls"
    Sheets("Sheet2").Select
    Range("A1").Select
    Range("A1").Value = DateValue(Now())
    End Sub

    How would I then make Range("A1").Value = a value on a worksheet in an entirely different .xls file

    Worksheets("CSTreport").range("N" & i).Value I have been using something similar to this within a workbook but I do not know what to add to specify it's pathway on the computer. (i.e. C:\... whatever)

    P.S. Not only am I new to programming but I'm relatively new to forums. (at least posting) So try not to flame me to much :)
     
    Last edited: Jul 21, 2008
  2. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    Try [WorkbookPath\Filename.xls]Sheet2
    ex
    Worksheets("[WorkbookPath\Filename.xls]Sheet2").blah blah


    I didn't actually try this out, but thats the convention with pan-workbook cells
     
  3. Acoustyk

    Acoustyk Jesus didn't save the dinosaurs lol

    Joined:
    May 31, 2007
    Messages:
    224
    Likes Received:
    0
    Location:
    The vicinity of Richmond, VA
    Sub wbopen()


    Dim ws As String
    Dim wsrich As String
    Dim pnumber As String
    Dim familyname As String
    Dim firstname As String
    Dim csnumber As String
    Dim calendarweek As Integer
    Dim year As Integer
    Dim i As Integer

    i = 12
    wsrich = Workbooks("C:\Documents and Settings\longest\Desktop\ Project Folder\Weekly Report models\Most recent model\newapproach2replaced8updatesaveex.xls").Sheets("TimesheetR")
    ws = Workbooks("C:\TOOL\Account\WeeklyReport.xls").Sheets("Timesheet")

    pnumber = wsrich.range("c9")
    familyname = wsrich.range("J9")
    firstname = wsrich.range("S9")
    year = wsrich.range("BA9")
    calendarweek = 0


    Workbooks.Open Filename:="C:\TOOL\Account\WeeklyReport.xls"
    Sheets("Timesheet").Select
    range("A1").Select
    range("A1").Value = pnumber
    End Sub

    This is what it's become since yesterday. I'm getting subscript 9 errors for the ws and wsrich definitions and invalid compile errors for the use of wsrich in the other variables definitions. Maybe that gives you more info?
     
  4. Acoustyk

    Acoustyk Jesus didn't save the dinosaurs lol

    Joined:
    May 31, 2007
    Messages:
    224
    Likes Received:
    0
    Location:
    The vicinity of Richmond, VA
    I actually got this working so... /thread
     

Share This Page