Export Chart With PopUp Window

[geospatial]

I currently have a macro that will export all the charts i have on a worksheet. At this time it is only one worksheet at a time.

The code I have is

Sub create_jpeg()

Application.Screenupdating = False
Dim mychart As Chart
Set my chart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:="T:\CEHC\Intel\mychart.jpg", Filtername:="JPG"
End Sub

Is there a way to bring a popup window to allow the user to select save location and filename?

 

[Sal Paradise]

Using the power of Google:

Public 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

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

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

Here let us use the above shell functions to open the browse directory dialog

Sub Show_BrowseDirectory_Dialog()

' BrowseForFolder
' SHBrowseForFolder API Function Example

Dim dirInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer
Dim mychart As Chart

Set my chart = ActiveSheet.ChartObjects(1).Chart

' Set Default Root folder = Desktop
dirInfo.pidlRoot = 0&

dirInfo.lpszTitle = "Browse directory!"

' Type of directory
dirInfo.ulFlags = &H1

' Show the Browse Dialog
x = SHBrowseForFolder(dirInfo)

' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
mychart.Export Filename:=Left(path, pos - 1) & InputBox("Please select a filename","Save As..."), Filtername:="JPG"
Else
MsgBox "Browse a Directory..."
Show_BrowseDirectory_Dialog
End If


End Sub

Code taken from here.

 

[geospatial]

I get an Invalid Qualifier error on dirInfo

 

[geospatial]

Nevermind. Typo in earlier text. Looking for a possible other typo now. Will give praise when i get working. Thanks

 

[geospatial]

I am now getting a run time error '1004'

Method 'Export' of object'_Chart' failed on the line

mychart.Export Filename:=Left(path, pos - 1) & InputBox("Please select a filename","Save As..."), Filtername:="JPG"

Any ideas?

 

[Sal Paradise]

Try debugging it.

First try Left(path, post - 1) & "filename.jpg"

See if that works. If so, the inputbox is wrong.

If not, then the Left(path, post - 1) may be wrong. Echo it to the debugger to make sure it is a proper path.