Then how about taking Arvin's solution a few steps further??
Using things like TransferSpreadsheet from Access to a new Excel
workbook. Use Automation of Excel from Access to enter a new world of
power.
Create your data in Access and then export just the data you need to
Excel using ~TransferSpreadsheet.
Do this next part in Excel first then move the VBA over to the Access
side after things work perfectly in Excel. I find the Macro Recorder
in Excel provides great training wheels for us Access types.
(Actually, I've read posts from Excel types touting that as their
standard practice when starting something new. :-)).
Massage your data as much as necessary in Excel and then create a
chart sheet and label it. If you have the Recorder on for all of
those steps you'll be waaay ahead of the game. Name your macro. You
can clean out the superfluous code after you have a working system in
Excel.
When all is well on the Excel side, return to the Access side and look
in Help on "Automation". When last I looked there wasn't much there
but, surprisingly, enough to get you started
A general word to the wise: When starting the process it's a good
idea to use Early Binding in order to get the benefits of
Intellisense. When you're pretty pleased with your code, change to
late binding.
Once you feel you're ready, move a copy of the code in the Excel macro
you recorded, tweaked and named and tweaked some more over to the
Access side. If you want the code to remain intact then it may be a
good idea to move it into a standard module in Access with a creative
new name like basExcelCode. If you want to save it in line with
other code already in Access then be sure not to copy the Begin Sub
and End Sub lines. Make sure to save a copy of the Excel workbook out
of harm's way or be sure that you create a new workbook with a
different name.
By now the idea may have crossed your mind that the whole thing may
just not be worth it. Believe me it is worth it. The fact that you
need to do something the first time is a pretty good indicator that
you'll need to do it again in the future. From start to finish it may
take you two or three days. You'll be researching and learning as you
go. That takes time. What you learn will be useful in using any of
the MS Office platforms together in true client / server
relationships.
If you'll open the Newsgroups window in your newsreader you'll find a
couple of groups on "Automation"
Post back here or in those groups or in all three using crossposting.
HTH
--
-Larry-
--
"Samuel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The chart must be completely automated
>
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > "Samuel" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >>I need to create a chart that presents the trend of the foreign
exchange
> >>rate
> >>
> >> Where do I begin?
> >
> > I would export the data that you wish to use to an Excel range.
Use the
> > range as a source for an Excel chart. At least until Office 2007,
Excel
> > had a better implementation of the chart engine used by both
programs.
> > --
> > Arvin Meyer, MCP, MVP
> > http://www.datastrat.com
> > http://www.mvps.org/access
> > http://www.accessmvp.com
> >
>
>
Similar Threads
