D
Dave
Hi,
I'm using Excel 2003
I want to automate the importing of data from a .txt file, but the .txt file
won't always be in the same place.
When I do it manually, (with the macro recorder on), I use:
Data > Import External Data > Import Data
The 'Select Data Source' dialogue box appears, I find the file I want, hit
OK, then the 'Text Import Wizard' box comes up, etc.
Problem is, the macro records that specific path.
What I would like the macro to do is to ask the user to find the file, then
automatically do the Text Import Wizard stuff.
Is this possible?
Here is the code so far.
Range("AA1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\User 4\Desktop\New Quote Sheet\Pick
Place for JRB001078B DDU.txt" _
, Destination:=Range("AA1"))
.Name = "Pick Place for JRB001078B DDU"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Regards - Dave.
I'm using Excel 2003
I want to automate the importing of data from a .txt file, but the .txt file
won't always be in the same place.
When I do it manually, (with the macro recorder on), I use:
Data > Import External Data > Import Data
The 'Select Data Source' dialogue box appears, I find the file I want, hit
OK, then the 'Text Import Wizard' box comes up, etc.
Problem is, the macro records that specific path.
What I would like the macro to do is to ask the user to find the file, then
automatically do the Text Import Wizard stuff.
Is this possible?
Here is the code so far.
Range("AA1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\User 4\Desktop\New Quote Sheet\Pick
Place for JRB001078B DDU.txt" _
, Destination:=Range("AA1"))
.Name = "Pick Place for JRB001078B DDU"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Regards - Dave.