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!

 

[admiral100]

Glad you solved that....when you click "debug" which line was in yellow ?

 

[WildBurrow]

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!

I had a similar issue and was fighting it for three weeks. My code was using shared drive file path. First it created a Parent folder, four subfolders, then renamed the workbook, placing in the 'Reports' subfolder.

After reading this post, I decided to separate the code which created the folders from the code responsible for renaming the workbook.

To confirm, I had permissions to write the the shared drive and all cell values were cleared of bad characters and spaces prior to being referenced in my code.

My Original Code:

Option Explicit
Sub CreateFoldersWorkbook()

'Dims listed here
'Set references here
    
    'Create incident folders
    ParentFolder = worksheets("CountyTwps").Range("K2").Value        
    SubFolder = rng1.Value
    NewPath = ParentFolder & "\" & SubFolder
              
    If Dir(NewPath, vbDirectory) = "" Then
        Shell ("cmd /c mkdir """ & NewPath & """")
    End If
        
    'Create subfolders
    path = "NewPath\"
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Correspondence")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Labs_Manifests_Maps")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Photos")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Reports")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "SitReps")
   
    'Rename Workbook
    strDefaultPath = rng6.Value 'Subfolder file path
    strDefaultName = rng4.Value & ".xlsm" 'workbook name
    
    ActiveWorkbook.SaveAs filename:=strDefaultPath & strDefaultName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

My New Code that Fixed the Issue:

Sub CreateFoldersWorkbook()

'Dims listed here
'Set references here

   'Create incident folders
    ParentFolder = worksheets("CountyTwps").Range("K2").Value        
    SubFolder = rng1.Value
    NewPath = ParentFolder & "\" & SubFolder
              
    If Dir(NewPath, vbDirectory) = "" Then
        Shell ("cmd /c mkdir """ & NewPath & """")
    End If
        
    'Create subfolders
    path = "NewPath\"
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Correspondence")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Labs_Manifests_Maps")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Photos")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "Reports")
    Shell ("cmd /c mkdir """ & NewPath & "\" & "SitReps")

     Call WkbRename
End sub

New Subroutine called in above code:

Option Explicit
Sub WkbRename()

Dims listed here     
'Set references here
     
ParentFolder = rng1
SubFolder = rng2
wbfilepath = rng3
     
wbfilepath = ParentFolder & SubFolder & "\" & "Reports" & "\"
wbfilename = rng4.Value
          
    ActiveWorkbook.SaveAs filename:=wbfilepath & wbfilename & ".xlsm", FileFormat:=52

End Sub