Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with tables in Excel 2007.
I wish there were a way to directly maipulate the big colorful data tables
that Excel can now directly import from my database. =( The only way I've found to handle data tables is using the old ADODB connections... So I put some combo boxes on my sheet to select the records I want. This works minimally well because I can't just put objects into these boxes, I can only put strings in, and I can't read the index of the string selected, I have to take the string back, unfortuanetly the Find function on the ADODB dataset doesn't work so I had to manually implement a good old linear search. OK... not much time wasted... Now I have my data set selected and I need to do two things with it. First, I need to load it, here's the query: #### SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name, date, activity, assay_data_point.comments FROM assay_data_point, assay_data_set where assay_data_point.assay_data_set_id = assay_data_set.assay_data_set_id and date is not null and feed_line_id = 11 UNION SELECT assay_data_set.assay_data_set_id, assay_data_point_id, sample_name, date_recieved as date, activity, assay_data_point.comments FROM assay_data_point, assay_data_set where assay_data_point.assay_data_set_id = assay_data_set.assay_data_set_id and date is null and feed_line_id = 11 order by date #### Pretty, huh? That doesn't include the date range part of the select. ;) Well, here's where it works: It works in MySQL navigator, It works in the colorful data table displayer in Excel '07, it works in Microsoft's Query browser, even though I can't edit it there, it works when I print it out and sing it from my rooftop... But guess where it doesn't work? -- Yeah. There. I can't make it work in the ADODB connector, where I can give it parameters and such. There's some limitation with the Union statement that I can't figure out and doesn't seem to be documented anywhere. =( I'm not sure there's a way I can code around this. Ideally I'd be able to use the big colorful built in table interface. Which brings me to my second problem. There doesn't seem to be any way to programmatically access my workbook connections and, ideally, be able to set them up with my ADODB recordsets or, just as well, simply give them new query strings and tell them to refresh. =( For my next trick, I'll be grouping those by date and computing a bunch of statistics from them... |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with Lists (Now Tables) in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 Macro Help (Excel 2003 not working in 2007) | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Excel 2007 Pivot tables | Excel Worksheet Functions | |||
Excel 2007 Tables | Excel Discussion (Misc queries) |