Excel to PDF Naming convention

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
93
Hi. I have the macro below which upon running converts the active sheet in excel to pdf.

Code:
Sub SaveasPDF()


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & ""

strName = Format (Now(), "WORK AS OF MM-DD-YYYY")

'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & strPathFile

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub
[B][/B]
2 Issues I'm running into:

1. The pdf name comes in weird numbers. Example, instead of WORK AS OF 06-22-2019, it comes as 6ORK AS OF 06-21-2019.

2. There is a sheet call 'total' in my workbook which I want to be automatically included every-time while converting to pdf along with the active sheet that they will run the macro on.

Please help.

Thank you in advance.
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi & welcome to MrExcel.
Try
Code:
strName = "WORK AS OF " & Format(Date, "MM-DD-YYYY")
 
Upvote 0
And to print both sheets try
Code:
[COLOR=#ff0000]Sheets(Array("Totals", wsA.Name)).Select[/COLOR]
wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:="+Testing.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
 
Upvote 0
I don't want to hard code my sheet name because the excel has 30 sheets. Our users can run macro from any sheet. I just want the active sheet + 'total' sheet to be converted into pdf.
 
Upvote 0
I just want the active sheet + 'total' sheet to be converted into pdf.
That's what it does :)
Just add the line in red to your code as shown
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top