VBA code to find current user default download folder, then open a specific file

I'm attempting to dummy proof a macro that will take a downloaded .csv file and import the data then delete itself. I can do this easily on my machine since I know the file paths but making it work for the computers I am sending it to will be another story as the current location of the download folder may be changed.

Code:

---

Sub Macro1()


    Workbooks.Open Filename:="E:\Downloads\Form1.csv"
    ActiveCell.FormulaR1C1 = "Reference #"
    Cells.Select
    Selection.Copy
    Windows("testing.xlsm").Activate
    Sheets("IMPORT").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DATA ").Select
End Sub

---

This is my current macro to copy the downloaded csv to the workbook and set up the rearrangement of the data. What I would like to do is search the registry and replace the E:\Downloads\ with whatever the current user's download folder path is so I can use the form there and then after the import of the data use the same path to delete the file.

Any help would be appreciated :)!

get this a shot

Code:

---

Sub GetDownloadFolder()
    Const DESKTOP = &H10&
   
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(DESKTOP)
    Set objFolderItem = objFolder.Self
    temp = objFolderItem.Path
    temp = Left(temp, Len(temp) - 7) & "Downloads" '<--- I believe this is the download folder
    MsgBox temp
End Sub

---

This does give me a popup box with my C:\User\Name\Downloads folder but that is not where my items download by default. But it does point out to me that I'm likely approaching this with flawed logic. All of the users will be using Google Chrome as we set up direct links to our VPN through chrome. Is it possible to use VBA to extract the chrome users' default download location for this purpose?

Thanks for the reply JKwan. You answered by question and exposed my flawed logic all at once, my wife is proud of you!

Well, I don't use or have Chrome, so I don't know. Maybe you can give me examples of these folders....

Or or is the folder all over the place, based on the user preference?

It is usually a user defined location set in the chrome settings menu.
Attachment 18214
It can be changed on the fly, but so far I haven't located where that information is stored if it is stored locally at all.

Try something like this

Code:

---

Option Explicit
Sub drv()
    MsgBox ChromeDownloadFolder
End Sub

Function ChromeDownloadFolder()
    Dim sPref As String
    Dim iFile As Long, iStart As Long, iEnd As Long
    Dim sBuffer As String, sSearch As String, sDownloads As String

    'Chrome preferces file, no extension
    sPref = Environ("LOCALAPPDATA") & "\Google\Chrome\User Data\Default\Preferences"
   
    'marker
    sSearch = """download"":{""default_directory"":"

    'read the whole file into buffer
    iFile = FreeFile
    Open sPref For Input As #iFile
        sBuffer = Input$(LOF(iFile), iFile)
    Close #iFile

    'find start of marker
    iStart = InStr(1, sBuffer, sSearch, vbTextCompare)
   
    'find comma
    iEnd = InStr(iStart + Len(sSearch), sBuffer, ",", vbTextCompare)

    'pull out path
    sDownloads = Mid(sBuffer, iStart + Len(sSearch) + 1, iEnd - iStart - Len(sSearch) - 2)

    'remove double back slashes
    ChromeDownloadFolder = Replace(sDownloads, "\\", "\")
End Function

---

Thank you for replying Paul!
I've been toying with this for 2 days (the first 2 days of toying with VBA at all) and this let me dump the file name into my sheet to pull from with my macro. This is great and has shown me there is still a TON for me to learn. Currently I understand about 30% of what each section of code does but it works for now and I'll be studying what you did so I can learn from it.

Thanks again!

You could probably integrate the code something like this

Code:

---


Sub OpenChromeDownload
    Dim sPref As String
    Dim iFile As Long, iStart As Long, iEnd As Long
    Dim sBuffer As String, sSearch As String, sDownloads As String
   
    'Chrome preferces file, no extension
    sPref = Environ("LOCALAPPDATA") & "\Google\Chrome\User Data\Default\Preferences"
   
    'marker
    sSearch = """download"":{""default_directory"":"
   
    'read the whole file into buffer
    iFile = FreeFile
    Open sPref For Input As #iFile
    sBuffer = Input$(LOF(iFile), iFile)
    Close #iFile
   
    'find start of marker
    iStart = InStr(1, sBuffer, sSearch, vbTextCompare)
   
    'find comma
    iEnd = InStr(iStart + Len(sSearch), sBuffer, ",", vbTextCompare)
   
    'pull out path
    sDownloads = Mid(sBuffer, iStart + Len(sSearch) + 1, iEnd - iStart - Len(sSearch) - 2)
   
    'remove double back slashes
    sDownloads= Replace(sDownloads, "\\", "\")

    Workbooks.Open Filename:=sDownLoads & "\Form1.csv"

---

Currently I have the a cell running the function to retrieve the file path and using concatenate function to add the Form1.csv name, then using that to begin my initial macro. It is less elegant for sure but I really appreciate your continued feedback, I wouldn't have thought to turn the function into a subroutine.