Macro for Print to PDF via cell name.

Kaharthemad · Feb 28, 2012

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

Simi · Feb 29, 2012

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 ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'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

Macro for Print to PDF via cell name.

Kaharthemad

New member

Simi

New member