Updating connections in excel in python

Question

Updating connections in excel in python

Tell me how to use win32com.client or another python library to update connections to oracle db in excel? I tried RefreshAll(), but my connection is password protected, so I can't update

Xlapp = win32com.client("Excel.Application")

Wb = xlapp.workbooks.open('filepath')

Wb.RefreshAll()

1

python excel connection

Author: 0xdb, 2020-11-24

Source

1 answers

score 1 · Answer 1

I found this solution, it works for me. Please note that the script is relevant for pivot tables that are built on data from Oracle. For regular tables, you need to use some other object of the Excel sheet (I will not tell you who knows the name of the object-write in the comments, it will be useful for everyone).

   import win32com.client 
    
    office = win32com.client.Dispatch("Excel.Application") 
    wb = office.Workbooks.Open("FPath/FName.xlsx") 
    
    connCount=wb.Connections.Count
    for k in range(1,connCount+1):
    wb.Connections(k).OLEDBConnection.Connection="OLEDB;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=YourUserID;password=YourPassword;Data Source=YourDataSource"
        
    count = wb.Sheets.Count 
    for i in range(count): 
        ws = wb.Worksheets[i+1] 
        pivotCount = ws.PivotTables().Count 
        for j in range(1, pivotCount+1): 
            ws.PivotTables(j).PivotCache().Refresh() 
            print("Worksheet " + ws.Name + " updated")  
    
    for k in range(1,connCount+1):
        wb.Connections(k).OLEDBConnection.SavePassword = False
      
    office.Save()
    wb.Close()
    office.Quit()