S
sck352
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
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