Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False)
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(Backgroun dQuery:=False) If Not lngRet Then ' Error - Handling End If lngRet = Sheets("mysecond").QueryTables(1).Refresh(Backgrou ndQuery:=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 :-) (Anant) wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I 'Enable Automatic Refresh' for Query Refresh by default | Setting up and Configuration of Excel | |||
save & close after web query refresh | Excel Discussion (Misc queries) | |||
Problems with .Refresh BackgroundQuery:=False | Excel Worksheet Functions | |||
Query Refresh-Enable Automatic Refresh Dialogue Box | Excel Discussion (Misc queries) | |||
Web Query fail to Refresh All but individual refresh is ok | Excel Programming |