PDA

View Full Version : VBA code to find current user default download folder, then open a specific file



dactyls
02-01-2017, 03:27 PM
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.


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 :)!

JKwan
02-01-2017, 03:39 PM
get this a shot


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

dactyls
02-01-2017, 03:58 PM
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!

JKwan
02-01-2017, 04:44 PM
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?

dactyls
02-01-2017, 05:11 PM
It is usually a user defined location set in the chrome settings menu.
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.

Paul_Hossler
02-01-2017, 05:13 PM
Try something like this




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

dactyls
02-02-2017, 09:17 AM
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!

Paul_Hossler
02-02-2017, 09:21 AM
You could probably integrate the code something like this






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"

dactyls
02-02-2017, 09:39 AM
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.