Variables

[richie]

Hi,

Two quick questions:

1. How do you include variables incode? I want a macro to open a file
dependent on the filename/ path which the user inputs to a form cell
H20. My attempt is

Workbooks.OpenText FileName:="L:\bacs\" & ActiveSheet.Range("H20") &
"", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, OtherChar:=":",
FieldInfo:=Array( _
Array(0, 1), Array(4, 1))

2. How do you create a "flexible" macro that grows with the data?
Essentially I want to create a range A1:last but I don't know the code
for this.

Thanks for your help. Sorry my questions are so embarrassingly simple.

Richard

 

[Tom Ogilvy]

Workbooks.OpenText FileName:="L:\bacs\" & ActiveSheet.Range("H20"), _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, OtherChar:=":",
FieldInfo:=Array( _
Array(0, 1), Array(4, 1))

Should work if H20 contains something like filename.txt

Dim rng as Range
set rng = Range("A1").CurrentRegion