Prompt for filename MACRO

Posted by Rick Alford on December 01, 2000 2:48 AM

I have a macro that opens a text file and auto formats it as a spreadsheet. My problem is that the file name will not always be the same or possibly in the same location for the users. How can I insert the code into this macro to prompt the user for a File /Open window that will allow them to pick the file?

Thanks for your Help... Rick

Posted by Rob on December 01, 2000 3:04 AM

I don't know if this wil work in your case but I have a similar routine which askes the user to input the filename and then opens it.

Dim FileOpen As String
FileOpen = InputBox("Enter the name of the file you want to open.", Default:="c:\")
Workbooks.OpenText FileName:=FileOpen, Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
1), Array(30, 1), Array(47, 1), Array(65, 1), Array(79, 1), Array(95, 1), Array(133, 1))

Posted by Tim Francis-Wright on December 01, 2000 1:18 PM

To suggest an addendum to Rob's answer"
I've found that Application.GetOpenFilename
is useful because the user sees a very familiar
interface. You then can take the filename
generated and procees it as you need.

Filter = "Text files (*.txt),*.txt"
Caption = "Please Select a File"
SelectedFile = Application.GetOpenFilename(Filter, , Caption)
Workbooks.OpenText FileName:=SelectedFile, Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
1), Array(30, 1), Array(47, 1), Array(65, 1), Array(79, 1), Array(95, 1), Array(133, 1))
' modify the parameters in the last line as you need

HTH

Posted by Rick Alford on December 01, 2000 5:36 PM

Tim this is exactly what I was looking for. It works like a charm.

Thanks