Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Unanswered: Excel to PDF using Access VBA

    Hi all,

    I would like some guidance on which way you would tackle this mission I have.

    The goal is, from a JOB screen in Access 2003, click a button which:
    -1- gets a filename by way of concatenating fields etc.
    -2- using the calculated filename, which is always an Excel (XLS) file, convert all the pages in that workbook to PDF files.
    -3- attach all those PDFs to an e-mail in outlook.
    -4- show the email in outlook and leave it there for the user to complete and send when they are ready.

    That should give you the goal my customer is after.

    I'm only look for a solution to -2- at this point. 3 and 4 I'll tackle once I have 2 under control

    Any advice, links or anything would be greatly appreciated!

    Cheers,

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Oh, I forgot to mention, if there is completely different way of tackling this to achieve the same result, please suggest away
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    PDF Generation

    ST,

    From your post I assume you already know how to gen the Excel, so all you need in a PDF gen tool like PrimoPDF or CutePDF.

    Add code to your VBA, after saving and closing the .xls file, to print using the PDF printer, these create.

    Should be rather easy, just look for code examples on how to call different printers in the printing process.

    DBS4M

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    How you getting on with this ST - I can do this for you if you get stuck...?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Excellent, thanks for the suggestion DBS4M, tbh, I hadn't even considered a PDF printer and then just printing it...

    Come to think of it, would that work? Wouldn't it produce a print PDF dialog to be filled in? Can that be automated? Also, I should point out that the Excel file is not generated by me, but already exists.

    I also have VERY little experience in automating with either Excel or Outlook from Access -- it's just never come up before for me. I know it has for countless others though, but I have been either lucky or unlucky... depending on your point of view... to not have had to wrestle with it before.

    And Gareth, I haven't gotten anywhere yet, just planning at this point. Is going the way of a PDF printer what you would suggest as well?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Hey ST,

    I used Stephan Leban's code a few years ago on a project to get my report to pdf. I can't remember the details enough to remember if it's easily applicable to Excel or not sorry. I'll see if I can remember.

    As for working with Excel from Access, I learned quite a bit by dabbling with Poot's post in the Code Bank.

    HTH
    Me.Geek = True

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Did some Internetting and found this from Granite's site.
    Me.Geek = True

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I also have a form of the code to make PDFs from Reports. I was thinking along the lines of that kind of code to do the same with Excel, but I think that has to be done from Excel, not from Access. Not sure of course.

    Thanks for the link to the codebank post, I'll use that when the time comes

    Edit: Re: post #7, thanks for those links too Nick! That first one might just be able to be converted to run in Access. I'll check it out in detail and let you know
    Last edited by StarTrekker; 05-12-09 at 01:31.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Great Links (Specially Nick).

    Did a mod of this ST - Works as far as automating Excel from Accesss and saving individual sheets to PDFs.

    Any particulars ref Outlook - Just attach the individual .PDFs?

    Subject: ??
    Body message: ??

    Do you need to keep the PDFs after they have been eMailed or can they just be killed after eMailing?
    Last edited by garethdart; 05-12-09 at 07:32.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Done a few trials and I think the original code might need a bit of expansion.

    Not sure how consistent your Excel spreadsheets are in terms of used rows / columns?

    Anyhow - answer my Outlook queries when you have five and I'll drop you what I've got so far.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hey, great Gareth!! I really appreciate that, it will save me quite a bit of time... which is something I don't have much of atm!

    To answer your questions:

    Just attach the PDFs.

    The subject and body message can be set to either nothing or something like "Quote", since the user will want to edit them prior to sending.

    The PDFs can be killed.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Okey dokey - hang on in there...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  13. #13
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Starting Point...

    HI ST,

    Here is a mod of Ken Puls Excel code which runs as a MS Access function;

    Give this a try and see what issues you come up with - I've found that PDF Driver a little temperemental but this might just be the machine I'm on at the moment.

    You should consider this a starting point only and I will help to tidy it up but I'm going to be chocca for the next few days so anyone else is more than welcome to chip in if they wish!

    It could do with some error trapping and a bit of polishing up but hope it gets you started.

    Anyhow - give this a try and let me know whether it's what you were after.

    Option Compare Database
    Option Explicit

    Public Function PrintToPDF_MultiSheet_Early(sPDFPath As String, sPDFName As String, strExcelPath As String, strRecipient As String, Optional strSubject As String = "Quotation", Optional strBody As String = "Dear Sirs,")

    'Author : Ken Puls (Excelguru.ca | Tips and pointers for Excel and other MS Office applications)
    'Macro Purpose: Print to PDF file using PDFCreator
    ' (Download from SourceForge.net: PDFCreator)
    ' Designed for early bind, set reference to PDFCreator

    '12th May 2009 - Modifed as access function by Gareth Dart
    'Creates an Outlook messsage and adds individual .PDFs of each worksheet
    'Changed sPDFPath and sPDFName to function variables
    'Also added strExcelPath, strRecipient
    'Also strSubject and strBody as optional variables
    'Dont forget to add references for Excel, pdfcreator and Outlook

    'call PrintToPDF_MultiSheet_Early("c:\", "TESTpdf", "c:\xlsTest.xls", "someperson@domain.com", "Revised Quotation", "Dear Sirs,")

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim lSheet As Long

    Dim strOriginalName As String
    strOriginalName = sPDFName

    'Variables for excel
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlWs As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Open(strExcelPath)

    Dim olApp As Outlook.Application
    Dim olMessage As Outlook.MailItem
    Dim fsoTemp As FileSystemObject

    Set olApp = New Outlook.Application
    Set olMessage = olApp.CreateItem(olMailItem)

    olMessage.Recipients.Add strRecipient
    olMessage.Subject = strSubject
    olMessage.Body = strBody

    Set pdfjob = New PDFCreator.clsPDFCreator
    sPDFPath = xlWb.Path & xlApp.PathSeparator

    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
    MsgBox "Can't initialize PDFCreator.", vbCritical + _
    vbOKOnly, "PrtPDFCreator"
    Exit Function
    End If

    For lSheet = 1 To xlWb.Sheets.Count
    'Check if worksheet is empty and skip if so
    If Not IsEmpty(xlWb.ActiveSheet.UsedRange) Then
    With pdfjob
    '/// Change the output file name here! ///
    sPDFName = strOriginalName & xlWb.Sheets(lSheet).Name & ".pdf"
    .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
    'Syntax changed
    xlWb.Sheets(lSheet).PrintOut , , 1, 0, "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

    End If
    'Add the latest attachment to the Outlook message
    olMessage.Attachments.Add (sPDFPath & sPDFName)
    'Delete the temporary PDF
    Kill (sPDFPath & sPDFName)

    Next lSheet

    pdfjob.cClose
    Set pdfjob = Nothing

    'Close workbook without saving changes, exit Excel then clear variables.
    xlWb.Close False
    xlApp.Quit
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing

    'Display the eMail and allow user to edit then send
    olMessage.Display

    'Clear variables
    Set olApp = Nothing
    Set olMessage = Nothing

    End Function
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    Hey Gareth, I haven't had a chance to test it yet, but it looks like this would be an excellent addition to the Code Bank!
    Me.Geek = True

  15. #15
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hi Nick,

    When finished yes but I think there are a few things that need addressing;

    Whilst testing, the format wasn't perfect when I tried with various excel layouts. However once ST is happy, we can modify to a more general set of functions.

    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •