No need to go anywhere near MS Graph ;)

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

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Steve G" wrote in message
...
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...

"Andy Pope" wrote:

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 wrote:
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...

"ShaneDevenshire" wrote:


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.
--
Cheers,
Shane Devenshire


"Steve G" wrote:


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("DefaultImag ePath").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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info