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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
oops I didn't see that the wait stops the downloading.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top