E
E Harris
I'm a newbie when it comes to Excel VBA coding, but am quite familiar
with pure VB.
I've been asked to do something that I think I could tackle easily
with a stand-alone VB app, but they want this to be an Excel app,
mainly for Excel's chart capabilites.
Here's what I have:
I've created a query table in a worksheet that, when refreshed, hits
an Access database, running a query and displaying the results on this
Excel worksheet. The fields are Location, Result, Parameter, and
Date.
I've created a small user interface on another worksheet that prompts
the user for values for two fields: Location and Parameter.
What I need to do now is to create a simple graph that represents the
particular Location/Paramter pair the user selects. The Y-axis will
represent time (Date field) and the Y-axis, Result.
What is the best way to go about doing two things:
--1-- Filtering the data
As I said, I have the results of a query that gives me ALL the
possible data the user can search. After getting the values of the
fields the user is looking for, if I were in Access, I would simply
write an SQL SELECT statement, ending with "WHERE Location = (the
value the user entered in textbox 1) AND Parameter = (the value the
user entered in textbox 2)".
Is there a way within Excel, to treat a worksheet as a table and run
SQL queries upon it? I suppose the results would need to be outputted
to a new worksheet.
Or, should I somehow create the query table on the fly, so that, by
using VBA, I only return the data from the Access database that the
user wants? (As opposed to having already created the query table at
design time, that has ALL the data, most of which the user will not
need for his or her particular search).
In reagards to the above, in my research I came across the SQL.REQUEST
function and it seemed that it was used to do this.
With my limited knowledge of Excel, my convoluted solution to the
problem would be to have a loop in VBA that uses Find, Select, Copy,
and Paste to manually get the records the users wants, from the query
table I made at design time. A sort of do-the-SQL-yourself approach,
but I am sure there is an easier way.
--2-- Making the chart
So however I get it, by this point I should have a worksheet that
contains only records with values in Parameter and Location that match
what the user wants to plot.
Using code, how do I make this a chart to plot this?
I know this is a very general question and probably very easy. If
anyone can just point me to a document or web page that steps a newbie
like me though this, that's really all I need. Again, I can make a
chart at design time with the wizard, no problem. I just need to know
how to make the chart in code, when you don't know beforehand how many
rows the spreadsheet will be that serves as data for the chart.
Point --1-- is my biggest obstacle now.
Thanks in advance to anyone kind enough to respond!!
with pure VB.
I've been asked to do something that I think I could tackle easily
with a stand-alone VB app, but they want this to be an Excel app,
mainly for Excel's chart capabilites.
Here's what I have:
I've created a query table in a worksheet that, when refreshed, hits
an Access database, running a query and displaying the results on this
Excel worksheet. The fields are Location, Result, Parameter, and
Date.
I've created a small user interface on another worksheet that prompts
the user for values for two fields: Location and Parameter.
What I need to do now is to create a simple graph that represents the
particular Location/Paramter pair the user selects. The Y-axis will
represent time (Date field) and the Y-axis, Result.
What is the best way to go about doing two things:
--1-- Filtering the data
As I said, I have the results of a query that gives me ALL the
possible data the user can search. After getting the values of the
fields the user is looking for, if I were in Access, I would simply
write an SQL SELECT statement, ending with "WHERE Location = (the
value the user entered in textbox 1) AND Parameter = (the value the
user entered in textbox 2)".
Is there a way within Excel, to treat a worksheet as a table and run
SQL queries upon it? I suppose the results would need to be outputted
to a new worksheet.
Or, should I somehow create the query table on the fly, so that, by
using VBA, I only return the data from the Access database that the
user wants? (As opposed to having already created the query table at
design time, that has ALL the data, most of which the user will not
need for his or her particular search).
In reagards to the above, in my research I came across the SQL.REQUEST
function and it seemed that it was used to do this.
With my limited knowledge of Excel, my convoluted solution to the
problem would be to have a loop in VBA that uses Find, Select, Copy,
and Paste to manually get the records the users wants, from the query
table I made at design time. A sort of do-the-SQL-yourself approach,
but I am sure there is an easier way.
--2-- Making the chart
So however I get it, by this point I should have a worksheet that
contains only records with values in Parameter and Location that match
what the user wants to plot.
Using code, how do I make this a chart to plot this?
I know this is a very general question and probably very easy. If
anyone can just point me to a document or web page that steps a newbie
like me though this, that's really all I need. Again, I can make a
chart at design time with the wizard, no problem. I just need to know
how to make the chart in code, when you don't know beforehand how many
rows the spreadsheet will be that serves as data for the chart.
Point --1-- is my biggest obstacle now.
Thanks in advance to anyone kind enough to respond!!