Pause macro till the file downloads

[hrayani]

Hello Friends,

I am using this code which download a file & pull some data from it.

Sub update_database()

Workbooks("REPORTS.xlsm").Worksheets("INDEX").Range("A2").Hyperlinks(1).Follow
    
Workbooks.Open Filename:=Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"
    
    Workbooks("DATA ENTRY.xlsm").Worksheets("VALIDATION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial Paste:=xlPasteValues
        
    Workbooks("DATA ENTRY.xlsm").Close savechanges:=False
    Kill (Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm")
    Workbooks("REPORTS.xlsm").Save
    
    End Sub

The problem is that sometime it takes the file to download but the code jumps to the next part and give this error

Can't we tell the macro to wait until the file is downloaded and then jumps to the workbook open part ??

Regards,

Humayun

 

[Jon von der Heyden]

Hi Humayun

You can try check if the file exists. And keep trying until such time that it does exist (i.e. it has downloaded), or a certain amount of time has elapsed. In the example below we keep trying until the file is located or ten seconds has elapsed.

Sub foo()
    Dim strFile     As String
    Dim blnContinue As Boolean
    Dim dteStart    As Date
    
    Const lngWait   As Long = 10 'wait in seconds
    
    strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"

    dteStart = Now 'log start time
try:
    blnContinue = CBool(Len(Dir(strFile))) 'check if the file exists
    
    If Not blnContinue Then
        If Now <= dteStart + TimeSerial(0, 0, lngWait) Then GoTo try 'try again if the file doesn't exist (i.e. not downloaded)
    End If
    
    If blnContinue Then
        'do what you want to do
        MsgBox "continue"
    Else
        'it isn't downloading
        MsgBox "cannot download file"
    End If

End Sub

 

[Jon von der Heyden]

On second thoughts I don't like using a label - so in this version I'm opting for a Do Loop:

Sub foo()
    Dim strFile     As String
    Dim blnContinue As Boolean
    Dim dteStart    As Date
    
    Const lngWait   As Long = 10 'wait in seconds
    
    strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"

    dteStart = Now 'log start time
    
    Do
        blnContinue = CBool(Len(Dir(strFile))) 'check if the file exists
    Loop Until blnContinue Or Now > dteStart + TimeSerial(0, 0, lngWait) 'exit when the file is found or the timer has elapsed
    
    If blnContinue Then
        'do what you want to do
        MsgBox "continue"
    Else
        'it isn't downloading
        MsgBox "cannot download file"
    End If
End Sub

 

[hrayani]

Hi Jon von der Heyden,

Thanks for the solutions.... I am a novice in VBA - still learning from you guys
What I have understood from both the codes is this -

both the codes will exit which ever comes first.
a) file found or b) time out (10 seconds in said case)
Means both the codes are same as far as the functionality is concerned... approach is different
please correct me if I am wrong

Secondaly, as I said that I am a novice. please let me know where to put my part of the code in the above

is it after If blnContinue Then

If blnContinue Then
        'do what you want to do - my part of the code
        MsgBox "continue"
    Else
        'it isn't downloading
        MsgBox "cannot download file"
    End If

 

[Jon von der Heyden]

Hi

You are correct that both of my suggestions essentially do the same thing - but I favour the 2nd approach.

Regarding including your code, perhaps something like this (untested) - indeed included within the blnContinue condition:

Sub foo()
    Dim strFile     As String
    Dim blnContinue As Boolean
    Dim dteStart    As Date
    Dim wkbTarget   As Workbook
    
    Const lngWait   As Long = 10 'wait in seconds
    
    strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"

    dteStart = Now 'log start time
    
    Do
        blnContinue = CBool(Len(Dir(strFile))) 'check if the file exists
    Loop Until blnContinue Or Now > dteStart + TimeSerial(0, 0, lngWait) 'exit when the file is found or the timer has elapsed
    
    If blnContinue Then
        Set wkbTarget = Workbooks.Open(Filename:=strFile) 'set a reference to the workbook
        
        With wkbTarget
            .Worksheets("VALIDATION").Cells.Copy
            Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial Paste:=xlPasteValues
            .Close savechanges:=False
        End With
        
        Kill (strFile)
        Workbooks("REPORTS.xlsm").Save
    Else
        'it isn't downloading
        MsgBox "cannot download file"
    End If
End Sub

 

[hrayani]

Hi Jon von der Heyden,

Many thanks....

I will try and keep you posted

 

[NdNoviceHlp]

Hi hrayani. I'm sure that Jon has offered U the correct resolution but I thought perhaps this bit of code that Jon Peltier provided may also do the trick. HTH. Dave

Dim t As Double
Workbooks("REPORTS.xlsm").Worksheets("INDEX").Range("A2").Hyperlinks(1).Follow
t = Timer
Do Until Timer - t > 1
  DoEvents
Loop
Workbooks.Open Filename:=Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"

 

[hrayani]

Hello NdNoviceHlp,

Thanks very much... I appreciate.

I will for sure try both yours and jon’s code tomorrow as I don’t have access to my computer right now.

Will keep both of you posted

Regards,

Humayun

 

[Jon von der Heyden]

Just as a FYI - my code essentially does the same as the code from Jon Peltier - with exception that my suggestion incorporates checks as to whether or not the file has downloaded. This way my code will exit possibly sooner than the timer.

 

[hrayani]

Just as a FYI - my code essentially does the same as the code from Jon Peltier - with exception that my suggestion incorporates checks as to whether or not the file has downloaded. This way my code will exit possibly sooner than the timer.

Very well noted... I will share the outcome of yours & Jon Pettier’ code