GetFile Macro

  • Thread starter Thread starter Michele
  • Start date Start date
M

Michele

I'm new to VB...Need to alter macro to open text file
(will be diffent file each week). I currently have macro
set to import a specific text file and format it. I want
to have it ask which file. Found this code:

Sub Get_File()
fileToOpen = Application.GetOpenFilename('All Files
(*.*),*.**")
Workbooks.OpenText Filename:=fileToOpen
EndSub

Here is the beginning of the macro, where do I insert the
above code?
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\My Documents\MyFile.txt" _
, Destination:=Sheets("Sheet1").Range("A1"))
.Name = "ExternalData_1"
.FieldNames = True
.RowNumbers = False

Thanks very much.
 
Michele,

Try this

Dim sFile As String

sFile = InputBox("Please supply filename")
If Not IsEmpty(sFile) Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & sFile _
, Destination:=Sheets("Sheet1").Range("A1"))
.Name = "ExternalData_1"
.FieldNames = True
.RowNumbers = False
End With
End If


--

HTH

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