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



		Code:
__


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:



		Code:
__


 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

admiral100 said:


> 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

admiral100 said:


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


----------



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



		Code:
__


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

johnnyjoe1076 said:


> 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.
> 
> 
> 
> Code:
> __
> 
> 
> 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:*


		VBA 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:*


		VBA Code:
__


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:*


		VBA 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


----------

