Print a PDF using a String for the name

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
I cant figure out how to get a sub to

'Here is what I have:

Sub ExportDeptOneKPI
CallKPIPrint ("Dept One")
End Sub

'This calls the below but how do I get the name in the spot with the ???????:

SubKPIPrint (PDFPrint as String)
Worksheets (PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\DEPARTMENTS\DATA\???????.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
End Sub

'the Export Sub then runs for Dept Two, Three etc.

Do I need to set the whole filename as a string before putting it into the export part of the code?
If you have an solution or alternate idea it would be much appreciated.

Also I would like this to all happen without actually calling up the sheets (they are all hidden).

thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You mean like this

Code:
SubKPIPrint (PDFPrint as String)
Worksheets (PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\DEPARTMENTS\DATA\" & "Dept One" & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
End Sub
If you put your filename in say, cell A1, you could then change the filename in the cell to suit

Code:
SubKPIPrint (PDFPrint as String)
Worksheets (PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\DEPARTMENTS\DATA\" & range("A1").value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
End Sub
 
Upvote 0
Put the list of names of your leaves on a "Dept" sheet in the "A" column. eg.

Dept
<b>Dept</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:129.27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">DEPT</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">Dept One</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">Dept Two</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">Dept Three</td></tr></table>


Use this code.
The sheets can be hidden, the macro must be visible momentarily to generate the pdf and then hide the sheet again.
Change "Dept" by the name of your sheet that contains the list of sheets to be sent to pdf

Code:
Sub ExportDeptOneKPI()
    Dim sh As Worksheet
    Set sh = Sheets("Dept") 'name of your sheet with the departments
    For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
        Call KPIPrint(sh.Cells(i, "A").Value)
    Next
End Sub


'This calls the below but how do I get the name in the spot with the ???????:
Sub KPIPrint(PDFPrint As String)
    On Error Resume Next
    Application.ScreenUpdating = False
    Sheets(PDFPrint).Visible = True
    Worksheets(PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\trabajo\" & PDFPrint & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheets(PDFPrint).Visible = False
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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