Application.GetOpenFileName - Force macro to look in logged in user's download folder ?

[netrixuser]

I hope that made sense !
I have a working script that opens up three Worksheets [report sheets from various applications], renames them, does a bunch formatting and adds a few formulas - all is good except the three files have to be named in a certain way and have to be in the Root directory of the C: drive in order for my script to work.

When the three worksheets are downloaded, the filename contains a date so every month the filenames will be different - hence I ask the users to rename them and place them in the root of C:
I would like the macro to ask where the files are first, so the filenames and location is irrelevant as the user can browse to the files - and upon Googling I found this code, wich is a good start but I need to tweak it slightly. The code appears to open up the last folder that the user opened up, so I would like to force it to look in the Downloads folder (which is the default for the Worksheets to be downloaded to) The issue I have is that the path to the Download folder contains the Users Windows Name.

This is the code I found:

[COLOR=#333333]Sub OpenFileDialog()[/COLOR]' Defines variables
Dim TargetFile As Variant


' Open dialogue box to browse for file
TargetFile = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")


' If no file is selected then...
If TargetFile = False Then
    ' Display message box with an error
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    ' Exit the macro
    Exit Sub
' Else if a valid file is selected then...
Else
    ' Open the selected workbook
    Workbooks.Open FileName:=TargetFile
End If

 [COLOR=#333333]End Sub[/COLOR]

I have tried the ChDir command but cannot figure out how to add a variable to the ChDir statement so it would read something like:

ChDir "C:\Users\USER'S WINDOWS NAME HERE\Downloads"

I have managed to get the Windows User Name into a cell in the Spreadsheet that runs the script (ie opens the three reports up etc) using:

UserName = Environ$("UserName")
Range("A1:A1").Select
    With Selection
        .Font.Color = vbWhite
ActiveCell = UserName
    End With
Range("A2:A2").Select

This puts my Windows Name in cell A1, changes the font colour to white etc but I still am unable to get the Windows Name into the path statement so that the Application.GetOpenFileName always looks in the correct directory !
HELLPP !!

Thanks for any advice

NetrixUser

 

[Yongle]

Sub OpenFileDialog() ' Defines variables
    Dim TargetFile As Variant
   [COLOR=#ff0000][I] ChDir "C:\Users\" & Environ$("UserName") & "\Downloads"[/I][/COLOR]
    ' Open dialogue box to browse for file
    
    TargetFile = Application.GetOpenFilename _
            (Title:="Please choose a file to open", _
                FileFilter:="Excel Files *.xls* (*.xls*),")
    
    ' If no file is selected then...
    If TargetFile = False Then
        ' Display message box with an error
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        ' Exit the macro
        Exit Sub
    ' Else if a valid file is selected then...
    Else
        ' Open the selected workbook
        Workbooks.Open Filename:=TargetFile
    End If
 End Sub

 

[netrixuser]

Many thanks Yongle !!

 

[netrixuser]

Thanks again Yongle - I'm hoping you or others can provide further help, VBA is a steep learning curve for me - but hopefully I'll get there !
My original code automatically opened up three workbooks - added them to the existing workbook as sheets 2,3 and 4 and renamed the three tabs as Accsys Report, Fonetic Report and Cognia Report. As mentioned in the OP; the files had to be in the Root of C:\ and specifically named before running the macro.

Yongle helped me out with code to get the user to select the downloaded file(s) but I now have issues trying to get the files into the same spreadsheet as named tabs.
Here is the original code that opened up the three spreadsheets, renamed the tabs etc. It worked but Its probably not written as economically as it could be. [My spreadsheet containing the macro has 1 sheet named "Index"]

    Sheets("Index").Select    
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:="C:\Active MRL"
    ActiveSheet.Name = "Accsys Report"
    Sheets("Accsys Report").Copy After:=Workbooks(ControlFile).Sheets(1)
    Windows("Active MRL").Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate




    Sheets("Index").Select
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:="C:\Fonetic Report"
    ActiveSheet.Name = "Fonetic Report"
    Sheets("Fonetic Report").Copy After:=Workbooks(ControlFile).Sheets(1)
    Windows("Fonetic Report").Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate
    
    
    Sheets("Index").Select
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:="C:\Cognia Report"
    ActiveSheet.Name = "Cognia Report"
    Sheets("Cognia Report").Copy After:=Workbooks(ControlFile).Sheets(1)
    Windows("Cognia Report").Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate

After this code has run I have a spreadsheet with four tabs: Index, Accsys Report, Fonetic Report and Cognia report.

Moving forward with the new code to get the user to select the files, my aim was to have the user select each of the three reports using the code below, three times, but I cannot work out how to get the newly opened worksheet to be the next tab in the original spreadsheet.

Dim TargetFile As Variant

    ChDir "C:\Users\" & Environ$("UserName") & "\Downloads"
        MsgBox "Please Select the Accsys report", , "SELECT THE ACCSYS REPORT"
' Open dialogue box to browse for file
    TargetFile = Application.GetOpenFilename _
    (Title:="Please choose a file to open", _
    FileFilter:="Excel Files *.xls* (*.xls*),")


' If no file is selected then...
    If TargetFile = False Then
        ' Display message box with an error
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        ' Exit the macro
        Exit Sub
' Else if a valid file is selected then...
    Else
        ' Open the selected workbook
        Workbooks.Open Filename:=TargetFile
    End If

If I'm honest I don't fully understand the original code - I managed to find/copy from another thread somewhere on t'internet !!

All help gratefully received

Regards

NetrixUser

 

[Yongle]

Thanks for your feedback


You have strayed well away from your original question - I suggest you ask your latest question on a new thread