Excel VBA: Pause Code Until Download from Chrome Browser

[bt_24]

Hi Everyone,

Here is my current issue that I am trying to solve - I have code that follows hyperlinks from my unread emails using the chrome browser using a shell command. The code will loop through opening the hyperlink from the emails and quickly go one to the next. The issue with this is that upon opening that link is it kicks off a download. I need VBA to wait until that file lands in the Downloads Folder in order to further manipulate it before moving onto the next email in in the folder.

My initial thought was to obtain the count of files in the folder (ex there are 5 files in the folder at the start) and make excel wait until the count of files in the folder is +1. Below is the snippet of code that I have found - sourced from FileSystemObject in VBA – Explained

'creating download folder to check the count of items in folder
Dim DownloadFolderPath As String
Dim DownloadFolder As Object
Dim fso As Object 'FileSystemObject
Dim DownloadFiles As Object 'File - not sure if correct
Dim DownloadCount As Integer
        DownloadCount = 0
        
    DownloadFolderPath = "C:\Users\RandomPerson\Downloads\"
    Set fso = CreateObject("Scripting.FileSystemObject") 'New FileSystem Object
    Set DownloadFolder = fso.GetFolder(DownloadFolderPath)
    Set DownloadFiles = fso.GetFolder(DownloadFolderPath).Files
        
        For Each DownloadFiles In DownloadFolder.Files
            If DownloadFiles.Attributes <> 34 Then 'not sure what the 34 stand for I think it is probably a status like not blank ?
                DownloadCount = DownloadCount + 1
            End If
        Next DownloadFiles
        
              Debug.Print DownloadCount

I do not understand how to execute on my thought to force excel to wait for the file to download and would greatly appreciate any help on this problem or even pointing me in the right direction. I also tried to use Application.Wait to pause the code for 10 seconds but that method was unstable and I am looking for a better way like my thought above.

 

[bt_24]

Hi All,

Part of this is a bump. Part 2 is how I have edited where I am at in my thinking and trying to get code to run. The goal is to download multiple files from chrome and then access those file to be edited- the kicker is it takes a while for the file to get downloaded. Essentially instead of trying to wait for the count of files to increase by 1 - I listed the the files I am looking for on sheet 1 and and trying to use Dir to see if the file is there, if not wait a second then loop through again looking for the file.

The code in place works to kick off downloads for multiple files - in its own module. In a second module I have the following code below to check for the file. It works when I step through after having made sure the files are downloaded, but when combined with the first module that kicks off the downloads it gets suck in the while loop. Can anyone please advise how to get this to work?

Option Explicit
Sub FindFilestoEdit()
'Title: Find the files that were just downloaded and test them

Dim DownloadFolderPath As String
Dim MyFile As String
Dim lRow As Long
Dim i As Long              
               
    DownloadFolderPath = "C:\Users\WThompson\Downloads\"
    lRow = 4 'need to make this dynamic - to be done after testing
    i = 2
                
  Do
   MyFile = Dir(DownloadFolderPath & Worksheets("Sheet1").Cells(i, 4).Value & ".csv", vbNormal) 'i is the row, 4 is the column
           While Len(MyFile) < 1
               Application.Wait (Now + TimeValue("0:00:01")) 'this is basically an error handler until the file is there
           Wend
          'redeclaring MyFile without Dir because I think Dir is binary
           MyFile = DownloadFolderPath & Worksheets("Sheet1").Cells(i, 4).Value & ".csv"
           Debug.Print MyFile
  
           Workbooks.Open (MyFile)
  
           ThisWorkbook.Activate
  
        i = i + 1
      Loop Until i = lRow + 1
End Sub