Execute Excel Macro from Access failing

[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 correctly 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

xlsApp.Quit

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.

Sub RunChangetexttonumber()


Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Set xlsApp = CreateObject("Excel.Application")
Set xlswkb = GetObject("C:\Users\udcg012\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")

xlsApp.Application.Run "PERSONAL.XLSB!Changetexttonumber"

xlsApp.Quit
End Sub

Thank you for any help you can contribute!

Clayton Grove

[ssanfu]

I haven't used Access to run Excel code for a long time. Maybe one of these sites will help you:

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

http://www.fontstuff.com/index.html

http://www.meadinkent.co.uk/contactme.htm