VB In MS Excell
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.
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:
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?
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?
Participate on our website and join the conversation
This topic is archived. New comments cannot be posted and votes cannot be cast.
Responses to this topic
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:
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.
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.
