using EXCEL OLE from ACCESS.

[jackal63]

My boss wants to run queries from ACCESS (on information from FOXPRO through ODBC) and then have the information put through EXCEL so that it can be charted using EXCEL's graphics capabilities. I can make the charts in EXCEL, assuming I have the right information in EXCEL, but my boss wants this to be an automated, one-button kind of thing.

Does this sound feasible?
Does anybody out there have some good ideas?

Any help would be appreciated.

 

[B827]

Do you want the final result to be an Excel Workbook with charts? Sandy

 

[ETID]

Connect the excel worksheet to your access query using ODBC as well...

in the spread sheet...from menu, Data>Get External Data>New database query, choose MSAccess database* from the list, follow the wizard or select use MSQUERY option to return the data to your sheet....once returned, you can right click in the data area and select a propertie to refresh on open as well as other usefull options.

 

[KyleS]

Hi,
Yes this is very feasible. I am doing some of that here. We have an Excel worksheet that gets updated somewhere else and I send info into t "Temp File" - if you will - that I continually overwrite (I just output a query to excel and save over the original file). The Excel file that is updated has rows of formulas that link to my "Data Dump" file so all I have to do is export the query and my graphs update. As far as the report goes, I just created an OLE Linked object and placed it in my report. And on the OnOpen of the report I have code that outputs the query so my end of the graph is always up to date.

That's a quick outline of one way to do it. But have you tried using Access's graphing capabilities? I think it's a bit more cumbersome that Excel's but I haven't found anything Excel's can do that I couldn't get Access's graphing function to do, plus it save the whole FoxPro-Access-Excel-Access issue... I STRONGLY suggest you do everything you can to make this work before going the Excel route. It will save you many headaches... Kyle :

 

[jackal63]

To all. Thanks for the input. I'm not concerned with what the final working format of the file is (Excel worksheet, Access database), the only reason I was told to go with Excel was because it had inherant graphing abilites that were standard and didn't require extra 3rd party software to generate. All Access graphing solutions I saw came as extra 3rd party software. If there is a pack-in graphing ability in Access that maybe just hast to be installed off of the Office 2000 CD, then please let me know. Being able to do the reports directly from Access, for free, would help me out alot.

Any help is appreciated.
mike

 

[KyleS]

So if in your report design you goto "Insert" you don't have a "Chart" option towards the bottom? OK then go to Tools-->ActiveX Controls and find the Microsoft Chart Control, Version 5.0 (you may have a different version as I'm running '97) and try to register it. Kyle :

 

[ETID]

Again....

This is what you need.


Connect the excel worksheet to your access query using ODBC as well...

in the spread sheet...from menu, Data>Get External Data>New database query, choose MSAccess database* from the list, follow the wizard or select use MSQUERY option to return the data to your sheet....once returned, you can right click in the data area and select a propertie to refresh on open as well as other usefull options.


then build your charts and save ,...each time you open the xls the data will update (if you set that option) in the properties