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)

From within VBA you can refresh all conections using ActiveWorkbook.RefreshAll. Are you using xlwings in python? — MGP
– MGP, Commented Aug 5, 2019 at 12:41
You should show your code, so we can help you with this. Furthermore "It has some issues" is no useful error description. — Pᴇʜ
– Pᴇʜ, Commented Aug 5, 2019 at 13:09
@M.Getrost No, I did not use XLWings. I just created a WorkBook object and executed something like: wb.Refresh. It is not refreshing. — RKh
– RKh, Commented Aug 5, 2019 at 13:09
Yes. check out xlwings. It's an python package. you can refresh your connections with something like this: import xlwings as xw app = xw.App() wb = xw.Book('pathtoFile') wb.api.RefreshAll() — MGP
– MGP, Commented Aug 5, 2019 at 13:13

MGP · Accepted Answer · 2019-08-05 13:19:20Z

2

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.

answered Aug 5, 2019 at 13:19

MGP

2,6732 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DeFlanko · Accepted Answer · 2024-04-16 17:27:52Z

0

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()

edited Apr 16, 2024 at 17:27

answered Apr 16, 2024 at 17:16

DeFlanko

961 silver badge8 bronze badges

Collectives™ on Stack Overflow

Refresh Excel data from command line

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related