VBA - dynamic download folder in chrome

[Stetim]

Hi All

First time call, long time listener.

I am looking for help with a VBA module to download multiple files to a dynamic target folder with VBA.

My organisation checks building plans, and all submissions are through an online portal. At some key points in process we need to save these documents into our document managment system.

I have already got modules to;
- Identify relevant submissions
- Navigate to the website and extract the raw data
- Create a unique folder in the right place for each submission
- Identify which set of documents to be downloaded (there are multiple per page)
- Extract the URL and download the relevant files.

At the moment a manual step is required to move the downloaded documents from the chrome downloads folder into the newly (VBA) created folder.

There can be up to 20 separate applications a day and 40 documents per application. My goal is to set and forget for the whole thing.

 

[John_w]

At the moment a manual step is required to move the downloaded documents from the chrome downloads folder into the newly (VBA) created folder.

Are you asking for code to determine the current Chrome downloads folder?

 

[Stetim]

Are you asking for code to determine the current Chrome downloads folder?

Yes please, but only for the specific files. It needs to revert back to a default downloads folder after the macro is finished.


This is the code I am running.
All three modules are run continiously from a Call module and does everything I want except for putting the files in the correct folder.

Sub CreateNewFolder()
Dim chromeFileLocation As String
Dim hyperlink As String

'front page of submissions with address and application number'

hyperlink = "DYNAMIC URL TO ORGANISATION WEBPAGE"
chromeFileLocation = """C:\Program Files\Google\Chrome\Application\chrome.exe"""
Shell (chromeFileLocation & "-url " & hyperlink)
Application.Wait (Now() + TimeValue("00:00:05"))
SendKeys ("^a")
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys ("^c")
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys ("^w")
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys ("%{TAB}")
ActiveSheet.Range("a1").Select
ActiveSheet.Paste
MkDir "C:\consents\" & Range("a52") & " - " & Range("a50")

'Document upload page'

hyperlink = "DYNAMIC URL TO ORGANISATION WEBPAGE"
chromeFileLocation = """C:\Program Files\Google\Chrome\Application\chrome.exe"""
Shell (chromeFileLocation & "-url " & hyperlink)
Application.Wait (Now() + TimeValue("00:00:05"))
SendKeys ("^a")
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys ("^c")
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys ("^w")
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys ("%{TAB}")
ActiveSheet.Range("a1").Select
ActiveSheet.Paste

'isolates relevant data set'

Set Rng = ActiveSheet.UsedRange
Blank_Cells_Column = 5
For i = Rng.Rows.Count To 1 Step -1
If Rng.Cells(i, Blank_Cells_Column) = "" Then
Rng.Cells(i, Blank_Cells_Column).EntireRow.Delete
End If
Next i
Dim r As Long, lr As Long
lr = Cells(Rows.Count, 5).End(xlUp).Row
For r = lr To 2 Step -1
If InStr(Cells(r, 5), "Accepted") = 0 Then Rows(r).Delete
Next r
Rows(1).EntireRow.Delete

End Sub

'Convert embedded download hyperlinks to text hyperlinks'

Sub ExtractURLtwo()


ActiveSheet.UsedRange.Columns(7).Select
Dim Rng As Range
For Each Rng In Selection
Rng.Offset(0, 1).Value = Rng.Hyperlinks(1).Address
Next Rng

End Sub

'open list of download links, which downloads to default chrome download folder'

Sub Opentabstwo()
ActiveSheet.UsedRange.Columns(8).Select
Dim chromeFileLocation, hyperlink As String
Dim Rng, SelectRange As Range
chromeFileLocation = """C:\Program Files\Google\Chrome\Application\chrome.exe"""
On Error Resume Next
Set SelectRange = Application.Selection
For Each Rng In SelectRange
hyperlink = Rng.Item(1)
Shell (chromeFileLocation & "-url " & hyperlink)
Application.Wait (Now() + TimeValue("00:00:01"))
Next
End Sub

 

[John_w]

Yes please, but only for the specific files. It needs to revert back to a default downloads folder after the macro is finished.

But now it seems you're asking for code to change the Chrome downloads folder and change it back to the default. My reply was about reading the Chrome downloads folder setting, not changing it.

Here is a function which reads the current Chrome downloads folder setting:

Public Function Get_Chrome_Downloads_Folder() As String

    Dim ChromePreferences As String
    Dim json As String
    Dim settings As String
    Dim p1 As Long, p2 As Long
    Dim parts As Variant
    
    'Read Chrome Preferences file
    
    ChromePreferences = Environ("LOCALAPPDATA") & "\Google\Chrome\User Data\Default\Preferences"
    Open ChromePreferences For Binary As #1
    json = Space(LOF(1))
    Get #1, , json
    Close #1
    
    'Chrome download settings if default download directory hasn't been changed - "default_directory" setting is missing
    '   "download":{"directory_upgrade":true,"prompt_for_download":true},
    
    'Chrome download settings if default download directory has been changed - "default_directory" setting is present
    '   "download":{"default_directory":"C:\\folder\\path","directory_upgrade":true,"prompt_for_download":true
    
    parts = Split(json, """download""")
    parts = Split(parts(1), """default_directory""")
    If UBound(parts) >= 1 Then
        'Download folder has been changed from the default
        p1 = InStr(parts(1), """")
        p2 = InStr(p1 + 1, parts(1), """")
        Get_Chrome_Downloads_Folder = Replace(Mid(parts(1), p1, p2 - p1 + 1), Chr(34), "")
        Get_Chrome_Downloads_Folder = Replace(Get_Chrome_Downloads_Folder, "\\", "\")
    Else
        'Download folder is the default, i.e. C:\Users\[UserName]\Downloads
        Get_Chrome_Downloads_Folder = Environ("USERPROFILE") & "\Downloads"
    End If
   
End Function

Call it like this:

Public Sub Test()

    Dim ChromeDownloadsFolder As String
    
    ChromeDownloadsFolder = Get_Chrome_Downloads_Folder
    MsgBox ChromeDownloadsFolder
    
End Sub

So, instead of changing the Chrome downloads folder for each set of files, with the above function you could read the current Chrome downloads folder setting, download all the files to that folder, then move the files to the appropriate destination folders using the VBA Move statement, or FileSystemObject MoveFile method.

 

[Jaafar Tribak]

Hi John,

I hope I am not hijacking this thread.

Using Win10 x64 , I can't find the Chrome Preferences file.

The path you provided in your code doesn't exist for me . The closest I could get is looking in %ProgramFiles%\Google\Chrome\Application\
But that folder doesn't seem to have a Default sub folder.

AFAIK, I never changed any of the chrome defaults settings.

Regards.

Edit: Also looked in C:\Users\hp\AppData\Local\Google\Chrome\User Data (not there either)

 

[John_w]

Using Win10 x64 , I can't find the Chrome Preferences file.

The path you provided in your code doesn't exist for me . The closest I could get is looking in %ProgramFiles%\Google\Chrome\Application\
But that folder doesn't seem to have a Default sub folder.

Hi Jaafar,

That's a bit odd. I got the Chrome info from here:

Get chrome downloads folder

The default downloads folder in Windows 7 is c:\users\username\downloads for Chrome. But default downloads folder can be set to another folder through settings. I need to find out which folder is the

stackoverflow.com

On my Win 10 and Win 11 machines the Preferences file is in C:\Users\<Username>\AppData\Local\Google\Chrome\User Data\Default\

 

[Stetim]

Hi John

Thanks for your input so far.

So, instead of changing the Chrome downloads folder for each set of files, with the above function you could read the current Chrome downloads folder setting, download all the files to that folder, then move the files to the appropriate destination folders using the VBA Move statement, or FileSystemObject MoveFile method.

This is an option, Not completely ideal as it needs a built in delay to ensure that downloads are complete before being moved and a few other risk factors. Might throw in some count functions to help reliability.

Any thoughts on how I would ensure only the latest downloaded set could be isolated? It is not uncommon to have similarly named files with windows added version numbers. I need to ensure a single copy of the latest version is stored. Pic below of the file folder and online portal for example. On some projects I might have the same set of specifications downloaded multiple times and often updated versions are submitted without changing the file name, but I only need to save the last (accepted) version to my server

 

[John_w]

This is an option, Not completely ideal as it needs a built in delay to ensure that downloads are complete before being moved and a few other risk factors. Might throw in some count functions to help reliability.

But the alternative of changing the Chrome downloads folder would have the same problem; the code would have to ensure that a set of downloads is complete before changing the folder for the next set of downloads.

How does this code download the files?

'open list of download links, which downloads to default chrome download folder'

Sub Opentabstwo()
    ActiveSheet.UsedRange.Columns(8).Select
    Dim chromeFileLocation, hyperlink As String
    Dim Rng, SelectRange As Range
    chromeFileLocation = """C:\Program Files\Google\Chrome\Application\chrome.exe"""
    On Error Resume Next
    Set SelectRange = Application.Selection
    For Each Rng In SelectRange
    hyperlink = Rng.Item(1)
    Shell (chromeFileLocation & "-url " & hyperlink)
    Application.Wait (Now() + TimeValue("00:00:01"))
    Next
End Sub

When I run Chrome from the command line (Shell) with " -url http://www.files.com/file.txt" it opens Chrome with a Save As dialogue to download the file and therefore manual intervention is needed.

Any thoughts on how I would ensure only the latest downloaded set could be isolated? It is not uncommon to have similarly named files with windows added version numbers. I need to ensure a single copy of the latest version is stored.

Delete all the files in the Chrome downloads folder before downloading each set of files.