M
Maury Markowitz
I was wondering if someone can help me get up and running with
QueryTables under VBA. The dox on the web are fairly lightweight.
Normally I do my DB access using VBA code and ADODB. I find that
processing the result set in VBA gives me capabilities that I lack
using things like MSQuery, especially things like control over the
timing of the queries and such.
But on this current system, ADODB simply won't run, it complains about
the connection string no matter what I do. In desperation I came
across some code using QueryTables to build a SQL query in VBA, and
when I tried it, presto, no problem at all. The reason one works and
the other doesn't will remain a mystery it seems.
But that's about as far as that document went, and there doesn't seem
to be a lot more out there. Experimenting further I found that every
time I use the .Add method to run the query, I would get another
QueryTable that seemed to me magically saved into the sheet without my
intervention.
So, can someone point me, or help me, get a handle on the QueryTable
object? My basic goal is to use them in an ADODB fashion - that is,
run some code that produces the output, waits while this happens, and
then processes it in code to produce the exact results I need.
QueryTables under VBA. The dox on the web are fairly lightweight.
Normally I do my DB access using VBA code and ADODB. I find that
processing the result set in VBA gives me capabilities that I lack
using things like MSQuery, especially things like control over the
timing of the queries and such.
But on this current system, ADODB simply won't run, it complains about
the connection string no matter what I do. In desperation I came
across some code using QueryTables to build a SQL query in VBA, and
when I tried it, presto, no problem at all. The reason one works and
the other doesn't will remain a mystery it seems.
But that's about as far as that document went, and there doesn't seem
to be a lot more out there. Experimenting further I found that every
time I use the .Add method to run the query, I would get another
QueryTable that seemed to me magically saved into the sheet without my
intervention.
So, can someone point me, or help me, get a handle on the QueryTable
object? My basic goal is to use them in an ADODB fashion - that is,
run some code that produces the output, waits while this happens, and
then processes it in code to produce the exact results I need.