Saving .xltm as .xlsx fails using VBA

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have a template which is Macro Enabled ending with .xltm

Through VBA (in a macro), I am attempting to save the template as an ordinary file with a new name. The file is currently defined as the Active Workbook and I am using the following command:-

VBA Code:
ActiveWorkbook.SaveAs fPath & ".xlsx", 51.

I have also tried using the following command:-

VBA Code:
ActiveWorkbook.SaveAs Filename:=fPath & ActiveWorkbook.Name&".xlsx", FileFormat:=xlOpenXMLWorkbook

I get a run-time error 1004.

Please note
VBA Code:
fPath is defined as Dim fPath As String, fPath = ThisWorkbook.Path & "\"

Where am I going wrong? I am a newbie to VBA so please be gentle.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
Hello Kayslover, you can't workbook template file save as workbook with VBA code.
Do as error say.
Save your workbook manually as macro enabled file (.xlsm) and run code from there.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
Is that code in the template file, or a different file?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
I think whatever, .xlsm or .xlsx will be treated as template file.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Error...
I think whatever, .xlsm or .xltx will be treated as template file.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
Error again...
I think whatever,.xltm or .xltx will be treated as template file.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Please do not make multiple posts for the same thing, in future if you make a mistake just edit your post.

Not quite sure what you mean, but it's perfectly possible for VBA to use saveas on a template file.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
Sorry, Mr. Fluff for repeating posts,
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
Are you trying to save the xlsm files as xlsx?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top