Solved

Save excel worksheet to pdf and use cell to name the file

Posted on 2014-01-07
7
5,337 Views
Last Modified: 2014-01-07
I am attempting to write a macro to save a worksheet to PDF and name it based on a cell value, however I am getting an "expected end of statement error with my code.

Sub savePDF()
     '
     ' savePDF Macro
     '
     
     '
    Dim fname As String
    With ActiveSheet
    fname = .Range("f5").Value
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "C:\Users\IJ64770\Desktop\"& fName", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End With
End Sub

Any suggestinos as to where I am going wrong?
0
Comment
Question by:DAFranzmann
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39761693
Hi,

pls try

 Dim fname As String
    With ActiveSheet
    fname = .Range("f5").Value
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "C:\Users\IJ64770\Desktop\"& fName, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End With

Open in new window

Regards
0
 

Author Comment

by:DAFranzmann
ID: 39761698
Hi Rgonzo1971

The new code returns a Run-time error

Automation error
The filename,Directory name or volume label syntax is incorrect.

Regards
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39761699
Extra double quote after fName
Sub savePDF()
'
' savePDF Macro
'

'
    Dim fname As String
    With ActiveSheet
        fname = .Range("f5").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                             "C:\Users\IJ64770\Desktop\" & fname, Quality:= _
                             xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                             OpenAfterPublish:=True
    End With
End Sub

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:DAFranzmann
ID: 39761704
I have added the extra quotation marks and it returns a:

compile error
Syntax Error

Sub savePDF()
     '
     ' savePDF Macro
     '
     
     '
Dim fname As String
    With ActiveSheet
    fname = .Range("f5").Value
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "C:\Users\IJ64770\Desktop\" & fname", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End With

End Sub
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39761719
Remove the quotation mark after fname.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39761721
Can you verify that C:\Users\IJ64770\Desktop\" & fname is valid?
0
 

Author Closing Comment

by:DAFranzmann
ID: 39761724
Thanks MacroShadow

Sub savePDF()
     '
     ' savePDF Macro
     '
     
     '
Dim fname As String
    With ActiveSheet
    fname = .Range("f5").Value
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "C:\Users\IJ64770\Desktop\" & fname, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End With

End Sub
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question