Solved

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

Posted on 2014-01-07
7
4,772 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
  • 3
  • 3
7 Comments
 
LVL 49

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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