Excell Chart as linked OLE Object as a field in a record in a table

[mikecollett]

Being self taught, I may be going about this the wrong way.
To show a graph on a report that is specific to a record, i query the database to get my record set, copy and paste to excell, insert a chart as a worksheet in the excell file and then insert the file into the acess table field as a linked OLE object. (original data could come from one of 5 different access databases).
It works but...
With hundreds of graphs it takes a day to update them all. I cant seem to figure out how to export to excell from acess or import to excell from access the info without our firewall (I think) stopping macros actions that happen on opening a file or the file with the graph in it being written over or smething else going wrong.
Am I on the right track?
Mike

 

[sdk]

Hi,

I have tried similar 'schemes', but alas without luck. I could get Access to create the excel workbook object & data tables & graph (and save as an excel file), but could not use it in a form. It was all to overcome the limited graphs available within access. I did find

http://www.mvps.org/access/

useful (as most users here have!) as well as the developer handbook by Getz et al, but will be watching with interest to see if you have luck with your post.

Something that I didn't see it the above resources was the need to ensure that the office object were turned on in the Access modules' references. Will warn you, this is unlikely to be readily acievable with macros - you can send objects (queries) to external files, but probably can't do much more (happy to be proven wrong!)

Best of luck

Steve

 

[mikecollett]

Cheers Steve!
You appear to have don the thing that I cannot - transfering the data to excell.
Once the data is there I seem to have no trouble with getting the graph back into access, its automatically updating a stack of excell files that is stumping me.
I checked out your suggested link but Im afraid the code confuses the heck out of me (I cant seem to find anyone to tell me how it works - the help files 1 - dont work on my computer at the moment and 2 - are too dry and send me to sleep. I am trying to get the company to get me some training but they are dragging their heels - I reckon I only need about 1 hour with someone who can answer my questions with a yes or no with no anecdotal bs and I will be fine. anyway, enough moaning).

How do you mean you could not use the file in a form?

cheers again
Mike

 

[sdk]

Hi,

I was trying to use the Graph object as an OLE object in an unbound control on a form. The intention was to create an interface in Access to create monthly reports, without needing to migrate data to excel each month and drawing from many data sources.

To do that I was creating a graph object totally with code, and trying to insert that on the form's OLE field. It was all to overcome the 'limited' grpah capabilities of Access. I was able to create the dataset, stick it in an excel workbook object, create the graph & format, (all in code) but could not put it in the form.

So I gave up.

If you were looking just to move data to excel, then the macro 'SendObject' or the VBA DoCmd.SendObject are fairly straightforward.

 

[mikecollett]

trust me not to be clear.
i want to export the data from the query to an existing worksheet within an existing workbook starting at a particular cell (a1 as it happens!). the number of columns will not change but the number of rows will as the year progresses.
i have played around with sendobject but that appears to over write the file or worksheet, not copy the data to an existing spot on an existing worksheet in an existing file.
there ia a bit of code that has cropped up copying a recordset to a specified range but the syntax has me reeling as it dont understand all of the "dim","call","set", "x as x", with/wend, private/public blah blah blah in enough detail as not one here at wotk cam teach me.
add that the help files on my computer wont work and i am getting very stressed! btw i am using access 2000.

as for your writing graphs in code, i didnt know you could do that so at that point i become useless to you with your little buggy thing.

cheers
mike

 

[mikecollett]

I have learned a bit and have redirected my question to another thread "exporting a query to a specified range in excell".

Cheers for looking!

Mike C