Refresh external data connection in excel using Python.

[mahesh]

I have a excel file with external data connection. I need to refresh this connection data using python.

I tried this code. 

import win32com.client
import os

fileName="testconn.xlsx"
xl= win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open(fileName)
xl.Visible = True
wb.RefreshAll()
wb.Save()
xl.Quit()

 This code works fine but requires excel installed on the machine.

The other approach I am thinking is:

 - if some how I get the mapping of URLs of data connection and named range in which it is loaded in excel, I can download the data using URL and update the named range with new data using openpyxl.

Is there a better approach for this ?

Thanks :) 

[Adrian Klaver]

On 07/20/2016 11:49 AM, mahesh wrote:
> I have a excel file with external data connection. I need to refresh
> this connection data using python.
>
> I tried this code.
> |
>

> *import*win32com.client
> *import*os
>
> fileName=*"**testconn.xlsx"
> *xl=win32com.client.DispatchEx(*"Excel.Application"*)
> wb =xl.workbooks.open(fileName)
> xl.Visible=*True
> *wb.RefreshAll()

> wb.Save()
> xl.Quit()
>
> |
>
> This code works fine but requires excel installed on the machine.
>
> The other approach I am thinking is:
> - if some how I get the mapping of URLs of data connection and named
> range in which it is loaded in excel, I can download the data using URL
> and update the named range with new data using openpyxl.
>
> Is there a better approach for this ?

What database/store is the connection to?

I doubt the URL is going to of much help without the underlying data
driver to fetch the data.

>
> Thanks :)
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to python-excel...@googlegroups.com
> <mailto:python-excel...@googlegroups.com>.
> To post to this group, send email to python...@googlegroups.com
> <mailto:python...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.


--
Adrian Klaver
adrian...@aklaver.com

[Victor Hsu]

I create a Marco in Excel.

Then use Python to call the Marco to complete the refresh of external data connection.

mahesh於 2016年7月21日星期四 UTC+8上午7時40分50秒寫道：