Filename in VBA variable

[Lothrian]

I am trying to build an analytics tool, I fully expect the end users of the tool to change the file name of the tool for their own purposes.

How would I write the file name as a variable when referencing the file the macro is imbedded into.

EG. the tool is copying a data sets from a location to the existing workbook, *names changed for anonymity*

Sub Example()

'Copy data from "Sheet 1" Download Sheet
strFile = Application.GetOpenFilename()
Workbooks.Open (strFile)
Filename = ActiveWorkbook.Name
Sheets("Sheet 1").Copy After:=Workbooks("Book 1.xlsm").Sheets(2)
Workbooks(Filename).Close savechanges:=False
Workbooks("Book 1.xlsm").Activate
Worksheets("Sheet 1").Activate
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Name = "Sheet 2"
Sheets("Sheet 2").Select

End Sub

... if the user was to change the save name of the workbook from "Book 1.xlsm" the macro will fail to run,

Can I set this as a variable so the code will look at the existing save name and use that as the reference?

EG
Dim Filename As ....
....

'Copy data from "Sheet 1" Download Sheet
strFile = Application.GetOpenFilename()
Workbooks.Open (strFile)
....

 

[Joe4]

Welcome to the Board!

If you put this code at the very beginning of your procedure, it will dynamically capture the workbook with macros:

Dim wb as Workbook
Set wb = ThisWorkbook

and then you can refer/go back to it later in your code like this:

wb.Activate

So you do not need to know the name of the workbook at all, but if you did want to see what it was, you could use:

MsgBox wb.Name

 

[Lothrian]

Welcome to the Board!

If you put this code at the very beginning of your procedure, it will dynamically capture the workbook with macros:

Dim wb as Workbook
Set wb = ThisWorkbook

and then you can refer/go back to it later in your code like this:

wb.Activate

So you do not need to know the name of the workbook at all, but if you did want to see what it was, you could use:

MsgBox wb.Name

Lovely thanks, I'll give that a try.