I’ve written a more detailed version that exports to the parent workbook’s directory, and allows the user to enter a file name, from which the routine determines the image file type. It also checks for an existing file with that name.
Sub ExportChart()
Dim sChartName As String
Dim sPrompt As String
Dim sDefault As String
If ActiveSheet Is Nothing Then GoTo ExitSub
If ActiveChart Is Nothing Then GoTo ExitSub
sPrompt = "Chart will be exported into directory of active workbook." & vbNewLine & vbNewLine
sPrompt = sPrompt & "Enter a file name for the chart, including an image file extension (e.g., .png, .gif)."
sDefault = ""
Do
sChartName = Application.InputBox(sPrompt, "Export Chart", sDefault, , , , , 2)
If Len(sChartName) = 0 Then GoTo ExitSub
If sChartName = "False" Then GoTo ExitSub
Select Case True
Case UCase$(Right(sChartName, 4)) = ".PNG"
Case UCase$(Right(sChartName, 4)) = ".GIF"
Case UCase$(Right(sChartName, 4)) = ".JPG"
Case UCase$(Right(sChartName, 4)) = ".JPE"
Case UCase$(Right(sChartName, 5)) = ".JPEG"
Case Else
sChartName = sChartName & ".png"
End Select
If Not FileExists(ActiveWorkbook.Path & "\" & sChartName) Then Exit Do
sPrompt = "A file named '" & sChartName & "' already exists." & vbNewLine & vbNewLine
sPrompt = sPrompt & "Select a unique file name, including an image file extension (e.g., .png, .gif)."
sDefault = sChartName
Loop
ActiveChart.Export ActiveWorkbook.Path & "\" & sChartName
ExitSub:
End Sub
A more advanced version is under development for a chart utility; it features a file-save dialog that lets the user browse to any directory, and also allows the user to overwrite an existing file.
Posted by
Jon Peltier on 03/24 at 05:06 AM
