Dynamic filename

  • Thread starter Thread starter JC
  • Start date Start date
J

JC

mystring = Workbooks("SupportTemplate.xls").Sheets
("WorkOrders").Range("I2").Value
In the above code, is there a way to make the filename of
the spreadsheet (SupportTemplate.xls) link dynamically to
the name of the spreadsheet. Thus, if the spreadsheet
name changes, the reference in the code changes as well.

Thanks,
 
JC,

It depends on what workbook you want to reference. If you want
the workbook that is presently active, use

mystring = ActiveWorkbook.Sheets("WorkOrders").Range("I2").Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
How about if I have two worksheets open and the macro is
selecting between the two worksheets. This will work
fine if I only have one worksheet open, but if I have two
I would have to refer to the spreadsheet by name to avoid
confusion. I'm trying to use this spreadsheet as a
template for users that don't know VBA code so automation
is key.

Thanks for the tip.
JC
 
mystring = Workbooks("SupportTemplate.xls").Sheets
("WorkOrders").Range("I2").Value
In the above code, is there a way to make the filename of
the spreadsheet (SupportTemplate.xls) link dynamically to
the name of the spreadsheet. Thus, if the spreadsheet
name changes, the reference in the code changes as well.
Click to expand...

Use workbook-type object variables.

Dim wba As Workbook, wbb As Workbook
Set wba = Workbooks("foo.xls")
Set wbb = Workbooks("bar.xls")
mystring = wba.Sheets("WorkOrders").Range("I2").Value
wbb.Sheets("WOSummary").Range("X99").Value = mystring
 
Create object variables that point to each workbook and reference via these
variables

Set oWB1 = Workbooks("Book1.xls")
Set oWB2 = Workbooks("Book2.xls")

then you have variables that you can use it your code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That's a good idea, but will this work if I do a save as
and change the name of the file from foo.xls to
foo1.xls? My variables will still be pointing to foo.xls
thus invalidating my code??? I want to use this as a
template and have users change the filename when they
have a new customer. I don't want them to have to change
the code references.
Thanks for the tip.
JC
 
Alright, I know what you're asking. Do it like this:

foo = ThisWorkbook.Name
Workbooks(foo).Worksheets("Sheet1) blah blah blah...

- Piku
 
That's a good idea, but will this work if I do a save as
and change the name of the file from foo.xls to
foo1.xls? My variables will still be pointing to foo.xls
thus invalidating my code??? I want to use this as a
template and have users change the filename when they
have a new customer. I don't want them to have to change
the code references.
Click to expand...
...

At some point, simple testing becomes a good idea. Add the following macro to a
new workbook then run the macro. What's displayed in the message boxes?


Sub foo()
Dim wb As Workbook, ofn As String

If Dir(Environ("TEMP") & "\foobar.xls") <> "" Then
Kill Environ("TEMP") & "\foobar.xls"
End If

Set wb = ActiveWorkbook
wb.Save
ofn = wb.FullName
MsgBox ofn
wb.SaveAs FileName:=Environ("TEMP") & "\foobar.xls"
MsgBox wb.FullName
Workbooks.Open FileName:=ofn
wb.Close SaveChanges:=False
End Sub
 
Alright, I know what you're asking. Do it like this:

foo = ThisWorkbook.Name
Workbooks(foo).Worksheets("Sheet1) blah blah blah...
Click to expand...

And if the OP then runs

Workbooks(foo).SaveAs Filename:=SomethingWithDifferentBaseFilename

the next time statement referencing Workbooks(foo) will throw a runtime error.
Using workbook-type object variables tracks the workbook as long as it's open.
 
Back
Top