dynamically read and/or overwrite an excel file with python without the overwrite alert appearing

Question

For some reason the following code runs fine but the file overwrite alert keeps coming up even though I have set xl.EnableEvents = False, the code won't execute further unless I manually click the overwrite file popup. Does anyone know how to fix this?

The code opens an excel file which contains a string which allows the excel file to connect to the bloomberg api, I used this solution here to get this to work. As long as the file is open for enough time the data is pulled into the file and then saves and exits. It takes around ~35 seconds to get the data and the pandas table starts displaying the content I'm requesting

The problem is the popups! - I need to see when the string '#N/A Requesting Data...' is no longer in the file and can't see a way to do it without periodically saving the file. A solution that allows me to see the file contents dynamically without having to save would be great.

The solution here didn't work for me to stop the popups, I could probably make a new file each time and then delete them all at the end but this seems a bit clunky. This question extends this problem here if anyone wants to see the code and the problem in a more full context.

WB = 'C:/path/to/my/file.xlsx'
location = "EGLL"

def run_VWA(WB, location):
    """open the excel file, allow enough time to pull the data, then close and save"""

    bb = 'C:/blp/API/Office Tools/BloombergUI.xla'
    xl=win32com.client.DispatchEx("Excel.Application")  
    xl.Workbooks.Open(bb)
    xl.AddIns("Bloomberg Excel Tools").Installed = True

    wb = xl.Workbooks.Open(Filename=WB) #opens workbook in readonly mode.

    xl.Visible = False
    xl.EnableEvents = False
    xl.DisplayAlerts = False

    total=0
    colstring='#N/A Requesting Data...'
    while total < 40:
        wb.Save()   
        df = df_from_excel(WB, location)
        if colstring not in df:
            break
        time.sleep(3)
        total+=3


    wb.Close(SaveChanges=1)
    xl.DisplayAlerts = True
    xl.Quit()
    #Cleanup the com reference. 
    del xl   

    return

Any help with this is much appreciated, I have very limited experience with the win32com library.

Breannebrear

Are you sure you need to wb.Save() at each iteration? There has to be another way to query the data in the spreadsheet. – Cenobite

Yea that's what I was hoping but wasn't familiar enough with the library to know how, I couldn't find any examples of it on stack overflow - though possibly not knowing the correct keywords to search could have undermined my searching. Anyone else comes up against this problem see answer below. – Breannebrear

Answer 1

After a good few hours digging I've found how to solve this dynamically without the need for saving the file each iteration. If anyone else comes up against this problem most of the solution was found here. Many thanks assylias for some useful pointers.

def run_VWA(WB, location):
    """open the excel file, allow enough time to pull the data, then close and save"""

    bb = 'C:/blp/API/Office Tools/BloombergUI.xla'
    xl=win32com.client.DispatchEx("Excel.Application")  
    xl.Workbooks.Open(bb)
    xl.AddIns("Bloomberg Excel Tools").Installed = True

    wb = xl.Workbooks.Open(Filename=WB) #opens workbook in readonly mode.

    xl.Visible = False
    xl.EnableEvents = False
    xl.DisplayAlerts = False

    count=0
    while True:
        readData = wb.Worksheets(location)
        allData = readData.UsedRange
        if allData.Rows.Count > 1 or allData.Columns.Count > 1:
            print('rows: {}'.format(allData.Rows.Count))
            print('cols: {}'.format(allData.Columns.Count))
            break
        print(wb)
        print(count)
        time.sleep(3)
        count+=3


    wb.Close(SaveChanges=1)
    xl.DisplayAlerts = True
    xl.Quit()
    #Cleanup the com reference. 
    del xl   

    return

Breannebrear