Excel Vba (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.)

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Thanks for the info. The record macro doesn

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Having checked my laptop, the correct code appears to be:

Application.Run "PDFMaker.xla!ConvertToPDFA"

Hope that helps.

Reply | Quote

Having checked my laptop, the correct code appears to be:

Application.Run "PDFMaker.xla!ConvertToPDFA"

Hope that helps.

Reply | Quote

Thanks for the info. The record macro doesn

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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.)

Reply | Quote