On Jan 21, 12:06 pm, Dale Fye wrote:
I've got an Access application that tracks multiple projects, and milestones
along those project timelines. My goal is to create a procedure in my Access
application that will use automation to open Excel, and create a visual
representation of the various projects and their milestones.
My client is playing with the idea of using Project to do this, but
apparantly, Project 2007 does not read Access 2007 data.
I've used Access to populate Excel fields in the past, so I'm somewhat
familiar with the Excel object model, but I have never tried to do anything
like create shapes. What I need to be able to do via code is:
1. Along the top, label the first row with the months
2. Label the second row with individual days, and size those cells
appropriately (I know how to do these)
3. On each successive row.
a. Enter some text in the first cell of the row (no problem)
b. Draw a line (of a certain width and color) from a start date through
an end date associated with the cells in row 2. Don't know how to do this.
How do you identify that a line starts at a certain vertical and horizontal
position in a cell and ends at in another cell on the same "row" of the
spreadsheet.
c. Draw a rectangle, that overlaps this line between two dates (don't
know how to do this, but assume it is similar to b above).
d. Draw a textbox immediately below the rectangle from step c with a
label in it.
Need to make sure that all of these shapes (line, rectangle, textbox) are
associated with the row so that if someon hides the row, all of the shapes
are hidden as well.
I know I can get some of this by recording a macro, but would appreciate any
comments regarding this process.
Dale
Since you mention creating shapes in Excel, I'll bring up the
following for discussion:
I was looking at trying to get some of the same functionality that I
get from creating PDF's from Access into an Access Report.
Specifically, I was looking at:
http://www.microsoft.com/interop/doc...ryFormats.mspx
and found the following gem:
Office Drawing 97-2007 Binary Format Specification
http://download.microsoft.com/downlo...cification.pdf
According to:
File formats supported in the 2007 Office system
http://technet.microsoft.com/en-us/l....aspx#section4
"Microsoft Office Clipboard file formats
You can paste data from the Microsoft Office Clipboard into Word,
Excel, PowerPoint, or Access by using the Paste or Paste Special
command (Home tab, Clipboard group, Paste button) if the Office
Clipboard data is in one of the formats shown in the following table.
....
Office drawing object .emf Office drawing object format or Picture
(Windows enhanced metafile format, EMF)."
But also:
Microsoft Security Bulletin MS05-053
Vulnerabilities in Graphics Rendering Engine Could Allow Code
Execution (896424)
http://www.microsoft.com/technet/sec.../ms05-053.mspx
In short, Microsoft seems not to be placing any emphasis on their
Enhanced Metafile Format for whatever reason(s). It seems like it
might be useful in some limited situations that I am only now
encountering. Does anyone else have some insights into the EMF
clipboard format or into why Microsoft seems to be backing away from
it? I'm not sure if an Office Drawing Object can be placed within an
Excel cell or not. BTW, doesn't Microsoft Project have an Object
Model that can be utilized by Access? Personally, I find PDF output
from Access to be much more powerful and flexible with complex
graphical output than Excel, in general, but hiding a row or column
does not strike me as something that PDF files are particularly good
at doing. I think much of what you are trying to do can be
accomplished in Excel, but like in the Excel flexible Pivot example
video I viewed recently, you will have to use a very clumsy work-
around or two to pull it off.
For an example of creating a PDF from Access 97, see:
https://files.oakland.edu/users/fort...arAnnotate.zip
Each date corresponds to the location of a specific rectangle on the
calendar, so it would be possible to, say, put a colored line of a
given thickness across several dates. Off the top of my head I can't
see a simple way to deal with multiple lines of different colors
across the top in a way that would be aesthetically pleasing, but I
could possibly come up with something after thinking about it for a
time.
James A. Fortune