VBA skipping to the next line without waiting file

shakeeb92

New Member
Joined
Nov 18, 2013
Messages
15
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:
Code:
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 :

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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
2011, 2010
Platform
Windows, MacOS
You could try checking for the workbook to have loaded. Maybe something like:

Code:
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

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
2011, 2010
Platform
Windows, MacOS
oops I didn't see that the wait stops the downloading.
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
2011, 2010
Platform
Windows, MacOS
maybe this will work

Code:
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

New Member
Joined
Nov 18, 2013
Messages
15
maybe this will work

Code:
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

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
2011, 2010
Platform
Windows, MacOS
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:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,096
Messages
5,466,633
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top