VBA Saving .xltm template as .xlsm

[johnnyjoe1076]

I have searched but I have not found a solution that has worked. I continually get a "runtime error '1004' method 'saveas' of object '_workbook' failed" in my .xltm file's code while it is attempting to save it as a .xlsm. I have tried different variations and methods but nothing seems to work. The following is the latest version of failure.

twb.SaveAs "H:\Continuous Improvement\Purchase Requisitions\Submitted Requisitions\" & prnum & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

prnum simply indicates the requisition number created for this file. I have verified that this aspect is working properly and not part of the problem. Any assistance would be greatly appreciated!

 

[johnnyjoe1076]

even same failure with the code as the following:

 Dim filename As String    filename = "H:\Continuous Improvement\Purchase Requisitions\Submitted Requisitions\" & prnum & ".xlsm"
    Application.DisplayAlerts = False
    'save file in folder
    Application.ScreenUpdating = True
    twb.SaveAs filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[admiral100]

Hi, try this :

twb.SaveAs "H:\Continuous Improvement\Purchase Requisitions\Submitted Requisitions" & prnum & ".xlsm", _
FileFormat:=52, CreateBackup:=False

or

twb.SaveAs "H:\Continuous Improvement\Purchase Requisitions\Submitted Requisitions" & prnum & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled = 52, CreateBackup:=False

 

[johnnyjoe1076]

I also get failures with those codes. I tried them again for kicks and same thing...

 

[admiral100]

Enter these lines to the beginning of your code


Dim twb As Workbook
Set twb = ActiveWorkbook

BTW , I assume you have write permissions to the directory on the H drive

 

[johnnyjoe1076]

Enter these lines to the beginning of your code


Dim twb As Workbook
Set twb = ActiveWorkbook

BTW , I assume you have write permissions to the directory on the H drive

I do have the rights, and I have the coding, as you stated, already as part of it, except "Set twb = ThisWorkbook". I switched it to ActiveWorkbook just to see and still the same error.

 

[admiral100]

Seems to me like folder location is wrong....Please double check whether the folder location is correct

I test with c:\test\ and it's work good.

 

[johnnyjoe1076]

Seems to me like folder location is wrong....Please double check whether the folder location is correct

I test with c:\test\ and it's work good.

I'm ahead of you, I tried that, I tried different folder locations, I tried recreating the folder, I tried different formats. I even tried changing the original file from an .xltm to .xlsm and still the same issue. I'm at a total loss. I have never had anything like this before with any save code.

 

[johnnyjoe1076]

Here's a strange bit of additional information. Whenever the run-time error occurs, I click "debug" and then I continue the macro it works! But this only happens if I click debug and then continue. I even tried simply rigging the macro to rerun the code if failed and it doesn't work.

 

[johnnyjoe1076]

Well I don't know exactly why, but I moved the "save code" to an earlier part in the code and it worked. Obviously there is issue somewhere else in my code hindering it. I'll have to trial and error it until I figure that aspect out.