none
EXCEL VBA code to tell Excel stop waiting for OLE object and move forward of code RRS feed

  • Question

  • Hi

    i am trying have IE automation using excel for our reporting requirement

    it did great opening the web (intranet , confidential ; cant share the link) but i was stuck in dead loop

    Our code did send the command to trigger the reports (where server will generate a savable CSV files in excel format after done)

    however before the server SQL start running, there is the prompter message want us certified that we acknowledged the data downloaded is confidential . here is the problem

    1) Excel trigger the server side query

    2) Server side gave a prompter want us click 'ok' for it

    3) we did had code (application.sendkey) to handle the prompter. HOWEVER, this has not being send to the application because excel is still waiting for #2 to complete

    so we are stuck in dead loop. code in #3 not moving to dismiss prompt in no #2, but code in #2 unable to complete because code #3 not send and unable to dismiss the prompt

    hopefully someone can assist for me in here

    i am not looking for way to prevent the prompt come out, but i need a solution where i can tell excel stop watining and move straight forward to #3 once #2 done 

    code with explaination

    With ie.document.querySelector("#query4")
    .Click 
    'potentially add wait here
    DoEvents
    .FireEvent "ondblclick" <<- this will trigger a message prompt from server side. the prompt need to be dismissed in order to go forward
    End With
    Application.sendkeys "{Enter}", true <<- this is handler for message from from server side. however it's with excel and this command does not send until the server side complete the OLE action. so we are in deadloop. Server wait for excel , excel wait for server.

    Monday, September 9, 2019 2:35 PM

All replies