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)
....
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)
....