ExportAsFixedFormat now failing

Hello,

I'm wrote the following code to export a chart to pdf using automation from within Access 2007.  It stopped working today and I'm now getting an runtime 1004 error on objXlWs.ExportAsFixedFormat.  I'm at a loss as to why it is now failing.
Private Sub SaveToPdf(objXlWs As Object)
    ' Comments:
    ' Params  : objXlWs
    ' Modified:
    
    On Error GoTo PROC_ERR
    
    Dim strMyFilename As String
    Dim objMyShell As Object
    
    strMyFilename = Application.FileDialog(msoFileDialogSaveAs)
    
    objXlWs.ChartObjects(1).Activate
    objXlWs.ExportAsFixedFormat Type:=0, FileName:=strMyFilename, Quality:=0, OpenAfterPublish:=True
    
    Set objMyShell = CreateObject("WScript.Shell")
    objMyShell.Run strMyFilename & ".pdf"
    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, Me.Name & ".SaveToPdf"
    Resume PROC_EXIT
    
End Sub

Open in new window

Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan VelasquezAuthor Commented:
I did some experimenting and found that when I tried saving the excel sheet produced by access as a pdf file, I was prevented from saving it because the default location was in the system32 file.  I suspect IT has restricted access to that folder.
0
Jeffrey CoachmanMIS LiasonCommented:
so,
...problem solved?
0
Juan VelasquezAuthor Commented:
I did some further investigation and that indeed was what the problem was.  Here is the revised code.

Private Sub SaveToPdf(objXlWs As Object)
    ' Comments:
    ' Params  : objXlWs
    ' Modified:
    
    On Error GoTo PROC_ERR
    
    Dim strMyFilename As String
    Dim objMyShell As Object
    Dim intchoice As Integer
    
    With Application.FileDialog(msoFileDialogSaveAs)
        .InitialFileName = "C:\Users\%userprofile%\desktop\" & objXlWs.Name
    End With

    intchoice = Application.FileDialog(msoFileDialogSaveAs).Show
    If intchoice <> 0 Then
        strMyFilename = Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
    Else
        MsgBox "You must enter or select a file name.", vbOKOnly + vbCritical, "Missing File Name"
        Exit Sub
    End If
    
    objXlWs.ChartObjects(1).Activate
    
    objXlWs.ExportAsFixedFormat Type:=0, FileName:=strMyFilename, Quality:=0, OpenAfterPublish:=True
    
    Set objMyShell = CreateObject("WScript.Shell")
    objMyShell.Run strMyFilename '& ".pdf"
    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, Me.Name & ".SaveToPdf"
    Resume PROC_EXIT
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
OK
Great, ...You can now accept you own post as the solution.
;-)
0
Juan VelasquezAuthor Commented:
I was able to determine the cause of the problem on my own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.