Excel to PDF Naming convention

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
88
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:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Try
Code:
strName = "WORK AS OF " & Format(Date, "MM-DD-YYYY")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
See post#3 ;)
 

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
88
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,776
Messages
5,488,778
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top