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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,538
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,538
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,538
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,538
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,538
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,934
Members
407,475
Latest member
Dix_Fix

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top