Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default Export Org chart as image

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("DefaultImagePa th").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

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,344
Default Export Org chart as image

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("DefaultImagePa th").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

  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default Export Org chart as image

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("DefaultImagePa th").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

  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Export Org chart as image

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("DefaultImagePa th").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
  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default Export Org chart as image

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("DefaultImagePa th").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



  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,344
Default Export Org chart as image

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
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"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("DefaultImagePa th").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

  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Export Org chart as image

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Image Export - Org Chart Steve G[_2_] Excel Worksheet Functions 1 May 22nd 08 08:48 PM
Chart.Export images are shrinking as I export more images Jared Charts and Charting in Excel 3 January 29th 08 03:23 AM
Export Pie Graph Chart as Image [email protected] Excel Discussion (Misc queries) 1 November 25th 06 06:57 PM
Displaying Chart Data ontop of Image Tinkicka Charts and Charting in Excel 1 June 26th 06 02:46 AM
Combine values into 1 chart image BRPtacek Charts and Charting in Excel 2 September 8th 05 03:33 PM


All times are GMT +1. The time now is 08:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"