S
Samuel
I need to create a chart that presents the trend of the foreign exchange
rate
Where do I begin?
Thank you,
Samuel
rate
Where do I begin?
Thank you,
Samuel
A
Arvin Meyer [MVP]
Samuel said:
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.
S
Samuel
The chart must be completely automated
Arvin Meyer said:
L
Larry Daugherty
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
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
A
Arvin Meyer [MVP]
It can be completely automated very easily. As a mater of fact, I have 1
application which requires creating multiple, quick changing graphs for
audiology POs and testing on forms. Rather than doing the graphs in Access,
I do them in Excel and save a gif or jpg which is returned to Access. The
process takes well under a second to create, but once the graph is imaged,
the POs with up to 2 images are created as Access reports, saved as PDFs,
and emailed in under 3 seconds, all in code, and completely automated.
I've published code here before to do that. Use Google Groups to Google on
my name and Excel and you should be able to find it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
application which requires creating multiple, quick changing graphs for
audiology POs and testing on forms. Rather than doing the graphs in Access,
I do them in Excel and save a gif or jpg which is returned to Access. The
process takes well under a second to create, but once the graph is imaged,
the POs with up to 2 images are created as Access reports, saved as PDFs,
and emailed in under 3 seconds, all in code, and completely automated.
I've published code here before to do that. Use Google Groups to Google on
my name and Excel and you should be able to find it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com