Export Org chart as image

[Steve G]

Hello,

I've created a macro which successfully exports to PNG or GIF an image of a
Pie Chart. I tried to use the same macro to export an Organizational Chart.
Routine does not seem to find it. Macro code is below.
The first macros are helpers ... again, they do work for a pie chart
The routine "Sub ExportCharts_PNG_Prompt()" is the macro.
Thanks
Steve G

'======================================================
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

'======================================================
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
'===============================
Private Function GetDirectory(Optional msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer
' Root folder = Desktop
bInfo.pidlRoot = 0&
' Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = msg
End If
' Type of directory to return
bInfo.ulFlags = &H1
' Display the dialog
x = SHBrowseForFolder(bInfo)
' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function
'========================================================


Sub ExportCharts_PNG_Prompt()
Dim MyPath As String
Dim ImagePath As String
Dim Filename As String
Dim i As Long
Dim Today
Dim DateSuffix As String
Dim ClientName As String

ImagePath = Evaluate(ActiveWorkbook.Names.Item("DefaultImagePath").Value)
DateSuffix = Format(Now, "YYYY-MM-DD")

If (ImagePath = "") Then
ImagePath = GetDirectory("Select Directory to Store Images")
ImagePath = InputBox("Enter File Path: ", "Get File Path", ImagePath)
End If

ClientName = InputBox("Enter Client ID (no spaces): ", "Get Client ID",
"ClientIDHere")


For i = 1 To ActiveSheet.ChartObjects.Count
Filename = InputBox("Enter Filename for Chart '" _
& ActiveSheet.ChartObjects(i).Name & "':", "Enter .PNG Image Name", _
ClientName & "_" & ActiveSheet.ChartObjects(i).Name & "_" & DateSuffix)
ActiveSheet.ChartObjects(i).Chart.Export _
ImagePath & "\" & Filename & ".PNG", "PNG"
Next
End Sub

 

[ShaneDevenshire]

Hi Steve,

Are we talking 2003 or 2007 or what? I've heard that not all of the new
graphic features of 2007 are supported in the VBA area (yet?).

And if you are using either version which Org chart are we talking about -
the one you can create using the Org Chart tool or the one you create using
the AutoShape tools.

 

[Steve G]

Shane et al,
Good question about 2003 vs 2007 so ...
I have now tried this with both 2003 (xls) and 2007 (xlsm) with the same
results.
I am talking about an Org Chart created with the Org Chart tool

Thanks for responding.

....S...

 

[Andy Pope]

Hi,

I think the org chart is one of the diagrams rather than a chart.
And the diagram object does not appear to have an Export method as
charts do.
What you can do is place a copy of the org chart in a chartobject and
then export it. This will give you a slight border to your image, a
couple of pixels, but will work.

Cheers
Andy

 

[Steve G]

Andy,
not sure how to go about copying the org chart into a "chart object" ...
tried creating a Microsoft Graph Chart object but I'm not sure how to create
an empty one to paste in the Org Chart.

I also tried converting the Org chart to a "SmartArt" type. That has no
useful effect that I can see on Export.

....S...

 

[ShaneDevenshire]

Hi Steve,

why not take a picture of the Org Chart in Excel and then maybe the picture
can be handled by VBA.

You can take picture in Excel with two basic approached -
A] 1. Highlight the range that includes the org chart
2. Hold down the Shift key and choose Edit, Copy Picture (2003)
3. Take your choice of formats and then Paste.
4. See if you can handle this object with VBA
B] 1. Add the Camera toolbar button to a toolbar - its under View, Toolbars,
Customize, Commands, Tools, Camera
2. Highlight the range as above
3. Click the Camera tool
4. Click another area of the spreadsheet
5. See if Excel can handle this object with VBA

 

[Andy Pope]

No need to go anywhere near MS Graph

Have a look at this,
http://www.andypope.info/vba/gex.htm

Cheers
Andy