user to select path
 

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.

user to select path
 

Option Explicit
Sub testme()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.txt")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

With ActiveSheet
'.Range("AA1").Select 'no need to select
With .QueryTables.Add(Connection:= _
"TEXT;" & myFileName, 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 With

End Sub



Dave wrote:

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.

--

Dave Peterson

user to select path
 

Hi Dave,
Exactly what I needed.
Thanks a lot!
Regards - Dave.

user to select path
 

hi
i may be late but...
the code your are running creates a new query table each time it is run. if
you are connecting to the same data source, then you need not run it a second
time, only refresh the querytable.
but i suspect that you are importing different text files from different
places so i would strongly advise that you add code to delete the old
querytable and it's named range reference before you run the code "as is" to
prevent any conflict.

Regards
FSt1


"Dave" wrote:

Hi Dave,
Exactly what I needed.
Thanks a lot!
Regards - Dave.

user to select path
 

Hi FSt1
I am importing the text data into a workbook which is generated by a
template. Data will only be imported once into each workbook, although the
template will be used by many people to generate many workbooks (one for each
job). The macro is for the template.
Regards - Dave.

"FSt1" wrote:

hi
i may be late but...
the code your are running creates a new query table each time it is run. if
you are connecting to the same data source, then you need not run it a second
time, only refresh the querytable.
but i suspect that you are importing different text files from different
places so i would strongly advise that you add code to delete the old
querytable and it's named range reference before you run the code "as is" to
prevent any conflict.

Regards
FSt1


"Dave" wrote:

Hi Dave,
Exactly what I needed.
Thanks a lot!
Regards - Dave.

user to select path
 

hi
well and good. and i hope everything works out for you and my advice not
needed. but i would keep my suggestion for future use just in case.

Regards
FSt1

"Dave" wrote:

Hi FSt1
I am importing the text data into a workbook which is generated by a
template. Data will only be imported once into each workbook, although the
template will be used by many people to generate many workbooks (one for each
job). The macro is for the template.
Regards - Dave.

"FSt1" wrote:

hi
i may be late but...
the code your are running creates a new query table each time it is run. if
you are connecting to the same data source, then you need not run it a second
time, only refresh the querytable.
but i suspect that you are importing different text files from different
places so i would strongly advise that you add code to delete the old
querytable and it's named range reference before you run the code "as is" to
prevent any conflict.

Regards
FSt1


"Dave" wrote:

Hi Dave,
Exactly what I needed.
Thanks a lot!
Regards - Dave.