Ms Access and Ms Excel VBA - Dynamic Charts

Hello everybody,

I have a strange problem. I must generate mutliple charts dynamically from a Ms Access database. The solution we wanted to implement is that we export all the Data in Excel, and from then we generate the charts. It's working fine when the charts are define statically. But when it's time to do it dynamically, it doesn't work.

I'll give you an example :

Please Login or Register  to view this content.

That code is working well. But, when I'm adapting my solution in a function like :

Please Login or Register  to view this content.

and the following code who calls the Procedure

Please Login or Register  to view this content.

The problems is that I don't see the series at all in the chart and there is no error message. It's like it sees nothing. Do you have any idea, because it's working well with the static code ?

Thank you for your help !

Daniel

Daniel

Where do you have the second code?

If it's in Access then Excel constants like xlLine won't be recognised unless you have a reference to the appropriate Excel library.

PS Where's the rest of the code? For example the part that opens Excel.

Have you stepped through the code to see what's happening?

Also, do you have a reference to the Excel object library?

Dagagnon,

Welcome to the forum.

I have added code tags to your 1st post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

Hi Norie,

I'm working with confidential data, so I can't send you the whole project, but I'll give you the most information possible.

Please Login or Register  to view this content.

Edited the code for all the 5 other static charts but they are all working well.

Please Login or Register  to view this content.

Does the code work if you don't use a separate sub?

I've just noticed something - you are adding the new series after everything else here.

Please Login or Register  to view this content.

Yeah, I know, but the Charts.Add method create a new Collection by default. But the code fails even before that. It gives me a 1004 - Invalid paramter S

which line gives the error?

what is the actual (not assumed!) value of parametre at that point and what is in the cell to which it refers?

and what is in the cell?

in your earlier code you had

Please Login or Register  to view this content.

which should be Excel.Application