Macro prompt for filename

  • Thread starter Thread starter NealUK
  • Start date Start date
N

NealUK

I have asked a similar question before, but can't quite suss out how to do
it.
This is the macro from a spreadsheet i have. However, the file to open is
not always "a", and may be something random.

Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/adam/spool/a", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=
_
Array(Array(0, 1), Array(8, 1), Array(14, 1), Array(39, 1),
Array(45, 1), Array(54, 1))
ActiveWindow.SmallScroll Down:=12

Is there a way i can use the InputBox function to ask the user the filename,
assign it to a variable, and get the macro to open that file instead of "a".

I assumed it would be along the lines of

MMM = InputBox("Enter File Name", "Data entry", 10, 10)

Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/adam/spool/"
mmm, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=
_
Array(Array(0, 1), Array(8, 1), Array(14, 1), Array(39, 1),
Array(45, 1), Array(54, 1))
ActiveWindow.SmallScroll Down:=12

This doesn't seem to work. Does anyone know the correct syntax for this, as
i am having problems
any help much appreciated.
regards
Neal
 
Neal,

Try:

Filename:="ftp://xxx.xx.xxx.xxx/home/adam/spool/" & MMM

As a general rule, it's better to use descriptive variable names. Perhaps
UserFilename, instead of MMM. Makes it easier to see what you were doing
when you look at the code a year down the road.
 
Back
Top