Pause macro till the file downloads

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,500
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I am using this code which download a file & pull some data from it.
VBA Code: 
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

1580116637692.png



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

Regards,

Humayun
 
Sort by date Sort by votes
Hello NdNoviceHlp,

I tried your solution

VBA Code: 
Sub try()
Dim t As Double
Workbooks("WB PATH.xlsm").Worksheets("Sheet1").Range("A1").Hyperlinks(1).Follow
t = Timer
Do Until Timer - t > 1
  DoEvents
Loop
Workbooks.Open Filename:=Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"
End Sub

I tried your solution - but its giving me this error

1580193925221.png


on the second attempt it does work as by then the file is uploaded...

Any idea ??

But when I do the following approach (calling the url with Selenium (3rd party library) ) it does work
here is the code..

Code: 
Public driver              As New Selenium.ChromeDriver

Sub tryselenium()

Dim t As Double

Call driver.Start("chrome")
Call driver.Get("https://onedrive.live.com/download?cid=xxxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxxx%xxxxx&authkey=xxxxxxxxxxxxxxx&em=2")
t = Timer
Do Until Timer - t > 1
  DoEvents
Loop
Call driver.Quit

Workbooks.Open Filename:=Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"

End Sub

Any Idea ??
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sorry, It does not download with the second code also (post # 12)
It quits chrome and download stops.

But if the quit part is place after the workbook open then it works
then quits chrome
 
Upvote 0
Hi

Can you give this a whirl. If it works then you possibly want to put in some conditions to ensure that the target workbook is successfully opened that that the destination workbook exists (i.e. is open). Untested as I do not have Selenium.

VBA Code: 
Public driver               As New Selenium.ChromeDriver

Sub foo()
    Dim strFile             As String
    Dim blnContinue         As Boolean
    Dim dteStart            As Date
    Dim wkbTarget           As Workbook
   
    Const lngWait   As Long = 10
   
    '1. OPEN CHROME AND INITIALIZE THE DOWNLOAD.
    With driver
        Call .Start("chrome")
        Call .Get("https://onedrive.live.com/download?cid=xxxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxxx%xxxxx&authkey=xxxxxxxxxxxxxxx&em=2")
    End With
   
    '2. TRY AND IDENTIFY THE DOWNLOADED FILE.
    strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"
    dteStart = Now 'log start time
   
    Do
        DoEvents
        blnContinue = CBool(Len(Dir(strFile)))
    Loop Until blnContinue Or Now > dteStart + TimeSerial(0, 0, lngWait)
   
    '3. COPY THE CONTENTS OF THE DOWNLOADED FILE IF IT EXISTS AND CLOSE CHROME.  WARN THE USER IF FILE DID NOT DOWNLOAD.
    If blnContinue Then
        Set wkbTarget = Workbooks.Open(Filename:=strFile)
       
        With wkbTarget
            Call .Worksheets("VALIDATION").Cells.Copy
            Call Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial(Paste:=xlPasteValues)
            Call .Close(SaveChanges:=False)
        End With
       
        Kill (strFile)
        Call Workbooks("REPORTS.xlsm").Save
    Else
        Call MsgBox(Prompt:="Cannot download file", Buttons:=vbOKOnly + vbCritical, Title:="Download failed")
    End If
   
    '4. CLOSE CHROME AND CLEAN UP
    Call driver.Quit
    Set wkbTarget = Nothing
End Sub

Edit: I have added the public object variable for driver - which I previously omitted.
 
Last edited:
Upvote 0
Jon von der Heyden said:
Hi

Can you give this a whirl. If it works then you possibly want to put in some conditions to ensure that the target workbook is successfully opened that that the destination workbook exists (i.e. is open). Untested as I do not have Selenium.

VBA Code: 
Public driver               As New Selenium.ChromeDriver

Sub foo()
    Dim strFile             As String
    Dim blnContinue         As Boolean
    Dim dteStart            As Date
    Dim wkbTarget           As Workbook
 
    Const lngWait   As Long = 10
 
    '1. OPEN CHROME AND INITIALIZE THE DOWNLOAD.
    With driver
        Call .Start("chrome")
        Call .Get("https://onedrive.live.com/download?cid=xxxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxxx%xxxxx&authkey=xxxxxxxxxxxxxxx&em=2")
    End With
 
    '2. TRY AND IDENTIFY THE DOWNLOADED FILE.
    strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"
    dteStart = Now 'log start time
 
    Do
        DoEvents
        blnContinue = CBool(Len(Dir(strFile)))
    Loop Until blnContinue Or Now > dteStart + TimeSerial(0, 0, lngWait)
 
    '3. COPY THE CONTENTS OF THE DOWNLOADED FILE IF IT EXISTS AND CLOSE CHROME.  WARN THE USER IF FILE DID NOT DOWNLOAD.
    If blnContinue Then
        Set wkbTarget = Workbooks.Open(Filename:=strFile)
     
        With wkbTarget
            Call .Worksheets("VALIDATION").Cells.Copy
            Call Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial(Paste:=xlPasteValues)
            Call .Close(SaveChanges:=False)
        End With
     
        Kill (strFile)
        Call Workbooks("REPORTS.xlsm").Save
    Else
        Call MsgBox(Prompt:="Cannot download file", Buttons:=vbOKOnly + vbCritical, Title:="Download failed")
    End If
 
    '4. CLOSE CHROME AND CLEAN UP
    Call driver.Quit
    Set wkbTarget = Nothing
End Sub

Edit: I have added the public object variable for driver - which I previously omitted.
Click to expand...

Hi Jon,

Thanks for the code..... the timer function does give error sometimes.
To the best of my understanding it should pause the code unless the file completes the downloading process. Well it does sometime - but sometime it does not resulting in an error.

Better I stick to your code as it is more precise about finding the file or time elapsed (whichever comes first). correct me if I am wrong.

I tried it with an error in mid of the code

1580209273564.png


with this part of the code being highlighted

1580209382887.png


I would request you to please have a look at my full original code and please merge it with your code to get desired result i.e. wait till the download is completed and then move further

VBA Code: 
Public driver              As New Selenium.ChromeDriver

Sub update_database()
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
 
Dim t As Double
Dim ws As Worksheet

Call driver.Start("chrome")
Call driver.Get("https://onedrive.live.com/download?cid=xxxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxxx%xxxxx&authkey=xxxxxxxxxxxxxxx&em=2")

Workbooks.Open Filename:=Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"

Set WSArray = Workbooks("REPORTS.xlsm").Worksheets(Array("INDEX", "DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))
Set WSArray1 = Workbooks("REPORTS.xlsm").Worksheets(Array("DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))

For Each ws In WSArray
ws.Unprotect Password:="merchant"
 Next
    
     For Each ws In WSArray1
   ws.Visible = True
   Next
          
    '''''''''''''''''''''''''''''''''''''''''''    
    
    Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("VALIDATION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial Paste:=xlPasteValues    
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4:AO1002").ClearContents
    
    If Workbooks("DATA ENTRY.xlsm").Worksheets("ORDERS").AutoFilterMode = True _
    Then Workbooks("DATA ENTRY.xlsm").Worksheets("ORDERS").AutoFilterMode = False
    
    Workbooks("DATA ENTRY.XLSM").Worksheets("ORDERS").Range("A2:AO10000").Copy
    Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4").PasteSpecial Paste:=xlPasteValues
    
    Range("A3").Select
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("DAILY PRODUCTION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("FRI DPI LABTEST").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("PO DESIGN COLOUR").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("DATA ENTRY.xlsm").Close SaveChanges:=False
    Kill (Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm")
    Workbooks("REPORTS.xlsm").Save
    
    '''''''''''''''''''''''''''''''''''''''''''
  
   For Each ws In WSArray1
   ws.Visible = xlSheetVeryHidden
   Next
  
    Sheets("INDEX").Range("A41").Value = Now
    
    For Each ws In WSArray
ws.Protect Password:="merchant", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True
 Next
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False

Call driver.Quit

    End Sub
 
Upvote 0
Hi Jon von der Heyden,

This is what I have come up with... Final Code (very slight changes) ... Works great.

Code: 
Public driver               As New Selenium.ChromeDriver

Sub update_database()

On Error GoTo errorhandler

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
    
    Dim strFile             As String
    Dim blnContinue         As Boolean
    Dim dteStart            As Date
    Dim ws                  As Worksheet
    
    Const lngWait   As Long = 15
  
    '1. OPEN CHROME AND INITIALIZE THE DOWNLOAD.
    
    With driver
            Call .Start("chrome")
    SendKeys ("%{TAB}")
    SendKeys "{NUMLOCK}"
        Call .Get("https://onedrive.live.com/download?cid=xxxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxxxxxxxxx&authkey=xxxxxxxxxxxxxxx&em=2")
    End With
  
    '2. TRY AND IDENTIFY THE DOWNLOADED FILE.
    strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"
    dteStart = Now 'log start time
  
    Do
        DoEvents
        blnContinue = CBool(Len(Dir(strFile)))
    Loop Until blnContinue Or Now > dteStart + TimeSerial(0, 0, lngWait)
  
    '3. COPY THE CONTENTS OF THE DOWNLOADED FILE IF IT EXISTS AND CLOSE CHROME.  WARN THE USER IF FILE DID NOT DOWNLOAD.
    If blnContinue Then
      
       Call driver.Quit
       Workbooks.Open Filename:=strFile
              
      
Set WSArray = Workbooks("REPORTS.xlsm").Worksheets(Array("INDEX", "DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))
Set WSArray1 = Workbooks("REPORTS.xlsm").Worksheets(Array("DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))

For Each ws In WSArray
ws.Unprotect Password:="merchant"
 Next
    
     For Each ws In WSArray1
   ws.Visible = True
   Next
          
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("VALIDATION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4:AO1002").ClearContents
    
    If Workbooks("DATA ENTRY.xlsm").Worksheets("ORDERS").AutoFilterMode = True _
    Then Workbooks("DATA ENTRY.xlsm").Worksheets("ORDERS").AutoFilterMode = False
    
    Workbooks("DATA ENTRY.XLSM").Worksheets("ORDERS").Range("A2:AO10000").Copy
    Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4").PasteSpecial Paste:=xlPasteValues
    
    Range("A3").Select
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("DAILY PRODUCTION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("FRI DPI LABTEST").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Cells.ClearContents
    Workbooks("DATA ENTRY.xlsm").Worksheets("PO DESIGN COLOUR").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    '''''''''''''''''''''''''''''''''''''''''''
    Workbooks("DATA ENTRY.xlsm").Close SaveChanges:=False
    kill Environ$("USERPROFILE") & "\Downloads\DATA ENTRY" & "*"""
    Workbooks("REPORTS.xlsm").Save
    
    '''''''''''''''''''''''''''''''''''''''''''
  
   For Each ws In WSArray1
   ws.Visible = xlSheetVeryHidden
   Next
  
    Sheets("INDEX").Range("A41").Value = Now
    
    For Each ws In WSArray
ws.Protect Password:="merchant", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True
    Next
        
    Else
        Call driver.Quit
        kill Environ$("USERPROFILE") & "\Downloads\DATA ENTRY" & "*"""
        Call msg

End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

Exit Sub

errorhandler:
       Call driver.Quit
       Call msg

End Sub

It would not have been possible (for a novice like me) without your help & support.

Thanks once again

Regards,

Humayun
 
Upvote 0
Hello Jon & Justyna,

I have been working with the last final code (Post # 16) since over a month now and it works very well.
The problem I am now having is when I try to run this code on another computer it gives me this error

Run-time error '-2146232576 (80131700)':
Automation error

with this part of the code highlited

Call driver.Quit

Do you guys have any idea... why is this happening

Regards,

Humayun
 
Upvote 0
Hi Jon,

It does have the selenium basic driver.....
 
Upvote 0
Hello Jon & JustynaMK

I have tried the code on the third machine and it works fine. So I will get back to the second machine once it is available to see what went wrong with that selenium driver and also that correct chrome driver is installed.

Just wanted to ask that is it possible to load the chrome window in the minimized state or minimize it as soon as it is loaded

I want the downloading to run in the background so the user can only see the main window & nothing else

Regards,

Humayun
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Macro to download zip, unpack and copy data
Replies
0
Views
75
HeinrichPaul
H
P
  • Solved
Have copied data paste below occupied cell in Column A (offset)
Replies
5
Views
174
smozgur
smozgur
G
  • Question
Email from Excel query
Replies
0
Views
167
gmazza76
G
J
  • Question
Kill Temp File
Replies
1
Views
334
ExcelToDAX
E
H
  • Question
VBA code required to pull data from closed workbook
Replies
7
Views
868
Fluff
Fluff

Forum statistics

Threads
1,214,833
Messages
6,121,868
Members
449,053
Latest member
Mesh

Share this page

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