Open, Refresh Connections, Save, and Close Excel File

[mpboyle]

We have data in an Excel spreadsheet that is linked to a web xml file.  This data contains lat/long positions which we use to convert the data into a feature class.

I already have a script created that will convert the data to a feature class, but to update the data within the spreadsheet, I need to manually open the Excel file, refresh all the data connections (4 different worksheets), save, and close.

I'm pretty new to python and am looking for a way to automate the process of opening, refreshing all data connections, saving, and closing an Excel file in order to fully automate this process.

Does anyone have a python example of opening, refreshing data connections, saving, and closing an Excel file?

Thanks in advance!

[ShaunWalbridge]

Updating Excel spreadsheets can be tricky, in part due to the particulars of the Excel file format. That said, since the 10.2 release, you can use the xlrd and xlwt python modules to read and write Excel spreadsheets. You can combine these two existing modules with an additional module, xlutils, as is shown in this example:

python - writing to existing workbook using xlwt - Stack Overflow‌

Depending on what you're doing, you may have an easier time just writing a new spreadsheet with xlwt alone, which ships with ArcGIS, and using some semantic naming to track which spreadsheet is the current iteration.

[ToddUlery]

Hi Matthew,

The two mentioned modules are excellent, and I use them when needed to traverse excel and making edits or new sheets.

The deeper you want to manage your excel assets will make it more cumbersome; i.e. a new MODULE.

For what you want to accomplish you are going to need to access the application, or atleast that is what I have done in the past.

Python for Windows extensions -  Browse Files at SourceForge.net‌

import win32com.client

xl = win32com.client.DispatchEx("Excel.Application")

You will need to access Excel so you can so you can refresh your connections.

wb = xl.workbooks.open("***YOUR EXCEL FILE LOCATION***")
xl.Visible = True

Next call the refresh all()

wb.RefreshAll()


Next of course save.


wb.Close(savechanges=1)
xl.Quit()

In addition to save and close.

If you need to run a macro, but you shouldn't need to for refreshing.

xl.run('**MACROFILE LOCATION*** !macro')

[mpboyle]

Todd Ulery‌,

I found the python for windows extensions and that seems to have done what I was after.  Below is a sample of the script I'm using:

from win32com.client import Dispatch
xl = Dispatch('Excel.Application')
wb = xl.Workbooks.Open(r'...path to file...')
wb.RefreshAll()
wb.Close(True)

Like I said, this seems to have worked for me.

[ToddUlery]

Script looks good, was there anything else?

[mpboyle]

Nothing else...script works well...

Unless you can convince my IT dept to give my username sufficient privileges to execute scheduled tasks from my machine

Thanks!

[JohannesBierer]

Would be as well interested in how to convince IT people and sys admins 🙂

[ToddUlery]

I think we might all suffer from this dilemma.