automatically selecting folder for "FldrPicker"

[B-Man]

I'm using this formula - Loop Through All Excel Files In A Given Folder — TheSpreadsheetGuru

and want to have it automatically select the folder location with no user input
it started off like this

  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

I added the line

              .InitialFileName = "C:\Users\Admin\register\batch\test\"

  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker) into it.

    With FldrPicker
      .Title = "Select A Target Folder"
      .InitialFileName = "C:\Users\Admin\register\batch\test\"

.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With[/CODE]


this defaults to that directory so I still have to click ok... not a big issue. close enough work around BUT it causes an issue with saving somehow.

I figured out if I set it to this and then opened the test folder in the dialog box it ran through and saved fine. not sure of the issue

              .InitialFileName = "C:\Users\Admin\register\batch\" ' removed test folder

So can I either fix the current issue our be able to set a target folder and it automatically just works from that?

 

[Anthony47]

Could you be more clear? Because I seem you are asking how to write

myPath = "C:\Users\Admin\register\batch\test\"
myPath2 = "C:\Users\Admin\register\batch\"

Bye

 

[B-Man]

im a little confused how else to explain it.

if I set it as this it breaks the macro and wont save (which is part of the macro).

.InitialFileName = "C:\Users\Admin\register\batch\test\"

I feel like it has caused an issue with the file path

'Save File With Naming 
thisfile = ThisWorkbook.Path & Application.PathSeparator & "Batch " & Range("C3").Value & " (" & Left(Range("C4"), Len(Range("C4")) - 6) & ")"
Debug.Print thisfile
wsDest.SaveAs Filename:=thisfile

If i set it like this it opens the dialog box to the previous folder and then I click the test folder within.... (same folder as above) it doesnt break my macro and automatically saves

.InitialFileName = "C:\Users\Admin\register\batch\"

I was hoping to bypass all this and have it automatically select the directory without any human intervention ie. no dialog box. like I have to do now. failing that how do I fix the issue above.

 

[Anthony47]

May I suggest that you describe briefly your process and your goal; then we will discuss about how doing that

 

[B-Man]

I'm trying to open all excel files in a folder and copy data from them into the Master excel file, save it and then rerun it with the next one.

I'm basically updating the workbooks layout and formulas etc but keeping the original data.
by using the master excel file setup how I want and and formulas etc and any bugfixes fixed.
I Use a vba to copy the data from each file separately into the master file. save it. Then load the next one and repeat

 

[Anthony47]

And about which part of the process are you looking for suggestions?
Selecting the folder? Looping through the files? Importing them to the Master workbook? Saving the result?