Problem executing Excel QueryTables from Access

[Terrell Miller]

Help!

I have a VBA function that works fine as a standalone in
Excel VBA, but crashes if I run it in a virtual copy of
Excel from Access:

I have an Access 2002 database where I need to open an
instance of Excel, open a template file, rename it, then
open a QueryTable that will pull in the contents of a tab-
delimited file to cell A2, then save and close the
spreadsheet and garbage-collect the Excel object.

Here's the code:

'----------------------
Function bLinkToSWINTemplate(sFileName As String) As
Boolean

'Called by cmdContinue_Click handler on frmFileDialog
'Instantiates Excel, opens template s/s, saves

Dim objXL As New Excel.Application, sDateString As String,
dNow As Date
Const sRange As String = "import_start"

On Error GoTo ErrTrap

dNow = Now()
sDateString = Year(dNow) & sConvertInt(Month(dNow)) &
sConvertInt(Day(dNow))

With objXL
.Application.Visible = False
.Workbooks.Open FileName:=sXLTemplate,
UpdateLinks:=False, ReadOnly:=False
.Workbooks(1).SaveAs sXLPopulated & sDateString &
sXLFile 'check if already exists
'populate Excel spreadsheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" &
sFileName, Destination:=Range(sRange))
.NAME = sFileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
.Workbooks(1).Save
.Workbooks(1).Close
.Application.Visible = True
End With

GoTo Cleanup

ErrTrap:
ErrorHandler

Cleanup:
'cleanup Excel object
objXL.Quit
Set objXL = Nothing

End Function
'----------------------

First time I ran this it worked fine. Every time since, it
bombs on the With ActiveSheet.QueryTables.Add line with an
error code 1004: "Application-defined or Object-defined
error".

But if I copy the With ActiveSheet.QueryTables.Add block
to a subroutine inside the Excel spreadsheet I'm trying to
use and run it, it works fine.

The filepath is "\\....." instead of a mapped drive letter
("G:\..."), but that works fine in Excel. The text file is
there and has not been renamed or changed in any way.

I checked the spreadsheet and it does not have any
QueryTables members defined yet (the template doesn't have
them, and the file has been saved under a different name
by the time the With ActiveSheet.QueryTables.Add executes).

Any ideas why this doesn't work from Access?

Many thanks,

Terrell

 

[Douglas J. Steele]

Access doesn't know what ActiveSheet is.

I don't know whether or not this will solve the problem, but try putting a
period in front of ActiveSheet, so that it becomes a property of the Excel
application.