Opening files through dialog boxes

[etpeterson]

My current spreadsheet takes a file path that the user inputs into a cell, ex.(D:\test_log\01-19-2005) into cell "B5." In that folder are numerous text files. I currently have the spreadsheet open 1.txt-10.txt in the folder specified by the user, after they push I button I have linked to my macro. What I would like to do is have the user push the button and a dialog box come up asking the user where to look for the .txt files. I don't want them to have to type in the path, but just "browse" through their file structure and click on a folder. The variable that this dialog box saves the information to needs to be compatible with this line:

Workbooks.OpenText Filename:=Range("B5") & "\1.txt"

Where Range("B5") is the name of the folder. (This is the old way I was doing it.)

I hope I gave enough information for someone to understand what I'm doing and is able to help.

Thanks so much!!!

Eric

 

[etpeterson]

I still am looking to do the same thing, but my code has changed since my last post. I now use filesearch to find the .txt files I want to open. This way I can open any number of files. However, I still have the user type in the path of the directory filesearch uses into a cell. This is what my new code looks like:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set FS = Application.FileSearch

With FS
.NewSearch
.LookIn = ws.Range("B5").Value
.Filename = "*.txt"
n = .Execute
End With

Dim counter
For counter = 1 To n
Workbooks.OpenText Filename:=(FS.FoundFiles(counter)) _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Sheets(1).Move After:=Workbooks("Fault Log Creator.xls").Sheets(counter)
Call Auto_Format
ws.Select
Next counter

The line:
.LookIn = ws.Range("B5").Value

Is the line I need to change. ws.Range("B5").Value references a cell and I would like to be able to add a dialog box command here that would return a file path by browsing through your file structure, just like the fileopen dialog box built into excel, but would return a directory.

Thanks for the help,
Eric

 

[sbendbuckeye]

Check out the Application.GetOpenFileName and Application.GetSaveAsFileName methods. Good Luck!