Visually represent project dates and activities as shapes in Excel

[Dale Fye]

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

 

[James A. Fortune]

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/docs/OfficeBinaryFormats.mspx

and found the following gem:

Office Drawing 97-2007 Binary Format Specification

http://download.microsoft.com/downl...ceDrawing97-2007BinaryFormatSpecification.pdf

According to:

File formats supported in the 2007 Office system

http://technet.microsoft.com/en-us/library/dd797428.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/security/bulletin/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/fortune/web/CalendarAnnotate.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
(e-mail address removed)

 

[Oliver Bock]

Enhanced metafiles are just a serialisation of Win32 GDI commands. That
is, you can record a bunch of lines, circles, etc. I think Microsoft
does not emphasise them much because they won't do a good job with GDI+
rendering and probably won't work at all with WPF. Thus it is a legacy
format, one which was largely used by Microsoft for OLE2, which is
itself in decline and being deemphasised, because nobody could ever get
it to work well.

 

[James A. Fortune]

Enhanced metafiles are just a serialisation of Win32 GDI commands. That
is, you can record a bunch of lines, circles, etc. I think Microsoft
does not emphasise them much because they won't do a good job with GDI+
rendering and probably won't work at all with WPF. Thus it is a legacy
format, one which was largely used by Microsoft for OLE2, which is
itself in decline and being deemphasised, because nobody could ever get
it to work well.

Thanks, Oliver. Your explanation makes sense.

James A. Fortune
(e-mail address removed)

 

[Dale Fye]

Actually, this turns out to be relatively simple; the key is knowing what
cells you want to draw the lines or boxes between, which is trivial really.

1. Each cell has .Left, .Top, .Width and .Height properties, so to
determine the start and end points of the line/box you use something like:

Set sht = xlApp.ActiveWorksheet
lngLineLeft = sht.Cells(intRow, intStartCol).Left
lngLineRight = sht.Cells(intRow, intEndCol).Left + sht.Cells(intRow,
intEndCol).Width

Same sort of thing for getting the top and bottom position of the line or
box you want to draw

2. Then you use the one of the Shapes.AddXXXX methods to add the shape to
that position on the spreadsheet. Below, I've listed the code I used to add
a line, a box. Additionally, since I wanted a mouseover or control tip text
type capability, I also added hyperlinks (that go nowhere)

With sht.Shapes.AddLine(lngLineLeft, lngLineCenter, lngLineRight,
lngLineCenter).Line
.Weight = 3
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(128, 128, 128)
End With

If IsNull(TipText) = False Then
sht.Hyperlinks.Add sht.Shapes(sht.Shapes.Count), "", "", TipText
End If

Set shpBox = sht.Shapes.AddShape(msoShapeRound1Rectangle, lngBoxLeft,
lngBoxTop, lngBoxRight - lngBoxLeft, 15)
With shpBox
.Fill.BackColor.RGB = RGB(121, 121, 113)
.Fill.Transparency = 0.5

sht.Hyperlinks.Add shpBox, "", "", "Place your hyperlink text here"

End With

3. Lastly, I wanted to be able to associate a label with each of the boxes,
so I used the code below:

Set shpLabel = sht.Shapes.AddTextbox(msoTextOrientationHorizontal,
lngBoxLeft, lngBoxTop + 9, lngBoxRight - lngBoxLeft, 15)
With shpLabel
.TextEffect.Text = IIf(Milestone = "Event dates", "Main event",
Milestone)
.TextFrame.AutoSize = True
.TextEffect.FontBold = msoTrue
.TextEffect.FontSize = 10
End With

Hope this helps the next guy that wants to try something like this.

Dale