Macro / VBA Help needed

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

I'm writing a macro to start the import wizard, and want
to use the contents of cell B1 as the filename to import.
I turned on record macro, copied the filename from my
sheet, and pasted it in the filename entry of the wizard.
But I want to declare it as a variable instead. I can add
Dim strFName as String
to my code, but that's the limit of my VBA knowledge. I'm
guessing I have to assign the value of cell B1 to
strFName, and then use strFName in the code instead of the
hardcoded filename, but I don't know the syntax.

Can anyone help?
Sub Scratch()
'
' Scratch Macro
' Macro recorded 2/23/2004 by Beth Holback
'
Range("B1").Select
Selection.Copy
Workbooks.OpenText Filename:="C:\Temp\idocerr2.txt",
Origin:=xlWindows, etc, etc,

End Sub
 
Beth,

Something like

Sub Scratch()
Dim strFName As String

strFName = Activesheet.Range("B1")
Workbooks.OpenText Filename:=strFName, Origin:=xlWindows, etc, etc,

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
one way:

Workbooks.OpenText _
FileName:="C:\Temp\" & Range("B1").Text, _
Origin:=xlWindows, _
etc.

you can also use a variable if you wish:

Dim strFName As String
strFName = "C:\Temp\" & Range("B1").Text
Workbooks.OpenText _
FileName:=strFName, _
Origin:=xlWindows, _
etc.
 
Back
Top