-1

enter image description here

The code keeps producing this error

seems to be this line "

xlBook.SaveAs Filename:="Template 1", FileFormat:=xlOpenXMLTemplateMacroEnabled

"

trying to run an external VBS script to open excel file run macro and save as self

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook


  Set xlApp = CreateObject("Excel.Application")
  Set fname = "mac PIW" 
  Set xlBook = xlApp.Workbooks.Open("C:\Users\14432\Documents\Custom Office Templates\Template 1.xltm") 
  xlApp.Run "Macro"
  xlBook.SaveAs Filename:="Template 1", FileFormat:=xlOpenXMLTemplateMacroEnabled
  xlBook.Close
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Yaki Just
  • 1
  • 1

1 Answers1

0

As well as the named parameters problem noted by Geert Bellekens, you have a number of other problems:

  • VBScript won't know what xlOpenXMLTemplateMacroEnabled is ... replace it with its numeric value of 53
  • You are using a blanket On Error Resume Next which is hiding problems in your script
  • You use Option Explicit and also have an undeclared variable fname
  • You say "and save as self" but when you call Workbooks.Open you are creating a new Workbook from the Template instead of opening the Template itself

Proposed code (assuming that you do want to actually re-save the Template itself, not a Workbook created from the Template):

Option Explicit

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook

  Set xlApp = CreateObject("Excel.Application")
  ' force the Template to open as itself instead of creating a new Workbook
  Set xlBook = xlApp.Workbooks.Open("C:\Users\14432\Documents\Custom Office Templates\Template 1.xltm", , , , , , , , , True) 
  ' in case there is a problem while running the macro or saving the file, we still want to ensure we quit Excel
  On Error Resume Next 
  xlApp.Run "Macro"
  xlBook.Save
  xlBook.Close False
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub

Hope this helps!

JohnM
  • 2,422
  • 2
  • 8
  • 20
  • Please do not suggest placing magic numbers in place of constants, just define them yourself, i.e. `Const xlOpenXMLTemplateMacroEnabled = 53`. See [How to use excel built-in constant in VBS](https://stackoverflow.com/q/39941922). Not sure why you thought you needed to answer this question when it was already flagged with multiple duplicate targets. – user692942 Nov 16 '22 at 18:18
  • 1.I was providing the questioner with a working script ... not a perfect script. 2. I answered the question before it was closed. It isn't possible to answer closed questions. – JohnM Nov 16 '22 at 19:55
  • I said it was flagged not closed, it's clear to see that there are duplicate targets before a question is closed. – user692942 Nov 16 '22 at 19:57
  • There were no duplicate targets when I opened the web page to answer the question. As my crystal ball is currently not working then I could not have known it was going to be flagged, closed or whatever at some future point. – JohnM Nov 16 '22 at 20:50