Macro needs to recognize existing filename

[SkoobiDrew]

Hello. Please forgive if this has an obvious solution - I am fairly new at the VBS/macro game.

I am creating a "template" file for other users. They will likely rename the file as needed. The macro I have written accesses another file to extract some data, then needs to switch back to the "template" file to place the data. If the user has renamed the file, what command can I put there so it switches back?

Recording the macro generates a "Windows(example filename).Activate" type command, but I need the example filename to be whatever the user has named their file.

Any help/tips would be greatly appreciated. Thanks!

[Paul_Hossler]

I usually do something like this. My experience is that I make fewer mistakes by being very explicit and use WB variables. I never rely on ActiveWorkbook




Option Explicit
Sub Example()
Dim wbData As Workbook, wbTemplate As Workbook

'remember the user's wb
Set wbTemplate = ThisWorkbook

'open the data wb and set it into a variable
Set wbData = Workbooks.Open ("E:\Data.xlsx")

'copy the data from Data.xlsx to the user's wb
'just an example
Call wbData.Worksheets("Data").Range("A1:Z26").Copy(wbTemplate.Worksheets("Working").Range("B2"))


'close data, not saving
Call wbData.Close(False)
End Sub

[SkoobiDrew]

Paul, many many thanks! That was the ticket!

[snb]

If you use With ... End With you won't need any variables at all:


Sub M_snb()
with Getobject("E:\Data.xlsx")
thisworkbook.sheets("working").range("B2:AA27")=.sheets("Data").Range("A1:Z26").Value
.close 0
end with
End Sub