Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-07
7
Medium Priority
?
5,601 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

715 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