VBA skipping to the next line without waiting file

[shakeeb92]

Hello guys,

I have this piece of code that is used to access a Link. Once this link is open, an excel file is automatically downloaded. This file is big and takes about 2 mins to open. The problem is that vba does not wait for the file and continues to the next line of code, which creates a problem since it is supposed to use the "already" open file. How can i make vba wait for the file to be full downloaded? I tried using:

application.wait

but this doesn't works since it halts everything on excel (including download).
Another problem is that everytime i open this link, excel adds automatically _(number) at the end of the file name (if i open this file for the 10th time, its called ABC_10). How can i call this file in the next line?

here is my code :

Const Hyper As String = "[URL]http://can't_mention_file_name.com[/URL]"
ThisWorkbook.FollowHyperlink Address:=Hyper ', NewWindow:=True


Workbooks(Workbooks.Count).Activate ' I tried this code to avoid the change in file name


Sheets("PDF Report ").Select    ' Code breaking here since it didn't find the sheet which is supposed to be in the file that is currently downloading

Thanks for the help

 

[par60056]

You could try checking for the workbook to have loaded. Maybe something like:

Dim found As Boolean
Dim timeout As Integer
On Error Resume Next
found = False
While !found And timeout < 100
    Workbooks("can't_mention_file_name").Activate
    If ActiveWorkbook.Name = "can't_mention_file_name" Then
        found = True
    Else
        Application.Wait Now() + TimeValue("0:00:10")
        timeout = timeout + 1
    End If
Wend
On Error GoTo 0
If timeout = 100 Then
    MsgBox "The file didn't load.  Stopping", vbCritical
    Stop
End If

 

[par60056]

oops I didn't see that the wait stops the downloading.

 

[shakeeb92]

oops I didn't see that the wait stops the downloading.

No worries. Thank you for trying

 

[par60056]

maybe this will work

Dim found As Boolean
Dim timeout As Date
On Error Resume Next
found = False
timeout = Now() + TimeValue("0:05:00")
While not found And Now() < timeout
    Workbooks("can't_mention_file_name").Activate
    If ActiveWorkbook.Name = "can't_mention_file_name" Then
        found = True
    End If
    DoEvents
Wend
On Error GoTo 0
If Not found Then
    MsgBox "The file didn't load.  Stopping", vbCritical
    Stop
End If

 

[shakeeb92]

maybe this will work

Dim found As Boolean
Dim timeout As Date
On Error Resume Next
found = False
timeout = Now() + TimeValue("0:05:00")
While not found And Now() < timeout
    Workbooks("can't_mention_file_name").Activate
    If ActiveWorkbook.Name = "can't_mention_file_name" Then
        found = True
    End If
    DoEvents
Wend
On Error GoTo 0
If Not found Then
    MsgBox "The file didn't load.  Stopping", vbCritical
    Stop
End If

Hello,

Another problem that i mentioned is that excel is downloading the file and auomatically adds an increment of 1 at the end of the file name each time i open it. So i can't try your method unless i resolve this issue.thanks

 

[par60056]

Hello,

Another problem that i mentioned is that excel is downloading the file and auomatically adds an increment of 1 at the end of the file name each time i open it. So i can't try your method unless i resolve this issue.thanks

I suspect that this is a file system issue. It is running into a name collision in the download directory. Do you need to keep old ones?

may need some code like:

Dim fs As FileSystemObject


Set fs = CreateObject("Scripting.FileSystemObject")
If (fs.FileExists("downloadfolder\" & "can't_mention_file_name")) Then
    fs.DeleteFile "downloadfolder\" & "can't_mention_file_name", True
End If

You will need to add a reference to "Microsoft Scripting Runtime" to get the file system object.

Or you can delete them from outside Excel.