Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a query and a chart on the fly
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a chart | New Users to Excel | |||
making a chart | Excel Discussion (Misc queries) | |||
Making a chart | Excel Discussion (Misc queries) | |||
Making a chart | Charts and Charting in Excel | |||
Chart making | Charts and Charting in Excel |