Pass a variable from python to vba

Question

I am trying to convert some .csv documents to .xlsx. I do this with the VBA code shown bellow and it works fine, but I want to have path of the file folderes defined as a variable in python and linked to VBA.

Sub CSVtoXLSX()


Dim CSVfolder As String, _
    XlsFolder As String, _
    fname As String, _
    wBook As Workbook

 CSVfolder = "pathcsv"
 XlsFolder = "pathxlsx"

 fname = Dir(CSVfolder & "*.csv")

 Do While fname <> ""
    Set wBook = Workbooks.Open(CSVfolder & fname)
    wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), xlOpenXMLWorkbook
    wBook.Close False
    fname = Dir
 Loop

End Sub

Also it runs fine when run from python with win32 addon. The problem arises when I try to define a variable and pass it to VBA.

python code:

import win32com.client
CSVfolder = "csvpath"
XlsFolder = "xlsxpath"

xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename = r"macro.xlsm", ReadOnly=1)
xl.Application.Run('CSVtoXLSX', CSVfolder, XlsFolder)
##    xl.Application.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
xl.Application.Quit() # Comment this out if your excel script closes
del xl

I have found this thread with almost the same question, but I get the error:

Traceback (most recent call last):
  File "C:/Users/.../PycharmProjects/untitled6/exc.py", line 7, in <module>
    xl.Application.Run('CSVtoXLSX', CSVfolder, XLsFolder)
  File "C:\Users\...\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x9\_Application.py", line 370, in Run
    return self._ApplyTypes_(259, 1, (12, 0), ((12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)), 'Run', None,Macro
  File "C:\Users\...\AppData\Local\Programs\Python\Python38\lib\site-packages\win32com\client\__init__.py", line 467, in _ApplyTypes_
    self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352562), None)

what am I doing wrong?

score 0 · Answer 1 · answered Apr 25 '20 at 14:55

You may need to change your vba function to accept arguments for CSVfolder and Xlsfolder with default values you specify in the body of the function.

Sub CSVtoXLSX (Optional CSVfolder As String = "csvpath", Optional Xlsfolder As String = "xlspath")

But if all you need to do it convert csv to xlsx, there is a straightforward way to do so in python.

from pathlib import Path
import pandas as pd


CSVfolder = Path("pathcsv")
XlsFolder = Path("pathxlsx")

for f in CSVfolder.glob("*.csv"):
    df = pd.read_csv(f)
    xlsfn = XlsFolder / f.with_suffix('.xlsx').name
    df.to_excel(xlsfn, index=False)

Pass a variable from python to vba

1 Answers1