Refresh Excel data from command line

Question

I have an Excel sheet with PowerQuery connecting via ODBC to SAP Data Source.

Usually, I manually click "Refresh All" to refresh data. To automate this, I wrote a Python script to refresh the sheet automatically. It has some issues.

Is there a way to execute this Refresh All command from command line?

Code tried in Python:

 xlapp = win32com.client.DispatchEx("Excel.Application")
 wb = xlapp.workbooks.open(File1.xlsx)
 wb.RefreshAll()
wb.SaveAs(File2.xlsx)

Answer 1

You can use xlwings in python. It's probably the best Excel-Library in Python. your code could look like this:

import xlwings as xw

app = xw.App()

wb = xw.Book('pathToFile')
wb.api.RefreshAll()

Another option, as PEH mentioned, would be to use a VBScript. Open an txt-Editor copy in this Code:

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("excel.application")
Set ObjWB = ObjExcel.Workbooks.Open("pathToFile")
ObjWB.RefreshAll

Set ObjWB = Nothing
Set ObjExcel = Nothing

save as fileName.vbs Run the VBScript to update the connections.

Answer 2

I wanted to add some extra notes for any future visitors.

I ended up using the above from MGP's post and added a few extras.

import xlwings as xw

file = '\\\\networkshare\\folder\\folder\\my excel file with spaces.xlsx'

app = xw.App()

wb = xw.Book(file)

wb.api.RefreshAll()

# Save the file
wb.api.Save()

# Close the file
wb.close()

# Close Excel 
app.kill()