Refresh BackgroundQuery fails

[JMMach]

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

 

[Dick Kusleika]

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.

 

[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

 

[keepITcool]

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;PageTimeout=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 :