VBA - Unable to open worksheet while VBA is running

Question

I'm having problems with Opening a worksheet while VBA is running (as Excel/VBA is occupied, it prevents another sheet opening as far as I'm aware).

SendKeys ("%O")

Application.Wait DateAdd("s", 1, Now)


'Loop that waits for file to open
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing

On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")

OpenLoop:
If TestWorkbook Is Nothing Then
    Application.Wait DateAdd("s", 1, Now)
    GoTo OpenLoop
Else
End If
'Loop that waits for file to open (End)

Workbooks("export.csv").Activate

SendKeys ("%O") is used to open the file on Internet Explorer as below:

If the code ended here, it will open successfully as VBA has finished. Now, it just get stuck in the loop because VBA being busy is preventing the file to open.

Problem: I need a solution to allow the file to open so the VBA can continue and work on the downloaded folder.

Any help will be appreciated, thank you in advanced.

Avoid using `Sendkey` if possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: https://stackoverflow.com/a/17877390/3219613 — Pᴇʜ, Nov 16 '18 at 12:15
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you. — Craig Wheeler, Nov 16 '18 at 12:23
Try commenting out your 'TestLoop' code lines, from `Open to End If` and then run your code. — GMalc, Nov 16 '18 at 13:09
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop: — jkpieterse, Nov 16 '18 at 13:20
@jkpieterse better to use a real loop instead of an ugly and bad practice `Goto`. See my anwser. — Pᴇʜ, Nov 16 '18 at 13:22

score 0 · Answer 1 · answered Nov 16 '18 at 13:20

If Set TestWorkbook = Workbooks("export.csv") results in Nothing then the following code will loop for ever

OpenLoop:
If TestWorkbook Is Nothing Then
    Application.Wait DateAdd("s", 1, Now)
    GoTo OpenLoop
Else
End If

because you never try to Set TestWorkbook to something again.

So better use something like that:

Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing

Do
    On Error Resume Next
    Set TestWorkbook = Workbooks("export.csv")
    On Error Goto 0 'always re-activate error reporting!!!
    ' you can add a wait here but it's not necessarily needed.
    DoEvents 'keep Excel responsive (in case of endless loop)
Loop While TestWorkbook Is Nothing

Note that this loop will run until export.csv is open. If it will never open this will run forever. So probably you want to insert a time criteria to cancel the loop after some amount of time eg. 1 minute.

Note that Application.Wait DateAdd("s", 1, Now) is not necessarily needed. The loop will just re-try to find the workbook until it is there, even without wait.

VBA - Unable to open worksheet while VBA is running

1 Answers1