Firstly, Thanks to Excel Max, Fluffy, for looking into the problem I have.
If I can offer a bit of background.
I open a Template called Till Takings V2.xltm as it has the main macros I use to achieve what I want.
Reason for Opening the template is to get the folder location to save all files created in the same place that the template is in. There is command in the macro that defines the path using the command
VBA Code:
fPath = ThisWorkbook.Path & "\"
Running the macro does the following:-
Asks the User to enter data using an InputBox and storing the entered data in a field called myYear.
Opens 4 templates with an extension “xltx” individually using command
VBA Code:
Workbooks.Open fPath & "Bar Area Taking Print.xltx"
Populate a specific sheet in the Open Workbook, save it as a normal Excel file using the command and close the opened template with the command:-
VBA Code:
ActiveWorkbook.SaveAs Filename:=fPath & "Bar Area Taking Print.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
I am then left with the Original Template that was opened.
I then create 12 copies of the template and save them individually with the command
VBA Code:
ActiveWorkbook.SaveAs Filename:=fPath & "[COLOR=rgb(226, 80, 65)]01 January.xlsm[/COLOR]", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Please note
01 January.xlsm is replaced with 02 February.xlsm, 03 March.xlsm etc etc etc.
I open the saved 01 January.xlsm using the command
VBA Code:
Workbooks.Open fPath & "01 January.xlsm"
call a macro to manipulate the file. I then save 01 January.xlsm as 01 January.
xlsx by using the command
VBA Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fPath & "01 January.[COLOR=rgb(0, 0, 0)]xlsx[/COLOR]", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
I open the saved 02 February (using Workbooks.Open fPath & "02 February.xlsm") and call a macro (same as that called by 01 January) to manipulate the file.
I then save 02 February.xlsm as 02 February.
xlsx by using the command
VBA Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fPath & "02 February.[COLOR=rgb(0, 0, 0)]xlsx[/COLOR]", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
The code involved in the macro to manipulate the file is quite long, and I therefore thought that if I were to create a new Macro and called E_DoRepeatWork_AllWorkbooks I could refine the code by just having:-
VBA Code:
Open Workbooks.Open fPath & "01 January.xlsm"
Call E_DoRepeatWork_AllWorkbooks
Open Workbooks.Open fPath & "02 February.xlsm"
Call E_DoRepeatWork_AllWorkbooks
Open Workbooks.Open fPath & "03 March.xlsm"
Call E_DoRepeatWork_AllWorkbooks
Macro E_DoRepeatWork_AllWorkbooks, is where I am trying to save the .XLTM to a .XLSX and I am getting problems.
The fact that I can issue the command
VBA Code:
ActiveWorkbook.SaveAs Filename:=fPath & "01 January.xlsx", FileFormat:=xlOpenXMLWorkbook
to save a file I basically have a loop and therefore cannot hard code the file Name.
I thought using the command
VBA Code:
ActiveWorkbook.SaveAs Filename:=fPath & ActiveWorkbook.Name&".xlsx", FileFormat:=xlOpenXMLWorkbook
Would resolve the issue of giving a fully qualified file name.
I hope the above explains what I am trying to achieve and why I need to resolve the command
VBA Code:
ActiveWorkbook.SaveAs Filename:=fPath & ActiveWorkbook.Name&".xlsx", FileFormat:=xlOpenXMLWorkbook
To save the active workbook with as .xlsx.
I can get most probably get round it by moving the save command back to where it was with a full qualified name if the worse came to it.
The last thing that is done in the macro is to delete the the .xltm files.
Hope that the above makes sense.
Hoping you can help.
