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").Va lue


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JC" wrote in message
...
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,

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
-----Original Message-----
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


"JC" wrote in
message
...
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" wrote...
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.

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

--
To top-post is human, to bottom-post and snip is sublime.

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)

"JC" wrote in message
...
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
-----Original Message-----
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


"JC" wrote in
message
...
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,


.

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
-----Original Message-----
"JC" wrote...
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.

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

--
To top-post is human, to bottom-post and snip is sublime.
.

Alright, I know what you're asking. Do it like this:

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

- Piku

--
Message posted from http://www.ExcelForum.com

"JC" wrote...
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.
...

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

--
To top-post is human, to bottom-post and snip is sublime.

"pikus <" wrote...
Alright, I know what you're asking. Do it like this:

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

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.

--
To top-post is human, to bottom-post and snip is sublime.