[python-win32] Issue with refresh of ODBC linked table and submitting credentials in excel using Python

[Carl E. Evans]

I have the following code:

import win32com.clientimport os


file1= r'C:\\Users\file.xlxm'
def refresher():
if os.path.exists(file1):
xl = win32com.client.DispatchEx("Excel.Application")
xl.Workbooks.Open(Filename=file1,ReadOnly=1)
xl.Visible = 1



xl.Workbooks(1).Close(SaveChanges=1)
xl.Application.Quit()
del xl
refresher()

Which gets me into the file, but I am unsure of how to refresh a ODBC
connected table that requires a simple form submission of user credentials.
The current settings dont allow me to save credentials so a script seems to
be the only solution. Any suggestions?

This will allow me to quickly update my models without have to do it
manually in excel.

Thanks!

[Vernon D. Cole]

I think you are saying that you have an Excel spreadsheet which imports
data from an ODBC data source, as talked about in Overview of connecting to
(importing) data
<https://support.office.com/en-ca/article/Overview-of-connecting-to-importing-data-c0ad7aec-ff1a-4e48-9c21-dc18a102433f>
.

If that is correct, then my suggestion would be to eliminate Excel from the
picture, and manipulate the database directly using Python and SQL.
Unfortunately, the version of adodbapi which ships with the present latest
version of pywin32 is broken, so you will have to download directly from
http://sf.net/projects/adodbapi until the next release. Using ADO you can
write SQL commands to manipulate data in any ODBC data source, or in an
ACCESS database, or in an Excel spreadsheet.

Post by Carl E. Evans
import win32com.clientimport os
file1= r'C:\\Users\file.xlxm'
xl = win32com.client.DispatchEx("Excel.Application")
xl.Workbooks.Open(Filename=file1,ReadOnly=1)
xl.Visible = 1
xl.Workbooks(1).Close(SaveChanges=1)
xl.Application.Quit()
del xl
refresher()
Which gets me into the file, but I am unsure of how to refresh a ODBC
connected table that requires a simple form submission of user credentials.
The current settings dont allow me to save credentials so a script seems to
be the only solution. Any suggestions?
This will allow me to quickly update my models without have to do it
manually in excel.
Thanks!
--
-----
Carl E. Evans | Economist
_______________________________________________
python-win32 mailing list
https://mail.python.org/mailman/listinfo/python-win32