Opening files through dialog boxes

etpeterson

New Member
Joined
Jan 19, 2005
Messages
18
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
 

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.

etpeterson

New Member
Joined
Jan 19, 2005
Messages
18
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

Active Member
Joined
Nov 26, 2003
Messages
440
Check out the Application.GetOpenFileName and Application.GetSaveAsFileName methods. Good Luck!
 

Forum statistics

Threads
1,148,047
Messages
5,744,493
Members
423,880
Latest member
CRE_finance_guy

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