nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Find more posts tagged with

Accepted answers

All comments

ToddUlery

I think we might all suffer from this dilemma.

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!

ToddUlery

Script looks good, was there anything else?

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

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‌




<SPAN class="kwd" style="color: #00008b; background: transparent;">import</SPAN><SPAN class="pln" style="background: transparent;"> win32com</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="pln" style="background: transparent;">client
</SPAN>




<SPAN class="pln" style="background: transparent;">xl = win32com</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="pln" style="background: transparent;">client</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="typ" style="color: #2b91af; background: transparent;">DispatchEx</SPAN><SPAN class="pun" style="background: transparent;">(</SPAN><SPAN class="str" style="color: #800000; background: transparent;">"Excel.Application"</SPAN><SPAN class="pun" style="background: transparent;">)</SPAN>

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




<SPAN class="pln" style="background: transparent;"></SPAN><SPAN class="pln" style="background: transparent;">wb = xl</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="pln" style="background: transparent;">workbooks</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="pln" style="background: transparent;">open</SPAN><SPAN class="pun" style="background: transparent;">(</SPAN><SPAN class="str" style="color: #800000; background: transparent;">"***YOUR EXCEL FILE LOCATION***"</SPAN><SPAN class="pun" style="background: transparent;">)</SPAN><SPAN class="pln" style="background: transparent;">
xl</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="typ" style="color: #2b91af; background: transparent;">Visible</SPAN><SPAN class="pln" style="background: transparent;"> </SPAN><SPAN class="pun" style="background: transparent;">=</SPAN><SPAN class="pln" style="background: transparent;"> </SPAN><SPAN class="kwd" style="color: #00008b; background: transparent;">True
</SPAN>

Next call the refresh all()

 

<SPAN class="pln" style="background: transparent;">wb</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="typ" style="color: #2b91af; background: transparent;">RefreshAll</SPAN><SPAN class="pun" style="background: transparent;">()</SPAN>

 Next of course save. 




<SPAN class="pln" style="background: transparent;"></SPAN><SPAN class="pun" style="background: transparent;"></SPAN><SPAN class="typ" style="color: #2b91af; background: transparent;">wb.Close</SPAN><SPAN class="pun" style="background: transparent;">(</SPAN><SPAN class="pln" style="background: transparent;">savechanges</SPAN><SPAN class="pun" style="background: transparent;">=</SPAN><SPAN class="lit" style="color: #800000; background: transparent;">1</SPAN><SPAN class="pun" style="background: transparent;">)</SPAN><SPAN class="pln" style="background: transparent;">
xl</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="typ" style="color: #2b91af; background: transparent;">Quit</SPAN><SPAN class="pun" style="background: transparent;">()</SPAN>

In addition to save and close.

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

<SPAN class="pln" style="background: transparent;">xl</SPAN><SPAN class="pun" style="background: transparent;">.</SPAN><SPAN class="pln" style="background: transparent;">run('**MACROFILE LOCATION*** !macro')</SPAN>

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.