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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

par60056

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

ADVERTISEMENT

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
  1. 2011
  2. 2010
Platform
  1. Windows
  2. 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,129,790
Messages
5,638,322
Members
417,020
Latest member
MSVII

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
Top