How to write a macro using a user selected filename when reading a textfile

[news.sintef.no]

I want to write a simple macro that allows the user to select a filename
(i.e. FileNameSelected.txt) before the following OpenText macro command is
executed. How do I "transfer" the user selected filename into this macro
command?


Workbooks.OpenText Filename:= "c:\data\FileNameSelected.txt", Origin
:=xlMSDOS, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(9, 1),
......), TrailingMinusNumbers:=True

Thanks for any suggestions.


Frank Krogh

 

[Kevin Beckham]

Dim strFilename As String

strFilename = Application.GetOpenFilename("File
type,*.txt", , "Select your file")
if strFilename <> "False" then
Workbooks.OpenText strFilename

Kevin Beckham

-----Original Message-----
I want to write a simple macro that allows the user to select a filename
(i.e. FileNameSelected.txt) before the following OpenText macro command is
executed. How do I "transfer" the user selected filename into this macro
command?


Workbooks.OpenText

Filename:= "c:\data\FileNameSelected.txt", Origin

 

[Nikos Yannacopoulos]

Frank,

try this line of code before your Workbooks.Open statement, and change the
latter to accomodate a variable rather than a hardcoded filename, as
follows:

fnam = Application.GetOpenFilename
Workbooks.OpenText Filename:= fnam, Origin:=xlMSDOS, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(9, 1), .....),
TrailingMinusNumbers:=True

Alternatively, you could have the used type the the full path and file name
in a sheet cell and, with the cell selected, run the code:
fnam = Activecell.Value
Workbooks.OpenText Filename:= fnam, Origin ect etc etc

Personally I find the first looks far more professional Still, the second
option can be very convenient if the user has to choose from a relatively
stable list of files (which needs to be added to occasionally?).

HTH,
Nikos

 

[Mike Fogleman]

This will open the directory browser and let the user select any .txt file
and open it.

Dim fileToOpen

fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
Workbooks.Open filename:=fileToOpen

Mike