Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False)

[Anant]

Dear all,
I desperately need help. I have been working on this Excel automation
from VB. The task involves launching a set of Excel Addins, workbooks,
printing them and shutting everything down. All my workbooks shut down
with the usual quitting the excel object and setting it nothing,
except this one workbook. This workbook doesnt let excel shutdown
keeps lurking in my Task Manager. I traced the reason to one line of
code

xlApp.Sheets("myfirst").QueryTables(1).Refresh BackgroundQuery:=False

when i comment this out every thing shuts down. but i need the queires
refreshed before i print. I looked at what people had suggested for
this problem and specifically defined objects for workbook, worksheet,
querytable and once i was done with them set them to nothing. That
didnt work. im certain im missing something. Please please

regards,
Anant

(relevent section of code)

Sub Main()
Dim xlApp As Object
Dim myDate As Date
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
xlApp.DisplayAlerts = True
xlApp.Workbooks.Open ("D:\bin\mySheet.xls")
xlApp.Sheets("myfirst").QueryTables(1).Refresh BackgroundQuery:=False
xlApp.Sheets("mysecond").QueryTables(1).Refresh BackgroundQuery:=False
'xlApp.Application.ActiveWorkbook.PrintOut Copies:=1, Collate:=True

myDate = xlApp.Sheets("myfirst").Range("B12").Value
xlApp.Workbooks("JGBAnalytics.xls").Close True

xlApp.Quit
Set xlApp = Nothing
End Sub

 

[Heiko]

Hello Anant
try to use the method as a Function like

Sub Main()
Dim lngRet As Long
Dim xlApp As Object
Dim myDate As Date
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.DisplayAlerts = True
.Workbooks.Open ("D:\Mappe1.xls")
lngRet =
Sheets("myfirst").QueryTables(1).Refresh(BackgroundQuery:=False)
If Not lngRet Then
' Error - Handling
End If
lngRet =
Sheets("mysecond").QueryTables(1).Refresh(BackgroundQuery:=False)
If Not lngRet Then
' Error - Handling
End If
'.Application.ActiveWorkbook.PrintOut Copies:=1, Collate:=True
'Sheet has been changed so perhaps
'.Application.ActiveWorkbook.Close False
myDate = .Sheets("myfirst").Range("B12").Value
.Workbooks("JGBAnalytics.xls").Close True
.Quit
End With
Set xlApp = Nothing
End Sub

Heiko

Dear all,
I desperately need help. I have been working on this Excel automation
from VB. The task involves launching a set of Excel Addins, workbooks,
printing them and shutting everything down. All my workbooks shut down
with the usual quitting the excel object and setting it nothing,
except this one workbook. This workbook doesnt let excel shutdown
keeps lurking in my Task Manager. I traced the reason to one line of
code

xlApp.Sheets("myfirst").QueryTables(1).Refresh BackgroundQuery:=False

when i comment this out every thing shuts down. but i need the queires
refreshed before i print. I looked at what people had suggested for
this problem and specifically defined objects for workbook, worksheet,
querytable and once i was done with them set them to nothing. That
didnt work. im certain im missing something. Please please

regards,
Anant

<SNIP>