selecting a path to use instead of hardcoded in the code

Javi

Active Member
Joined
May 26, 2011
Messages
438
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.










Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. Windows
How about trying this:

Code:
Dim str As String

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

MsgBox str
 

Javi

Active Member
Joined
May 26, 2011
Messages
438
Thank you..... I appreciate the help. Worked great just was looking for.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,205
Messages
5,594,830
Members
413,942
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top