Problem Retrieving data after query table refreshes

Amir · External Since: Aug 05, 2004 Posts: 17

Hi,

I'm using VBA code to open a saved Excel file from Word. This Excel file is
linked to a Database using Oracle ODBC Driver Connect for Rdb (The
connection to the database was setuped prior to all the process. I use the
saved Excel file to 'save' that connection so that I won't have to define it
each time).

After I open the Excel file, I use VBA (code still running in Word) in order
to change the:
XLApp.QueryTables(1).CommandText
according to the query I wish to run, Then I use:
XLApp.QueryTables(1).Refresh BackgroundQuery:=False
to retrieve the data from the Database.

What I wish to do is that after data is refreshed, the fresh data will be
put into variables so that I can use these variables later in my original
Word VBA code.

Problem is that despite I use BackgrounQuery:=False, the code continues to
'run', therefore putting empty values into my variables, or it puts the
previous values (the values that where supposed to be the last time I've run
the query).

The connection to the database is defined correctly, so that if I do that
manually, I get the results, but since I use VBA code, the variables get the
data from the worksheet before it's refreshed according to the new
commandtext.

Problem consists even if I manually disable the Data range property 'Enable
background refresh', then saves the Excel file, then run the VBA code.

I havn't tried using AfterRefresh because I don't know how to use that
event. In addition I don't know if this should solve my problem and there is
also the problem that this event should run 'in Excel' so how will I pass
the data back to the Word VBA code? I'm not sure at all that this is the
solution..

Query can sometimes (according to commandtext) return no records so that I
can't know for sure that some of the cells will contain data after refresh
succeeds, since it won't always have data in it. Therefore checking if cells
are empty or not to get indication of refresh end seems to be useless.

I thought that BackgroundQuery:=False should take care of these cases but it
seems not to do that.
I'm using Office 2000 to run that code.

Is there anyway I can solve that?
Is there anyway to 'Catch' the 'Real' moment when query has finished
refreshing?

Your help is needed.

Kind Regards,
Amir.

Bill Manville · External Since: Jul 30, 2004 Posts: 536

Good question

I would try testing the Refreshing property of the querytable until it
went False.

Failing that I would try changing something in the first row of the
querytable (even a field name), before the refresh, to something that
would never be returned, and wait for it to be changed.

Let us know how you solve the problem, for the benefit of others (and
me!)

Bill Manville
MVP - Microsoft Excel, Oxford, England

Amir · External Since: Aug 05, 2004 Posts: 17

Hi,

I will try that (unable to test this at home since I don't have access to
the DB).

How would you suggest to to 'wait' until it's changed?

If I go through a loop like the following one, Excel will be too busy
running the loop instead of updating the data from the database:

With Worksheets(1).QueryTables(1)
.Refresh BackgroundQuery:=True
Do While (Worksheets(1).Range("A1") = "CheckString")
'Wait
Loop
End With

Maybe I should replace the line:
'Wait
with the following line:
Application.Wait(Now + TimeValue("0:00:02"))
?

I've thought about another solution: saving the Excel file:
Dim wkbkExcelWorkbook

Application.DisplayAlerts = False

For Each wkbkExcelWorkbook In XLApp.Workbooks

wkbkExcelWorkbook.Save

Next wkbkExcelWorkbook

Application.DisplayAlerts = True

Application.Quit

then open the file again and retrieve the data (in order for that to work I
think that I should make sure that 'Remove external data from worksheet
before saving' is NOT checked).

What do you think?

I will be happy if you could write your opinion about that, because I won't
be able to see this forum from monday until two weeks from now, and I have
to solve that one..

Thanks a lot!!
Regards,
Amir.

"Bill Manville" <Bill-Manville.TakeThisOut@msn.com> wrote in message
news:VA.00001356.0e0a0c6f@msn.com...
> Good question
>
> I would try testing the Refreshing property of the querytable until it
> went False.
>
> Failing that I would try changing something in the first row of the
> querytable (even a field name), before the refresh, to something that
> would never be returned, and wait for it to be changed.
>
> Let us know how you solve the problem, for the benefit of others (and
> me!)
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>

Bill Manville · External Since: Jul 30, 2004 Posts: 536

I think you will be doing the waiting in Word, not in Excel

Declare Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds as Long)

With xlApp.Worksheets(1).QueryTables(1)
.Refresh BackgroundQuery:=True
Do While (xlApp.Worksheets(1).Range("A1") = "CheckString")
Sleep 1000 ' wait a sec
Loop
End With

Bill Manville
MVP - Microsoft Excel, Oxford, England

Amir · External Since: Aug 05, 2004 Posts: 17

Hi,

I've tried that again and the problem was solved when disabled the property
'Enable background refresh'. I don't know why I've thought it didn't work
last time i've tried. Any way it didn't work when I only used :
XLApp.QueryTables(1).Refresh BackgroundQuery:=False
unless the 'Enable background refresh' property was unchecked.
Thanks a lot for your help!

Kind Regards,
Amir.

"Bill Manville" <Bill-Manville DeleteThis @msn.com> wrote in message
news:VA.00001358.118851e2@msn.com...
>I think you will be doing the waiting in Word, not in Excel
>
> Declare Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds as Long)
>
> With xlApp.Worksheets(1).QueryTables(1)
> .Refresh BackgroundQuery:=True
> Do While (xlApp.Worksheets(1).Range("A1") = "CheckString")
> Sleep 1000 ' wait a sec
> Loop
> End With
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>

alderran · Joined: Aug 10, 2008 Posts: 1

Did anyone figure out what is going on here? I created an add-in that allows users to embed calls to a database for financial information. It uses querytables in the workbook. I have a user who ends up from time to time with a workbook that will no longer connect to the database.

It's only the one person. No one else reports having this problem. So I'm guessing it is either the users computer or the workbook itself. She is the only one using that particular workbook. I checked to see if she might be deleting the querytables, but she claims that she refreshes the sheet and then the links are gone. When this problem comes up we just get the file restored. So it's always the same sheet.

I am unable to recreate the problem on my computer. The end user has Excel 2003 and I have Excel 2007. I'm currently in the process of installing MS Virtual PC so that I can load a copy of Office 2003 on Windows XP, but not really that hopeful because most of the users have this same setup and this is the only problem child.

Any help would be greatly appreciated.