Folder Path Selection in VBA

[dancingcab]

Hi,

I have the following code:

Sub Batch_calc()


Dim sFile As String
Const sPath As String = "C:\Users\CAB\Desktop\Chrono\"
sFile = Dir(sPath & "*.txt")
Do While sFile <> ""
Workbooks.OpenText FileName:=sPath & sFile _
, DataType:=xlDelimited, Tab:=True
result = ActiveWorkbook.Name
Call CopyCalc
ActiveWorkbook.Close True
sFile = Dir()
Loop
End Sub

This opens a txt file in the given folder, which is currently a constant, carries out some analysis and saves it as a different name (all in the CopyCalc subroutine) and then closes the file and moves onto the next one in the same folder. Rather that having a fixed folder I'd like the user to be able to select which folder their files are stored in. Any help or pointers to other threads would be appreciated.

Thanks!

 

[Andrew Poulsom]

You can use FileDialog to prompt for a folder. Here's an example:

http://www.mrexcel.com/forum/excel-...o-sheet2-using-msofiledialogfolderpicker.html