Search code examples
python-3.xxlsxwriterxlwings

Unable to save files via VBA when I invoke Python code

I am unable to save excel file using xlsxwriter when I invoke python via VBA

For example following VBA code is suppose to trigger the "mymodule.py"

Sub SampleCall()
 RunPython ("import mymodule;mymodule")
End Sub

The following is mymodule.py

import pandas as pd
import xlsxwriter
import xlwings as xw

wb = xw.Book.caller()
excel_file ="sample_file.xlsx"
df = pd.DataFrame(np.random.randn(5,2),index=(0,10,2), column=list('AB'))
writer = pd.ExcelWriter(excel_file,engine='xlsxwriter')
df.to_excel (writer,sheet_name='sample')
writer.save()
writer.close()

I am expecting the file called sample_file.xlsx to be saved but I am unable to locate the file when i use vba to trigger above code.

I will appreciate any help in this regards

Solution

  • Give it an explicit path so that you are sure where it is being saved:

    excel_file ="C:\mydirectory\sample_file.xlsx"

    otherwise, the file could be saved somewhere you aren't expecting.