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

  • Thread starter Thread starter news.sintef.no
  • Start date Start date
N

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
 
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
Click to expand...
Filename:= "c:\data\FileNameSelected.txt", Origin
 
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
 
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
 
Back
Top