Excel to PDF using Visual Studio 2005 -VB

[jason490]

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

 

[BrianB]

This might help

http://www.pdf995.com/

 

[BrianB]

After my earlier message I found time to explore this a bit deeper. To my amazement, despite having to use Sendkeys to mimic the process, the code below seems very stable on my 2 small sample sheets - one of which has 2 pages. It could probably do with a bit of tweaking of Wait times depending how long your sheets take to process.

I do not think this would be possible without PDF995 because as far as I know a PDF file is not like those for usual printers, it contains a lot of coded instructions like a macro sheet (which Acrobat can translate into "proper" printer language). Open one in a text editor to see. If you have an Apple computer to try, you might have more success.

Hope you find this useful.

'============================================================================
'- MACRO TO PRINT ALL WORKSHEETS TO PDF995 .PDF FILE
'============================================================================
'- Requires installation of application from http://www.pdf995.com/
'- ** Record macro of a test print to get your version of "PDF995 on Ne01:"
'- ADJUSTMENT : "Wait" lines repeated to enable adjustment to your system
'- (MAY NEED LONGER TIMES FOR BIGGER SHEETS)
'- run from the worksheet - not the VB Editor (produces time delays)
'- ** DO NOT USE THE COMPUTER WHILE THIS IS RUNNING **
'-
'- Brian Baulsom  July 2007
'============================================================================
Sub PRINT_SHEETS()
    Dim CurrentPrinter As String    ' current printer to be reset
    Dim PDF995printer As String
    Dim ws As Worksheet
    Dim wsName As String
    '------------------------------------------------------------------------
    '- delete test PDF files     (tested on workbook with 2 sheets)
    On Error Resume Next
    Kill "C:\PDF995\Sheet1.PDF"
    Kill "C:\PDF995\Sheet2.PDF"
    On Error GoTo 0
    '------------------------------------------------------------------------
    '- save current printer
    CurrentPrinter = Application.ActivePrinter
    '------------------------------------------------------------------------
    '- ** set PDF995 printer ** needs your version
    Application.ActivePrinter = "PDF995 on Ne01:"
    '------------------------------------------------------------------------
    '- Print worksheets one at a time
    For Each ws In Worksheets
        'Set ws = ActiveSheet
        wsName = ws.Name
        Application.StatusBar = ws.Name
        '--------------------------------------------------------------------
        '- Print Sheet
        ws.PrintOut
        DoEvents
        '--------------------------------------------------------------------
        '- PDF995 dialog box
        '- stop code until File Save dialog opens
        Application.Wait Now + TimeValue("00:00:05") ' 5 seconds
        '--------------------------------------------------------------------
        '- enter worksheet name into dialog box
        SendKeys wsName, True
        Application.Wait Now + TimeValue("00:00:01") ' 1 second
        '--------------------------------------------------------------------
        '- close File Save dialog - requires keypresses - 2 Tabs and Enter
        SendKeys "{TAB}", True
        SendKeys "{TAB}", True
        SendKeys "{ENTER}", True
        '--------------------------------------------------------------------
        '- stop code until ...
        '- PDF995 application opens - file saves - Acrobate appears
        Application.Wait Now + TimeValue("00:00:15") ' 15 seconds
        '--------------------------------------------------------------------
        '- close Acrobat
        SendKeys "%{F4}", True    ' keys ALT+F4
        DoEvents
        Application.Wait Now + TimeValue("00:00:01") ' 1 second
    Next
    '------------------------------------------------------------------------
    '- reset printer
    Application.ActivePrinter = CurrentPrinter
    MsgBox ("Done")
    Application.StatusBar = False
End Sub
'=========== END OF PROCEDURE ===============================================

 

[jason490]

Thanks Guys for the responses. I finally worked on the VB side of it for about a week and it was a headache. I decided to do some research and the lady that is using this has acrobat with distiller. You can convert the excel files to one large PDF, then extract the PDF's to seperate sheets just like she needed. So I stopped on the coding part, one of the hardest things I have yet to figure out.