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.

--
**** Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.*****-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

[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
"**** Kusleika" <dickieboy5@cox.net> wrote in message
news:eVRI0svbFHA.2736@TK2MSFTNGP12.phx.gbl...
> 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.
>
> --
> **** Kusleika
> MVP - Excel
> Excel Blog - Daily Dose of Excel
> www.*****-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
>
>

[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 :

> 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
> "**** Kusleika" <dickieboy5@cox.net> wrote in message
> news:eVRI0svbFHA.2736@TK2MSFTNGP12.phx.gbl...
> > 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.
> >
> > --
> > **** Kusleika
> > MVP - Excel
> > Excel Blog - Daily Dose of Excel
> > www.*****-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
> >
> >