I have a macro that imports data from an exported text file. And it works
like it is supposed to on a bunch of computers, except the customers. This
is my code:
With ActiveSheet.QueryTables.Add(Connection:= strImportFullName,
Destination:=Range("A1"))
.Name = "INVENT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'define the DataTypes to be TEXT:
' .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
' , 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
' .Refresh BackgroundQuery:=False
'DataTypes:
'1 General
'2 Text
'3 Date DYM
'4 Date DMY
'5 Date YMD
'6 Date MYD
'7 Date DYM
'8 Date YDM
'9 Skip
.TextFileColumnDataTypes = rayImportArray
.Refresh BackgroundQuery:=False

The line of code that fails is
.Refresh BackgroundQuery:=False

We have attempted to run this on Excel 97 and it failed, so we moved to an
Excel 2003 workstation and ran it, and much to my surprise, it too failed.
All I ended up with was a row of field names, but no data.

I am stumped. What do I have to change or activate to make this work?
Is there something that I can add to my code to be sure that whatever the
required element is, that it is activated so that the macro gets the
expected results?
Thanks in advance.
TTFN
JMMach

JMMach

If it works on your machine, but not on your customer's, the first thing to
check is strImportFullName. Make sure it's a properly formatted connection
string. One way you can do this is by recording a macro while creating the
external data table, then seeing how the connection string is created in the
recorded macro.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

JMMach wrote:
I have a macro that imports data from an exported text file. And it works
like it is supposed to on a bunch of computers, except the customers. This
is my code:
With ActiveSheet.QueryTables.Add(Connection:= strImportFullName,
Destination:=Range("A1"))
.Name = "INVENT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'define the DataTypes to be TEXT:
' .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
' , 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
' .Refresh BackgroundQuery:=False
'DataTypes:
'1 General
'2 Text
'3 Date DYM
'4 Date DMY
'5 Date YMD
'6 Date MYD
'7 Date DYM
'8 Date YDM
'9 Skip
.TextFileColumnDataTypes = rayImportArray
.Refresh BackgroundQuery:=False

The line of code that fails is
.Refresh BackgroundQuery:=False

We have attempted to run this on Excel 97 and it failed, so we moved to an
Excel 2003 workstation and ran it, and much to my surprise, it too failed.
All I ended up with was a row of field names, but no data.

I am stumped. What do I have to change or activate to make this work?
Is there something that I can add to my code to be sure that whatever the
required element is, that it is activated so that the macro gets the
expected results?
Thanks in advance.
TTFN
JMMach

I can tell you that the string is NOT the problem; the paths to the file are
correct.
It must have to do with some Reference Library, or the sequence of the
Reference Library, or some Add-in, or some component not being installed. I
need help to identify which of those it might be, so that I can solve this
little issue.
Thanks
TTFM
JMMach
"Dick Kusleika" wrote in message
...
JMMach

If it works on your machine, but not on your customer's, the first thing
to
check is strImportFullName. Make sure it's a properly formatted
connection
string. One way you can do this is by recording a macro while creating
the
external data table, then seeing how the connection string is created in
the
recorded macro.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

JMMach wrote:
I have a macro that imports data from an exported text file. And it
works
like it is supposed to on a bunch of computers, except the customers.
This
is my code:
With ActiveSheet.QueryTables.Add(Connection:= strImportFullName,
Destination:=Range("A1"))
.Name = "INVENT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'define the DataTypes to be TEXT:
' .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
' , 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2)
' .Refresh BackgroundQuery:=False
'DataTypes:
'1 General
'2 Text
'3 Date DYM
'4 Date DMY
'5 Date YMD
'6 Date MYD
'7 Date DYM
'8 Date YDM
'9 Skip
.TextFileColumnDataTypes = rayImportArray
.Refresh BackgroundQuery:=False

The line of code that fails is
.Refresh BackgroundQuery:=False

We have attempted to run this on Excel 97 and it failed, so we moved to
an
Excel 2003 workstation and ran it, and much to my surprise, it too
failed.
All I ended up with was a row of field names, but no data.

I am stumped. What do I have to change or activate to make this work?
Is there something that I can add to my code to be sure that whatever
the
required element is, that it is activated so that the macro gets the
expected results?
Thanks in advance.
TTFN
JMMach

look at the connectstring of both querytables.

These will look like:
'ODBC;DSN=Excel Files;DBQ=D:\My Documents\5000_1.xls;DefaultDir=D:\My
Documents;DriverId=790;MaxBufferSize=2048;PageTime out=5;

if the DSN "Excel Files" is not available on both machines
you may have a problem.

on windows XP:
via administrative tools\data sources you can add/change
ODBC settings. Usually you'll find the "Excel Files" under User DSN.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


JMMach wrote :

I can tell you that the string is NOT the problem; the paths to the
file are correct.
It must have to do with some Reference Library, or the sequence of the
Reference Library, or some Add-in, or some component not being
installed. I need help to identify which of those it might be, so
that I can solve this little issue.
Thanks
TTFM
JMMach
"Dick Kusleika" wrote in message
...
JMMach

If it works on your machine, but not on your customer's, the first
thing
to
check is strImportFullName. Make sure it's a properly formatted
connection
string. One way you can do this is by recording a macro while
creating
the
external data table, then seeing how the connection string is
created in
the
recorded macro.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

JMMach wrote:
I have a macro that imports data from an exported text file. And
it
works
like it is supposed to on a bunch of computers, except the
customers.
This
is my code:
With ActiveSheet.QueryTables.Add(Connection:= strImportFullName,
Destination:=Range("A1"))
.Name = "INVENT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'define the DataTypes to be TEXT:
' .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2,
2, 2,
2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2,
2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
' , 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2,
2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
' 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2,
2)
' .Refresh BackgroundQuery:=False
'DataTypes:
'1 General
'2 Text
'3 Date DYM
'4 Date DMY
'5 Date YMD
'6 Date MYD
'7 Date DYM
'8 Date YDM
'9 Skip
.TextFileColumnDataTypes = rayImportArray
.Refresh BackgroundQuery:=False

The line of code that fails is
.Refresh BackgroundQuery:=False

We have attempted to run this on Excel 97 and it failed, so we
moved to
an
Excel 2003 workstation and ran it, and much to my surprise, it too
failed.
All I ended up with was a row of field names, but no data.

I am stumped. What do I have to change or activate to make this
work? Is there something that I can add to my code to be sure
that whatever
the
required element is, that it is activated so that the macro gets
the expected results?
Thanks in advance.
TTFN
JMMach