Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Converting Excel Worksheet to PDF using VBA

I have a small problem when trying to to convert Excel to *.PDF using VBA.
All I need to do is to open adobe and the save as dialog box to be straight
on to a shared directory rather than displaying the My Docs folder for the
save point.

Any ideas?

Thanks!!


Post your answer or comment

comments powered by Disqus
hi frnds,

i am new to excel coding...

my doubt is

1) how to convert excel sheet to pdf using macro
2) if User selects macro within “sheet1” to send internal PDF copy via email using Outlook, and saves PDF to default directory

Thanks & regards
u r frnd..

I need help converting Excel worksheets to a PDF file. I have a fairly complicated file (at least for me) that is used by construction estimators. The file consists of about 12 worksheets and contains hundreds of formula, checkboxes, etc. At present the estimators print out only the relevant part of each worksheet after they enter the job details, then scan the print outs to create a PDF file, then email the PDF file to our field offices. I am looking for an efficient method to directly create PDF file of only the print range portion of each worksheet. I really just need a duplicate of the print in PDF format. I do not need hyperlinks, metadata, or anything fancy in the PDF. I have read about the “click-to-convert” program, “metadata assistant”, and “verypdf” and maybe an Adobe product would work, but I have no experience with any of them. Would printing to Microsoft Document Image Writer be a good alternative? Cost is really not an issue, the company will buy whatever works best. Thanks in advance to all.

Hi All,

i am new to excel coding....

I have one excel sheet. in that 3 sheets are there,they are sheet1,sheet2,sheet3. so i want convert sheet1 data to pdf and also convert sheet2 data to pdf using coding vba(Macro). so please provide code or any examples.

please help me.....

Thanks in Advance
suresh

Hi All,

I have the below macro which prints all excel sheets to pdf using pdfcreator as printer, it works absolutely fine but I want to come up with a userform where all excel sheets of a workbook will be listed down and then the user will be able to select the sheets and create the pdf as per his requirement. The Userform will do the following things for user.

1) Userform will allow user to select the sheets which he want to print to pdf.
2) It will allow him to select a option wherein he will able to print multiple sheets to one pdf.
3) If he doesn't select that option it will print each sheet to a separate pdf and save it on a selected path.

I have come up with a userform but I need a help to incorporate the below macro to the same so that they work as per above requirements.

' Print Multiple Worksheets to a Single PDF File:
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()


    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = New PDFCreator.clsPDFCreator

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub
Attached is my macro file.

Thanks a lot for your help in advance.

When trying to convert excel file to PDF Adobe Acrobat gives quite strict area where everything should be put into. I am making an invoice and it seems to be impossible to put the whole thing in given area. Is it possible to expand that area?

Hi, everyone -

I know it's been a long time, but I thought I'd jump back in with a doozie that I've been struggling with. I 've included a small bit of code below that is a small part of a much larger strip. Basically, what I use this bit for is to send a copy of one worksheet, out of a much larger income calculation spreadsheet, to a staff person.

It works really slick, but I have one problem: when the staff person gets the email and opens the copies single worksheet, it gives a couple messages about wanting to update the formulas in the worksheet itself. I'm thinking, to eliminate these messages, I'm hoping there is a way to modify my code that that, maybe, after ActiveSheet.Copy, I can convert the worksheet to a pdf... then finish out the code. Has anyone had any success in doing this before?


	VB:
	
 
    Sheets("FSS_Worksheet").Unprotect ("led52not") 
    Sheet20.Select 
    [b]ActiveSheet.Copy[/b] 
    ActiveWorkbook.SendMail Recipients:="jdoe@wvpha.org", Subject:=" FSS Escrow Sheet" 
    ActiveWorkbook.Close False 
     
    ActiveSheet.Shapes("object 6.wav").Select 'fss reminder msg'
    Selection.Verb Verb:=xlPrimary 
     
    MyTimer = Timer 
    Do 
    Loop While Timer - MyTimer < 4 
     
    paperwarning = MsgBox("I HAVE HEARD THE REMINDER MESSAGE AND PROMISE, UPON PENALTY OF SOMETHING REALLY BAD HAPPENING,
THAT I WILL PASS ON ACCOUNTING'S COPY OF THE FSS WORKSHEET TO THE APPROPRIATE STAFF PERSON.", vbOKOnly, "REMEMBER TO
PROPERTLY DISTRIBUTE COPIES OF FSS WORKSHEETS!!!") 
    Sheets("FSS_Worksheet").Protect ("led52not") 
    Sheet2.Select 
    [a1].Select 
    Sheets("data_entry_sheet").Protect ("led52not") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks to everyone for the help.

Take care.

Golf

Hi,

My issue is that I am trying to write an macro that will automatically convert the excel document I am currently in to PDF using the PDFDistiller.

When I do this I run into the problem where a dialouge box pops up asking me "Save PDF As". Is there a way to bypass this dialouge box or a way for excel to recognize this prompt and press save.

Thanks for your help!

Hi for all,

I have 30 tables in 15 worksheets and now I want to make those be PDF format in one file. It seems that I just can convert one worksheet to PDF every time and then conbime into one. Can I write a VBA code to solver this problem? If you can provide some examples or links, it will be great.

Thanks!

Fei

I have Excel and Adobe Acrobat Professional installed on my computer. Is there a way to create a macro that when activated will convert each worksheet in my excel file to a separate pdf file?

Hi,

Is there a UDF or macro that can help me convert hyperlinks in an excel workbook to PDF format?

For example, say cell A1 on one spreadsheet has a hyperlink that when clicked takes me to cell A1 on another spreadsheet within the same workbook. When I convert the entire workbook to PDF format, the links are not sticking. I have to manually re-link everything inside the PDF file.

I'm actually using an Excel Template, so the hyperlinks will remain constant each time I print to PDF. Any help would be much appreciated!

Well, I must be crazy today. I want to put an Excel workbook to pdf format and print it out at the click of a button located in the book. However, when I try to record the macro to get a feel for how to control pdf with Excel, I get a pdf file but no printout and no code to veiw! Any suggestions?

AC

I have searched all the topics and do not see anything that would be exactly like this unless I just didn't see it. I am needing to write a VB program that will automate converting Excel sheets to PDF's. The problem is that the sheets have multiple tabs at the bottom, workbooks. So the user is needing each and every tab converted to a PDF. It is one file name, like WH.xls but with multiple tabs, like stated all these tabs have to be converted and saved individually. I think in one workbook there is like 43 seperate sheets if you do it manually. If you do it through adobe then it spits it out as one large PDF, which will not work. I would like to do this all in VB with no outside programs, we have Acrobat 7 with Distiller.

Thanks for any help.

Jason

These is my situation
I know how to convert Excel to PDF, but the problem is i have more than 25 sheet in a workbook. I know how to convert each sheet to pdf one by one.
Can anyone share with me any other way to do it easier. i feel that it wasted many time by doing the repeatedly step again and again.

I need to select a user defined list of worksheets all at once using VBA.

I have a string variable containing a list of selected worksheets.
for example: mystring = "Sheet1,"&"Sheet2,"&"Sheet3"

Is it possible to pass the variable mystring as an argument in the following
function:
Sheets(Array(mystring)).Select without it generating an error message ?

Or is there another way of selecting worksheets at once using VBA ?

Hello,

I have created a report on a sheet in Excel 2007 made up of charts and shape objects.

I'm succesfully able to export the report into PDF and save it at a specific location on hard drive with a specific filename using following VBA code:


	VB:
	
 Export_to_PDF 
     
    Dim pdfName As String, folder As String 
     
    pdfName = Sheets("sheet1").Cells(counter, 136).Text 
     
    folder = Sheets("sheet1").Cells(15, 2).Text 
     
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder + "" + pdfName _ 
    , Quality:=xlQualityMedium, IncludeDocProperties:=False, _ 
    IgnorePrintAreas:=False, OpenAfterPublish:=False 
     
    MsgBox "Reports was created and saved in folder " & folder 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now I want to replicate the procedure in Excel 2003, but somehow I cannot using the above code!

How can I export the report in Excel 2003 to PDF? What changes should I make to the above code?

I want to make it very simple for the end users. So if exporting to PDF in Excel 2003 requires installing additional softwares for the users, is it easier to export it to Word or JPG or BMP file?

Thanks in advance!!

I need to insert an Excel worksheet in the PACS (Picture Archiving and
Communications System) in our medical imaging department. Our
PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the
Excel sheet to PDF and then JPEG and then insert it in the PACS.
Is there a way (macro? VBA?) to program a cell (lets call it "Save as a
Picture") so that when I click on that cell, the worksheet get saved as jpeg
or tiff or dicom format?
Thank you for any comments or help in advance.
Dori

Hello.
I have a specific problem.

I need to embed/insert a whole excel sheet into a powerpoint presentation as an object.

I have to use VBA to pull details from excel sheet and populate a PPT template. There is another workbook which is supposed to be inserted as an object, e.g. excel icon will be there in the powerpoint and on clicking it the the worksheet will open.

Here is what I have been using:

	VB:
	
oPS=oPP.Slides(1) 
oPS.Shapes.AddOLEObject _ 
Classname:="Excel.sheet",FileName:="abc.xls",DisplayAsIcon:=msoTrue 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where oPS is

	VB:
	
 Powerpoint.slide 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The error is am getting is:
shapes(unknown member): invalid request

There was some info on net but they were using ActivePresentation and I am creating macro in excel not in powerpoint.

Sorry If a lot of what I say is not making much sense. I am a total newbie to VBA and this is a one off assignment. Any help will be greatly appreciated.

I need to insert an Excel worksheet in the PACS (Picture Archiving and
Communications System) in our medical imaging department. Our
PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the
Excel sheet to PDF and then JPEG and then insert it in the PACS.
Is there a way (macro? VBA?) to program a cell (lets call it "Save as a
Picture") so that when I click on that cell, the worksheet get saved as jpeg
or tiff or dicom format?
Thank you for any comments or help in advance.
Dori

I have a small problem when trying to to convert Excel to *.PDF using VBA.
All I need to do is to open adobe and the save as dialog box to be straight
on to a shared directory rather than displaying the My Docs folder for the
save point.

Any ideas how I can write this in VBA?

Thanks!!

Hi everyone,

I know that there are many programs to convet excel worksheets to pdf, but is there also a vba code that can convert an excel worksheet to PDF?

Thanks,

Hi,
I am hoping someone can help me with a macro for converting excel worksheets to a PDF, saving and attaching it to an email. If cell A8 is empty the worksheet is to be skipped, the sheets where A8 is occupied i want them converted into one pdf file, saved and attached to an email. I have Excel 2003 and Adobe Professional 6.

Can someone please help?

Thanks
dawn

Hi all.

I want to use a macro to automate the creation of a PDF file as a I have few invoices to do every month. I have searched the net hi & lo & this is the closest I have but the problem is that after creating the PDF it hangs when trying to close the PDFCreator app. I am using Excel 2002 on XP SP3.

Code:
Sub PrintToPDF_Late()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for late bind, no references req'd

    Dim pdfjob As Object
    Dim sPDFName As String
    Dim sPDFPath As String

    '/// Change the output file name here! ///
    sPDFName = "testPDF.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub
It appears that the PDFCreator is not releasing the printjob so as to close the pdfcreator.

Any ideas would be appreciated.

After much searching and many times of trial and error, I have finally found a macro that will quickly and easily convert an excel worksheet to PDF and automatically do a save as with a filename based on content in a specified cell. Hope it works for you as well as it works for me!

The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:

' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.

Filename = "C:Folder1SubFolder1" & ActiveSheet.Range("InvNbr").Value & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application. The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False

' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True

If you don't have Acrobat Pro and are using a free version of PDF conversion software, try the following (it has been tested on Excel 2003 and CutePDF):

' This line of code calls your PDF printer and runs the conversion. Record your own macro to call your PDF printer and copy and paste it here.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

' This set of code tells the macro to pause for 2 seconds. This will allow for the PDF printer to run through its process and prompt you for a filename.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

' This line of code specifies your directory as well as the cell or range which you want the filename to come from.

Filename = "C:Folder1SubFolder1" & ActiveSheet.Range("InvNbr").Value & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application (i.e. the prompt window). The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False

The beautiful thing about these macros is that you can specify your directory, so you can customize them to send your worksheet to whatever directory you want and save it under whatever filename you want. Awesome!

I hope someone else finds this useful!!!

Hi,
I need a way to easily convert an Excel worksheet which is used
extensively at operational level to a Powerpoint slide for the management to
view summaries of operational information. It's just an exact "copy" of that
particular worksheet to be shown on Powerpoint and a means to automate this
process. ie. using a script/program. Thanks in advance.


No luck finding an answer? You could always try Google.