+ Reply to Thread
Results 1 to 17 of 17

Export Excel Tab to PDF. Macro Assistance Request!

  1. #1
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Export Excel Tab to PDF. Macro Assistance Request!

    Good day and a Hello!

    I need some help with a VBA that can ideally export all contents in an excel sheet named "My Analysis" to a PDF.

    Is this possible? My belief after seeing such great solutions here is that it very much is.

    Could someone please help me on this...

    Happy to share a sample file if needed. Please let me know....

    Thanks,
    Spi
    In your greatness, remain humble!

  2. #2
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Hi Spi,

    Sure this can be achieved:

    When you save as PDF it really prints the file as a PDF so you have to have your pagebreaks set correctly.

    Then you want hit ALT+F11 to bring up the VBE, Visual Basic Editor, insert a Module and paste the code below:

    Please Login or Register  to view this content.
    Just change the FileName from YOUR_FILE_NAME.PDF to anything you want it to be and add the save location in front say C:\YOUR_FILE_NAME.PDF" if you want it to be on your C: drive.

    Hit ALT+F8 to run the ExportPDF macro or you can insert a button and assign the macro to it.

    Thanks,

    Lifesigns
    If the post was helpful please click the black star on the bottom left to add some reputation and mark your thread as SOLVED.

    A day with nothing new achieved or learned, albeit however small, is a day lost forever?

    Constant Never Ending Improvement

  3. #3
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Thanks a bunch! Just one thing can I have it to automatically save it as PDF using the file name. My files have 15-20 tabs but for now I am only attempting to export 1 of them to PDF.

    I just tried it and it is giving me a run time error 5. In valid procedure call or argument....I have another macro that is on this sheet. I added your snippet as a separate module and assigned it a button. But faced the above error on running it. Please advise.
    Last edited by spiwere; 07-03-2013 at 02:18 PM.

  4. #4
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Try this:

    Please Login or Register  to view this content.
    Just change the PDFName Variable accordingly for you location.


  5. #5
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Thanks! But it is giving me a run time error 5. In valid procedure call or argument. This is what I used...

    Line changed in Red font. Am I doing something incorrectly?

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    What line does the code break on?

    Is this for Excel 2007?

  7. #7
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    It could be 2007 and 2010 both. It really depends as we have a mix of both versions.

    The code breaks at :

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    No it should work for 2007 and 2010. Can you save the file manually as a PDF from the ribbon save as PDF? If not then that would be why.

    If you can save it in Excel manually then the code should be fine.

    Here is a link about saving PDF in Office.

    http://office.microsoft.com/en-gb/he...010064992.aspx

  9. #9
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Looks like that is the case. I can't save it manually. The save as option doesn't show PDF or XPS option. Maybe I need to Download / Install and use the Publish as PDF or XPS add-in from Microsoft or do you think there is another way out?

    If not, then I'll give the installation a try, and then comeback.

    Thanks for all the hep! I'll seek further advise as I progress

  10. #10
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    No worries.

    Check this link it may enable the exports.

    http://www.microsoft.com/en-us/downl...ils.aspx?id=7#

  11. #11
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    This works great. However, I need a small modification w.r.t saving the PDF to the local desktop of any user rather than specifying a fixed path. This would be used by 100's of employees across the globe so a fixed path won't really work. It has to be generic.

    Thanks for all your help!

  12. #12
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Are they going to open the file from a network location or will they each have a copy?

  13. #13
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Each will get a copy of it via email.

  14. #14
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    If each are going have a copy you can just remove the location in the PDFName variable this will export the PDF to the same folder the Excel file is in.

  15. #15
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Got it! Just to be sure the final code would be:

    Please Login or Register  to view this content.
    Please correct me if I am off here. Apoloigies as haven't been able to test it. I'll close it as solved once I have your go ahead.

    Thanks again for your help. This would be of great use!

  16. #16
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    You need to change out the variables.

    Please Login or Register  to view this content.
    That should do it.

  17. #17
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Export Excel Tab to PDF. Macro Assistance Request!

    Fabulous, thanks!

    By the way, you just posted your 100th post on the Forum so Congratulations on that...

    Best,
    Spi

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1