Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Sacramento, California, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Vba (Excel 2000)

    I'm using excel 2000 and adobe 5.0. When I load excel the menu has Acrobat with a menu item Convert to Adobe PDF, I would like to know the code to select and execute or click on Convert to Adobe PDF using vb.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel Vba (Excel 2000)

    You can "click" a menu choice using the CommandBars collection. I don't know the exact wording of the menu item you have, but here's an example:

    Sub TestSaveAs()
    Application.CommandBars("Worksheet Menu Bar").Controls("&File"). _
    Controls("Save &As...").Execute
    End Sub

    (The & precedes the underlined accelerator key, if applicable.)

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    Sacramento, California, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Vba (Excel 2000)

    Thanks. I tried:
    "CommandBars("Worksheet Menu Bar").Controls("Acro&bat"). _
    Controls("&Convert to Adobe PDF").Execute"
    This command work to a point. It starts Adobe's "Convert to PDF File" button in excel, save the excel file, and coverts and saves the convertion. They it locks up. It closes the excel file and locks up excel. There must be something else happening when you activite the buildin button. When I just click on the button it work as it should. (converts to pdf and returns to excel)

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel Vba (Excel 2000)

    Sorry to hear about the lock-up! It sounds as though the code works, but perhaps VB is locking up waiting for some signal that the command it invoked has completed. Ctrl+Break is supposed to halt the VB runtime, but it doesn't always work. You could try it, but all this might tell you is that yes, VB is still running...

    I can't remember whether it was in thread or another one, but I think it was a little bit difficult to find out the name of the procedure that the button calls. You could try extracting the OnAction and Parameter properties of that button using the Immediate window in the VBE. E.g.,

    ?CommandBars("Worksheet Menu Bar").Controls("Acro&bat").Controls("&Convert to Adobe PDF").OnAction

    Then you could try calling those procedures directly. Not sure this would solve the problem.

    Another option is to change the Acrobat toolbar buttons so that they have a shortcut key and use SendKeys to issue that key combination. But I suspect you are trying to do something more hands off. Maybe you can explain your application a bit more?

  5. #5
    Lounger
    Join Date
    Sep 2004
    Location
    Evesham, Worcestershire, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Vba (Excel 2000)

    In the hope of not appearing too naive, what happens if you use the record macro option to capture some code when the button is pressed. I don't know how well this works with a third party application, but it might be worth looking at the code Excel generates to see if that has any pointers in it.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel Vba (Excel 2000)

    Good idea! At least it would be more information, which is what we need.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Vba (Excel 2000)

    Hi,
    Have you tried something like:
    Application.Run "PDFWriter.xla!printpdffile"
    You may need to replace the 'printpdffile' bit with whatever is assigned to the toolbar button ( you can check by selecting Tools-Customize... then selecting the Convert to PDF button and choosing Modify Selection and then Assign Macro... to see what the assigned procedure name is.)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Jul 2004
    Location
    Sacramento, California, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Vba (Excel 2000)

    Thanks for the info. The record macro doesn

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Vba (Excel 2000)

    Hi,
    I wasn't suggesting you record a macro. If you follow my instructions, it should show you the name of the procedure that the Convert to PDF button calls. You can then use code in the form:
    Application.Run "PDFMaker.xla!procedurename" to run the same code that the Convert to PDF button calls - i.e. the actual code in the PDF add-in. This should be exactly the same as clicking on the menu item manually. I have Acrobat 5 on my laptop so I will try it tomorrow and see what the actual code should be, if I get a chance.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Vba (Excel 2000)

    Having checked my laptop, the correct code appears to be:
    <pre>Application.Run "PDFMaker.xla!ConvertToPDFA"</pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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