Run Excel VBA in Access VBA

[dcgrove]

Hello, I am trying to run the code below in my access 2007 database to start an excel macro. However, I need to make some changes to it that far exceed my applicable knowledge. The changes are below:

1. The macro currently only runs corrects when Excel is not running and the Personal.XLSB workbook is closed. I need theo macro to be smart enough to open Personal.xlsb if needed, or run the code if it is already open.

2. The Macro that is built in Excel VBA opens several workbooks makes changes to them, then closes some and leaves some open and send a couple of emails. The Access VBA closes excel completely and crashes if I try to remove this line

Please Login or Register  to view this content.

How do I keep the Access VBA from closing excel?

3. I currently have the path hardcoded to my personal.xlsb, however I need this to be able to run from any one of my colleagues computers so I would like the path to the personal.xlsb workbook be relative. I will be copying the appropriate excel macros to my colleagues Personal.xlsb prior to deploying this.

Please Login or Register  to view this content.

Thank you for any help you can contribute!

Clayton Grove

[OnErrorGoto0]

Why are you starting Excel and then using GetObject? If you just use the latter, you will get a reference to the workbook whether it is currently open or not (it will be started if necessary). You can then check if personal.xlsb is loaded (or distribute an addin for the code rather than messing about with people's personal workbooks) or even run the code directly from your database since you are already automating Excel.

[dcgrove]

The code I posted was something I found on the internet. I have no idea why it is written why it is. I don't have enough knowledge of VBA to be able to apply any of your suggestions, but I appreciate the help anyways.

Thanks!
Clayton Grove

[OnErrorGoto0]

My apologies - I confess that I inferred from your post that you had some familiarity with this sort of coding. If not, we can still assist, I think.
My personal recommendation would be to run all the code from the database rather than having to distribute code to others purely for the application to work. If you can post the Excel code in question, I am sure it can be adapted to work directly from the database.