print as pdf

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
hi,

it would be fanatastic if some kind and clever person could sort the following issue i have

print sheet 1 as a pdf (name will change each time )
and save to c:\sheets

hoping this is viable?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,879
Office Version
365
Platform
Windows, MacOS
Something like this, which prints the active sheet. You need to determine what MyFileName is ahead of time.

Code:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\sheets\" & MyFileName & ".pdf"
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
This should get you started, please feel free to test and post any question

Sorry the codes a little rushed

Regards
jiuk
Code:
Option Explicit
'/// Jack in the UK
'/// A little basic start

Const myFILE_TYPE As String = ".pdf"

Sub TEST_PDF_SAVE()

Dim mySAVE_Location As String
Dim myFILE_NAME As String

mySAVE_Location = "C:\Sheets"

'/// Error check the path is correct
If Right(mySAVE_Location, 1) <> "\" Then
        mySAVE_Location = mySAVE_Location & "\"
    End If
        
'/// jiuk - Check Dir Exists
    Dim myFOLDER As Object
    Application.ScreenUpdating = False
    Set myFOLDER = CreateObject("Scripting.FileSystemObject")
'// jiuk - Used to test
    If myFOLDER.FolderExists(mySAVE_Location) Then
      '  MsgBox "Exists"
    Else
        myFOLDER.CreateFolder (mySAVE_Location)
     '   MsgBox "ICreated."
    End If
'/// jiuk - Checck DIR exists or create DIR

myFILE_NAME = InputBox("Type File Name Here")

'// juik - Save the Worksheet as PDF - takes few seconds
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=mySAVE_Location _
    & myFILE_NAME _
    & myFILE_TYPE

theEND:

Exit Sub
End Sub
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
This might be worth changing, prints only sheet 2 without selection etc

Regards
jiuk
Code:
myFILE_NAME = InputBox("Type File Name Here")

'// juik - Save the Worksheet as PDF - takes few seconds
'ActiveSheet.ExportAsFixedFormat _
'    Type:=xlTypePDF, _
'    Filename:=mySAVE_Location _
'    & myFILE_NAME _
'    & myFILE_TYPE
    
ThisWorkbook.Sheets(2).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=mySAVE_Location _
    & myFILE_NAME _
    & myFILE_TYPE

theEND:
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
hello Jack in the UK,

Sorry for the delay getting back to you & thanks for sorting. yeah it works great. Very much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,302
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top