selecting a path to use instead of hardcoded in the code

[Javi]

Hi All,

The below code loops through all of the workbooks in a folder and runs the specified (any code you wish) code. I would like to modify the code so that the path is not hard-coded.

I tried using a few different variations with no success. (ThisWorkbook.Path and strFolder = Range("C7").Value and strFile = Dir(strFolder & "*.xlsx").
I also put the in cell K1 "=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)" and attempted to reference cell K1 no luck



I would prefer for a open destination selection window to let me select the folder then the code would continue to run for all the worksheets in that folder.

Any help would be greatly appreciated.

Sub A_Loop_All_WB()
Dim file
Dim path As String


' Path to your folder. MY folder is listed below. I bet yours is different.
' make SURE you include the terminating "\"
'YOU MUST EDIT THIS.


path = "C:\Users\JR\Desktop\Jan 2018 Roll\6030\"


'Change this file extension to the file you are opening. .htm is listed below. You may have rtf or docx.
'YOU MUST EDIT THIS.
'file = Dir(ThisWorkbook.path & "*.Xlsm")


file = Dir(path & "*.xlsm")
Do While file <> ""
Workbooks.Open Filename:=path & file


' This is the call to the macro you want to run on each file the folder
Call A_0_RUN_Macro




' set file to next in Dir


ActiveWorkbook.Close SaveChanges:=True


file = Dir()
Loop


End Sub

 

[steve the fish]

How about trying this:

Dim str As String

With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then
        str = .SelectedItems(1) & "\"
    End If
End With

MsgBox str

 

[Javi]

Thank you..... I appreciate the help. Worked great just was looking for.

Sub A_Loop_All_WB()
Dim file
Dim path As String


Dim str As String


With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then
        str = .SelectedItems(1) & "\"
    End If
End With


MsgBox str


path = str




'Change this file extension to the file you are opening. .htm is listed below. You may have rtf or docx.


file = Dir(str & "*.xlsm")
Do While file <> ""
Workbooks.Open Filename:=path & file


' This is the call to the macro you want to run on each file the folder
Call A_0_RUN_Macro




' set file to next in Dir


ActiveWorkbook.Close SaveChanges:=True


file = Dir()
Loop


End Sub