need user input command in macro

  • Thread starter Thread starter Phil Wagner
  • Start date Start date
P

Phil Wagner

Hello -

I have a macro that works on my computer but not other user's computers who
are on a network. I'm importing a file using the Data, Import External
Data, Import Data. In the macro's Select Data Source screen I've indicated
I want it to look in the My Data Sources Directory and get the newdata.txt
file.

I'd like to set up the macro to ask the user to select the directory and
file in the Select Data Source screen, then continue through the rest of the
macro.

Here's the first part of the code:

' Keyboard Shortcut: Ctrl+d
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\pwagner\My Documents\My Data
Sources\newdata.txt" _
, Destination:=Range("A2"))
.Name = "newdata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells

TIA for hour help!
 
Dim myFileName as Variant
myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.txt")

If myFileName = False Then
'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfilename, _
Destination:=Range("A2"))
.Name = "newdata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.....
 
Back
Top