Opening a database query for excel

[hawker]

If I run a query in excel through VB using the excel object library, it works fine the very first time I do it in my application. Subsequent runs I get the following error:
Error 1004 Method 'Range' of object '_Global' failed.
I am closing down the work book and application objects properly between the attempts. If I close down my app, and retry it works every time !
Any ideas please ???

[Blitz]

Did you close down the Excel objects and set them to Nothing?

[hawker]

yes. I've done the .Close on the workbook object, and .Quit on the application object etc. Doesn't seem to make any difference when I try it for the second and subsequent times.

[Blitz]

Is it possible for u to post the code?

Thanks..

[hawker]

I've managed to work around the problem. Instead of doing a new query, I firstly create a .xls file from my database and get my VB app to tell excel to open that file instead. It's a bit of a botch job, but there you go.
Anyway, here's the peice of code in question where it was going wrong using the original method....

Dim exApp As Excel.Application

On Error GoTo Err_Handler
Set exApp = CreateObject("Excel.Application")
exApp.Visible = True
exApp.SheetsInNewWorkbook = 1
exApp.Workbooks.Add
exApp.Worksheets.Add

' Build up the sql for the query
sSqlTxt = "SELECT description"
For i = 1 To nRepCols
sSqlTxt = sSqlTxt & ", value" & CStr(i)
Next i
sSqlTxt = sSqlTxt & " FROM DBA.ma_report ma_report"

' Build up the connection string
sConn = "ODBC;DSN=" & frmLogin.cmbDataBase.Text & ";UID=" & frmLogin.txtUID _
& ";PWD=" & frmLogin.txtPWD

sRange = "A1"

' This QueryTables.Add is ok when run for the first time,
' but after this, it always generated an error 1004
With exApp.ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:= _
Range(sRange))
.SQL = sSqlTxt
.Name = "Query from ltd"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

' Pause until user wishes to close excel
MsgBox "Click OK to close down your Excel session."

GoTo Exit_Sub

Err_Handler:
Call MyErrorHandler

Exit_Sub:
If Not exApp Is Nothing Then
exApp.DisplayAlerts = False
exApp.ActiveWorkbook.Close
exApp.Quit
Set exApp = Nothing
End If