Workbook name in VBA doesn't match filename.xls

[Guest]

I have a workbook named ABC.xls, in that workbook are several macros that
reference the currently named workbook ... ABC.xls.
Now, my users rename the workbook to 123.xls.
When they go to run the macro in the newly named 123.xls workbook the macro
fails because there are references to the old workbook name of ABC.xls.
Is there a way to avoid this .. one note, having the user NOT rename the
workbook is not an option.
Is there a way to use a variable to reference the workbook no matter what it
is named, pehaps ?
Thanks in advance,
Joe

 

[Andy Pope]

Hi,

The object Thisworkbook will refer to the workbook containing the code.
So you can use this to modify your code and the way it references the
workbook.

If you need further assistance it would help if you posted an example of
your code in which you use the reference to a specific workbook.

Cheers
Andy

 

[Guest]

Sub routine()
MsgBox (ThisWorkbook.Name)
End Sub

 

[Guest]

Hi Andy,
Thanks for the quick reply... here is a sample of the VBA code.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/15/2007 by jc
'

' This macro resides in a workbook named ABC.xls.. and will eventually
jump/goto another workbook
' named DEF.xls... then the macro goes back to the workbook named ABC.xls.
' However, if I rename ABC.xls to a filename 123.xls.... this macro will
run but break
' on the line that reads "Windows ("ABC.xls").Activate because the
workbook is now called 123.xls

Range("A1").Select
ActiveCell.FormulaR1C1 = "This is the ABC workbook."
Range("A2").Select
Windows("DEF.xls").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"This is the DEF Workbook… now jumping back to the ABC workbook."
Range("A2").Select

' macro breaks here IF the workbook is not called ABC.xls
Windows("ABC.xls").Activate
ActiveCell.FormulaR1C1 = "Back to ABC workbook."
Range("A3").Select
End Sub

 

[Dave Peterson]

You can use:
Thisworkbook.activate
instead of:
Windows("ABC.xls").Activate

But better would be to stop the selects and activates.

'....

Activesheet.range("a1").value = "This is the ABC workbook."

Workbooks("Def.xls").worksheets("somesheetname").range("A1").value _
= "This is the DEF Workbook… now jumping back to the ABC workbook."

Activesheet.range("A2").value = "Back to ABC workbook."

End Sub

 

[iliace]

In the general case, it is best not to refer to anything with a string
literal. ThisWorkbook.Name is a good method, as people have pointed
out. If you must refer to multiple files, use string constants, or in
your master workbook, set up a sheet where you have named ranges
containing the strings, then initialize them at application startup -
Workbook_Open() or whatever your entry point is.