Results 1 to 2 of 2

Thread: Macro for Print to PDF via cell name.

  1. #1

    Macro for Print to PDF via cell name.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Running into a problem here. I need a macro to save as a pdf using the cell A2 as part of the name, so if a2=Hospice of Atlanta the same file would be Hospice of Atlanta Survey.pdf.


    Here is what I have so far. the code keeps failing somewhere and I am not sure why.
    Code:
    Sub Button279_Click()Dim Wb As Workbook
    Sheets("Site Survey").Copy
    Set Wb = ActiveWorkbook
    s_dir = "C:\dump"
    ActiveWorksheet.SaveAs Filename:="c:\dump" & Range("A2").Value
    Wb.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=Mypath, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
    Wb.Close False
    ActiveWorkbook.Close False
    MsgBox "Fact Sheet for " & Range("A2") & ".pdf saved in UHSFS2\Common\helpdesk\remote tech Surveys"
    
    
    End Sub
    any assistance would be most helpful

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    try changing the activeworksheet to activeworkbook.
    Also it is generally a good idea to check if the file name already exists before saving to it and possibly overwriting data.
    Ken has a good block of code to do this, I believe there is a link to it from the main page. or you can copy it here

    Code:
    Public Function FileFolderExists(strFullPath As String) As Boolean
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Check if a file or folder exists
        On Error GoTo EarlyExit
        If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
        
    EarlyExit:
        On Error GoTo 0
    End Function

    Code:
    ActiveWorksheet.SaveAs Filename:="c:\dump" & Range("A2").Value
    change to
    Code:
    ActiveWorkbook.SaveAs Filename:="c:\dump" & Range("A2").Value

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •