Save active sheet with the cell content

[adamsm]

Hi, the following code saves the active worksheet as the name specified in the code to the designated folder.

How could I make the code to save the sheet with the cell content of cell "A2"

[COLOR=#e56717][B]Sub[/B][/COLOR] ConvertExcel2PDF()[COLOR=#008000]
[/COLOR]
    [COLOR=#151b8d][B]Dim[/B][/COLOR] sFilePathnName [COLOR=#151b8d][B]As[/B][/COLOR] [COLOR=#f660ab][B]String[/B][/COLOR]
 
    [COLOR=#8d38c9][B]With[/B][/COLOR] ActiveWorkbook
    [COLOR=#008000]'   Current Excel file Location and File name to Save as PDF File:
[/COLOR]        sFilePathnName = .Path & Application.PathSeparator & Left(.Name, Len(.Name) - 4) & [COLOR=#800000]"pdf"[/COLOR]
 
    [COLOR=#008000]'   Set your custom Location and File name to Save as PDF File like this:
[/COLOR]        [COLOR=#008000]'sFilePathnName = "C:\MyFolder\MyFile.pdf"
[/COLOR]    
    [COLOR=#008000]'   Conversion Code...
[/COLOR]        .ExportAsFixedFormat _
        [COLOR=#151b8d][B]Type[/B][/COLOR]:=xlTypePDF, _
        Filename:=sFilePathnName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=[COLOR=#00c2ff][B]True[/B][/COLOR], _
        IgnorePrintAreas:=[COLOR=#00c2ff][B]False[/B][/COLOR], _
        OpenAfterPublish:=[COLOR=#00c2ff][B]True[/B][/COLOR]
    [COLOR=#8d38c9][B]End[/B][/COLOR] [COLOR=#8d38c9][B]With[/B][/COLOR]
 
    MsgBox [COLOR=#800000]"File converted successfully"[/COLOR], vbInformation, [COLOR=#800000]"sample"[/COLOR]
[COLOR=#8d38c9][B]End[/B][/COLOR] [COLOR=#e56717][B]Sub[/B][/COLOR]

Any help on this would be kindly appreciated.

Thanks in advance.

 

[VoG]

Try this

sFilePathnName = .Path & Application.PathSeparator & Range("A2").Value & ".pdf"

 

[adamsm]

Thanks for the help and I do really appreciate it.

 

[adamsm]

The current code changes the sheets that contain data into PDF. How could I make the code to change only the active sheet to PDF instead of the whole book?

 

[VoG]

Try

Sub ConvertExcel2PDF()

    Dim sFilePathnName As String
 
    With ActiveSheet
    '   Current Excel file Location and File name to Save as PDF File:
        sFilePathnName = .Parent.Path & Application.PathSeparator & .Range("A2").Value & ".pdf"
 
    '   Set your custom Location and File name to Save as PDF File like this:
        'sFilePathnName = "C:\MyFolder\MyFile.pdf"
    
    '   Conversion Code...
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=sFilePathnName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    End With
 
    MsgBox "File converted successfully", vbInformation, "sample"
End Sub

 

[adamsm]

Thanks for the help. The code now does save the active sheet, But it saves the file to the desktop. How could I make the code to save the sheet to the specified folder in the code?

 

[VoG]

This line of code

sFilePathnName = .Parent.Path & Application.PathSeparator & .Range("A2").Value & ".pdf"

sets the path for the pdf to the same folder as the workbook. When I tested it with a workbook in C:\example, that is where the pdf file was created, not the desktop.

Is that not what you want?

 

[adamsm]

I don't want the pdf file to be saved in the same folderas the workbook. But to the folder specified in the code only.

How could I achieve this?

 

[VoG]

Try this - change the bit in red to suit

Sub ConvertExcel2PDF()

    Dim sFilePathnName As String
 
    With ActiveSheet
    '   Current Excel file Location and File name to Save as PDF File:
        sFilePathnName = "C:\MyFolder" & Application.PathSeparator & .Range("A2").Value & ".pdf"
 
    '   Set your custom Location and File name to Save as PDF File like this:
        'sFilePathnName = "C:\MyFolder\MyFile.pdf"
    
    '   Conversion Code...
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=sFilePathnName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    End With
 
    MsgBox "File converted successfully", vbInformation, "sample"
End Sub

 

[adamsm]

Thanks for the help. It works fine now & I do really appreciate your help.