filename as variable

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
 
D

Dave Peterson

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.



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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automate Copy and Paste 12
Simple Help ASAP macro to fill range... 4
Changing filename as variable 0
Code not woring - please help!!! 4
Excel Data Combine 3
Modify RDB code with find and offset 6
Using Sheets.Add after and want to keep a sheet as the first sheet 2
Excel FileName as variable in macro? 0

Top