MACRO FOR PRINTING EXCEL FILE INTO PDF

[VIX]

We are using the following code for printing excel file into pdf

Sub Printing()
Dim pages As Integer
pages = 1
Do
Worksheets("Sal Slip - PM").Activate
Worksheets("Sal Slip - PM").PrintOut From:=pages, To:=pages
pages = pages + 1
Loop Until pages > Cells(3, 2)
End Sub

problem with the code is
1. We have to select cancel button 36 times (no of pages in the sheet) to end the macro I tried getsavesdialog code but it leads to saving the excel file again
2. We want the macro to select file name automatically from specified range
We cannot use export as pdf option since we want print various pages in the single worksheet to multiple pdf
please help on this

 

[Stevie]

Hi VIX,
try using

sub printing()
application.displayalerts = false
'code
application.displayalerts = true
end sub

in order to remove alerts.

To set the file name, use:

Worksheets("Sal Slip - PM").PrintOut From:=pages, To:=pages, PrintToFile:=true, PrToFileName:='put filename here

You can assign a string variable to be the filename for the printout statement, and have it update to the appropriate cell value when you change sheet.
Let me know if you need any more help.

range.printout info:
https://msdn.microsoft.com/en-us/library/office/ff197435.aspx

---
If this was helpful, please click like!

 

[VIX]

Hi VIX,
try using

sub printing()
application.displayalerts = false
'code
application.displayalerts = true
end sub

in order to remove alerts.

To set the file name, use:

Worksheets("Sal Slip - PM").PrintOut From:=pages, To:=pages, PrintToFile:=true, PrToFileName:='put filename here

You can assign a string variable to be the filename for the printout statement, and have it update to the appropriate cell value when you change sheet.
Let me know if you need any more help.

range.printout info:
https://msdn.microsoft.com/en-us/library/office/ff197435.aspx

---
If this was helpful, please click like!

Thank you for your help stevie i had tried the code but still the problem persists, still i need to click the cancel number of times to stop the code, the worksheet has 32 pages and i requires to click cancel button 32 times to abort the code and file name not updating automatically

 

[VIX]

Hi Stevie I changed the code as follow

Sub Printing()
Dim pages As Integer
Dim fname As Integer
pages = 1
fname = 1
Application.DisplayAlerts = False
Do
Worksheets("Sal Slip - PM").Activate
Worksheets("Sal Slip - PM").PrintOut From:=pages, To:=pages
newhour = Hour(Now())
newminute = Minute(Now())
newsecond = Second(Now()) + 2
waittime = TimeSerial(newhour, newminute, newsecond)
Application.Wait waittime
filename = "C:\Documents and Settings\admin\Desktop\Payroll\ " & fname
SendKeys filename & "{enter}"
pages = pages + 1
fname = fname + 1
Loop Until pages > Cells(3, 2)
Application.DisplayAlerts = True
End Sub
it captures file name for subsequent files but I need to type file name for the first file i tried a lot to figure out the mistake but i failed. Other problems still exists. kindly help on this and also it returns the error at end "compile error expected line number or label or statement or end of statement"

 

[VIX]

it captures file name for subsequent files but I need to type file name for the first file i tried a lot to figure out the mistake but i failed. Other problems still exists. kindly help on this and also it returns the error at end "compile error expected line number or label or statement or end of statement" [/code][/quote]