• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatic OK

A

Anar

Member
Hi,

In Excel I created several tables over Power Query by connecting to web sites.
I have set tales Refresh every 30 minutes. But sometimes the web sites do not respond and message box appears about it.
Can I configure Excel to automatically click on OK button so that it disappears immediately or do not pop up at all?

I have read several posts on this, but could not find appropriate solution.

Thank you,
Anar
 
Actually, there is no code. I used Power Query to import data from web and and set refresh frequency to 5 minutes in Connection Properties. So Excel connects to web sites every 5 minutes and imports data. But, the process is not successful always - sometimes Excel cannot retrieve data and message pops up about it and I have to click OK manually. My intention is to set Excel (with VBA or without it) so that it clicks on OK button itself.
 
The case is not about Power Query at all, rather more about how to click on OK button automatically in Excel.
 
Create a vbs file by copying below code. You need to find out the task handle as mentioned in other thread.

Start this vbscript from specific Excel workbook. It will keep running until you close Excel completely. It will periodically check for PowerQuery dialog. If open it will close it.

Code: 
Dim wdApp
Dim xlApp
Dim tsk
Dim blxlRunning

On Error Resume Next
Set xlApp = GetObject(,"Excel.Application")
On Error goto 0

If not xlApp is nothing then
    Set wdApp = CreateObject("Word.Application")
    wdapp.Visible = False
    blxlRunning = True
    do while blxlRunning <> False
        wscript.sleep 5000
        For each tsk in wdApp.Tasks
        '\\ Get the exact task name of PowerQuery first
            If Instr(tsk.Name,"PowerQuery") > 0 then
                tsk.close
            end if
        Next
        On Error Resume Next
        Set xlApp = GetObject(,"Excel.Application")
        On Error goto 0
        If xlApp is nothing then
            blxlRunning = False
        endif
    Loop
    wdApp.Quit
    Set wdApp = Nothing
endif
 
You must log in or register to reply here.
Back
Top