Search code examples
pythonexcelvbapywin32win32com

pywin/win32com file access error when saving a macro-enabled spreadsheet


I have the following code that reads an existing Excel file, adds a macro, and then tries to save it to a different macro-enabled excel file.

import win32com.client as win32
import comtypes, comtypes.client

name="//path//to//test1.xlsx"
sheetName = "Sheet1"

xlOpenXMLWorkbookMacroEnabled = 52
xl=win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible=True
ss = xl.Workbooks.Open(name, False, True)
ss.Worksheets(sheetName).Activate()
xlmodule = ss.VBProject.VBComponents.Add(1)  
VBAcode='''sub VBAMacro()
       Range("A1").Value = 1
      end sub'''

xlmodule.CodeModule.AddFromString(VBAcode)

#xl.Run(name+'!VBAMacro')

ss.SaveAs(Filename=name[:-5] + '-macro.xlsm', FileFormat=xlOpenXMLWorkbookMacroEnabled)

xl.Quit()

In doing this, I have been taking into consideration the questions about xlOpenXMLWorkbook here and here. However, when I try to run this I get the error:

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Microsoft Excel cannot access the file ...

I have tested this code without the SaveAs line and it reads the file ok, so I don't understand why it doesn't work. Any ideas?


Solution

  • I realised the origin of the problem was the way the file path is processed. After several trials and errors, it seems the right way to write paths (at least in Windows OS) is using double backslashes: "C:\\Users\\blablah\\".