Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filename as variable
Hi,
I am creating a job tracking journal which will link individual job journals to a job tracking sheet. The code i wrote is below. Basically it asks the user to choice the journal that they wish to add to the summary sheet then links to the proper data. This way was the journal is updated, so is the summary sheet. My current code works exactly as i wish except for one problem, it requires all of the journals to have the name "journal.xls", but we need to have dynamic file names, one job may be called "Journal - Transformer Install.xls" or "Journal - Pole Set.xls" etc. As you can see in my code, I reference the other sheet using: ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C3". How can I change it so that rather than referencing [Journal.xls], it referances OPENFILE, which is the file that was selected through the Application.GetOpenFilename command. I'm a power engineering guy who is trying to track all the district's jobs so this is all kind of new territory for me so any help is appreciated. Thanks!!!! Sean Sub Populate() MsgBox "Please select the Project Journal for the project that you would like to add, make sure the file is NOT currently open.", vbOKOnly OPENFILE = Application.GetOpenFilename("Project Journal (*.xls),*.xls", , "Open a Project Journal...") Workbooks.OpenText Filename:=OPENFILE Windows("Project Summary NEW.xls").Activate Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C3" Range("B3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R1C3" Range("C3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R5C5" Range("D3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R2C6" Range("E3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R1C5" Range("F3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R12C3" Range("G3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R8C3" Range("H3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R10C3" Range("I3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R11C3" Range("J3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C9" Range("K3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R6C9" Range("L3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R3C5" Range("M3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R2C5" Range("N3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R11C6" Range("O3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R6C6" Range("P3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C5" Windows("Journal.xls").Activate ActiveWorkbook.Close False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
filename as variable
Sometimes, you can use variables to refer to those things that the user can pick
and choose. But I'm kind of confused. You used .opentext, but you gave the user *.xls in the .getopenfilename line. I'm guessing that workbooks.open (not .opentext) should have been used. And you don't have to select something to work with it. You can just assign formulas directly to a range. Option Explicit Sub Populate() Dim resp As Long Dim OpenFileName As Variant Dim JournalWkbk As Workbook Dim JournalWks As Worksheet Dim ProjWks As Worksheet resp = MsgBox(prompt:="Please select the Project Journal for the project that you" _ & " would like to add, make sure the file is NOT currently open.", _ Buttons:=vbOKCancel) If resp = vbCancel Then Exit Sub End If Set ProjWks = ActiveSheet 'start on the correct sheet! OpenFileName = Application.GetOpenFilename(Filefilter:="Project Journal,*.xls", _ Title:="Open a Project Journal...") If OpenFileName = False Then Exit Sub 'user hit cancel End If Set JournalWkbk = Workbooks.Open(Filename:=OpenFileName, ReadOnly:=True) Set JournalWks = Nothing On Error Resume Next Set JournalWks = JournalWkbk.Worksheets("Journal") On Error GoTo 0 If JournalWks Is Nothing Then MsgBox "The Journal worksheet is missing from the journal workbook!" Exit Sub End If With ProjWks .Rows(3).Insert .Range("A3").FormulaR1C1 = "=" & JournalWks.Cells(4, 3) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("b3").FormulaR1C1 = "=" & JournalWks.Cells(1, 3) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("C3").FormulaR1C1 = "=" & JournalWks.Cells(5, 5) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("D3").FormulaR1C1 = "=" & JournalWks.Cells(2, 6) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("E3").FormulaR1C1 = "=" & JournalWks.Cells(1, 5) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("F3").FormulaR1C1 = "=" & JournalWks.Cells(12, 3) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("G3").FormulaR1C1 = "=" & JournalWks.Cells(8, 3) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("H3").FormulaR1C1 = "=" & JournalWks.Cells(10, 3) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("I3").FormulaR1C1 = "=" & JournalWks.Cells(11, 3) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("J3").FormulaR1C1 = "=" & JournalWks.Cells(4, 9) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("K3").FormulaR1C1 = "=" & JournalWks.Cells(6, 9) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("L3").FormulaR1C1 = "=" & JournalWks.Cells(3, 5) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("M3").FormulaR1C1 = "=" & JournalWks.Cells(2, 5) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("N3").FormulaR1C1 = "=" & JournalWks.Cells(11, 6) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("O3").FormulaR1C1 = "=" & JournalWks.Cells(6, 6) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) .Range("P3").FormulaR1C1 = "=" & JournalWks.Cells(4, 5) _ .Address(external:=True, _ ReferenceStyle:=xlR1C1) End With JournalWkbk.Close savechanges:=False End Sub Watch out for the .cells(x, y). I think I got them all correct, but you'll want to double check them. And .address will return things like $A$1. ..address(referencestyle:=xlr1c1) will return things like R15C22 ..address(external:=true) will include the workbook name and worksheet name. Excel will figure out the syntax. wrote: Hi, I am creating a job tracking journal which will link individual job journals to a job tracking sheet. The code i wrote is below. Basically it asks the user to choice the journal that they wish to add to the summary sheet then links to the proper data. This way was the journal is updated, so is the summary sheet. My current code works exactly as i wish except for one problem, it requires all of the journals to have the name "journal.xls", but we need to have dynamic file names, one job may be called "Journal - Transformer Install.xls" or "Journal - Pole Set.xls" etc. As you can see in my code, I reference the other sheet using: ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C3". How can I change it so that rather than referencing [Journal.xls], it referances OPENFILE, which is the file that was selected through the Application.GetOpenFilename command. I'm a power engineering guy who is trying to track all the district's jobs so this is all kind of new territory for me so any help is appreciated. Thanks!!!! Sean Sub Populate() MsgBox "Please select the Project Journal for the project that you would like to add, make sure the file is NOT currently open.", vbOKOnly OPENFILE = Application.GetOpenFilename("Project Journal (*.xls),*.xls", , "Open a Project Journal...") Workbooks.OpenText Filename:=OPENFILE Windows("Project Summary NEW.xls").Activate Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C3" Range("B3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R1C3" Range("C3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R5C5" Range("D3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R2C6" Range("E3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R1C5" Range("F3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R12C3" Range("G3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R8C3" Range("H3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R10C3" Range("I3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R11C3" Range("J3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C9" Range("K3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R6C9" Range("L3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R3C5" Range("M3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R2C5" Range("N3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R11C6" Range("O3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R6C6" Range("P3").Select ActiveCell.FormulaR1C1 = "=[Journal.xls]Journal!R4C5" Windows("Journal.xls").Activate ActiveWorkbook.Close False End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove .xls from a variable filename | Excel Programming | |||
Filename variable | Excel Programming | |||
Converting a Variable Filename to a Constant Filename | Excel Programming | |||
Doing a save with a variable filename? | Excel Programming | |||
Filename Variable? | Excel Programming |