VB In MS Excell

[ViolentGreen 0]

I have this huge, long, nasty query embedded into an excell maco that pulls in data from ODBC connection. After the data arives, I want to make some modifications to the data.

 

I am trying to run the function to modify the data right after the connection is added with

 

Code:

With ActiveSheet.QueryTables.Add(Connection......End WithFunction()

 

The problem I am runnign into is that it tries to run the function before the data reaches Excell. Is there a way that I can tell the function to wait until the data transfer is complete to run?

[ViolentGreen 0]

Quote:

Quote:

I have this huge, long, nasty query embedded into an excell maco that pulls in data from ODBC connection. After the data arives, I want to make some modifications to the data.

I am trying to run the function to modify the data right after the connection is added with

Code:

With ActiveSheet.QueryTables.Add(Connection......End WithFunction()

The problem I am runnign into is that it tries to run the function before the data reaches Excell. Is there a way that I can tell the function to wait until the data transfer is complete to run?

Possibly.... if you are directly loading this into a sheet in excel, check to see if a certain cell's contents are not still "" (or NULL).

That's your "start execution" flag... & if Excel's VBA sheet level object has a timer on it, there is your ticket... have it keep scanning for that empty cell content, & when it's NO LONGER EMPTY, run your macro from that timer.

Another way is to load the sheet to a file instead of directly into excel, & check its size (or even existence) with the same timer method.

My advice is lookup timers in Excel's VBA help... I had to do that in Access a ton of times, it exists for the form level object there & can be utilized in this manner.

If it does not exist for sheet or worksheet level objects in Excel, try an ActiveX control ->

http://activex.microsoft.com/controls/iexplorer/x86/ietimer.cab

APK

Wow.... Talk about flashback... I eventually got around it somehow, though I dont' remember how. It seems like there was an option in the Add member that would specify whether or not to run the query in the background. I can't remember if that did it or not. I much prefer controling the threads myself. Not a big fan of the VB here... The syntax is too lenient and unstructured for me. Not as much as AppleScript. I don't see how anyone can use it.