Easy way of using excel vba in access vba

Question

I have a fairly big piece of excel vba code that I need to run. The problem is, that I need to start it from Access and the company security does not allow the starting of makros from an external trigger.

So I can start the excel code when in excel but I need to basically have to open the spreadsheet and have access do the same work that the excel code does.

Can I just copy the code and have it all run in Access and just somehow tell it that "ActiveChart" and "WorkSheets("XY")" are within Excel file "Z"?

Thanks

Kaz

Access VBA can run any Excel VBA. You'll just have to call the appropriate objects and constants. Personally, I try to never run anything in Excel. As a relational database, Access has a more robust application and automation environment with a SQL engine, GUI interface builder, and reporting generator, much better tool than a flatfile spreadsheet. — Parfait
– Parfait, Commented Aug 7, 2015 at 0:04

Mark Butler · Accepted Answer · 2015-08-06 12:57:35Z

2

Use an instance of Excel in your Access VBA:

You will need to set a reference to the Microsoft Excel 15.0 Object Library

Dim appExcel As Excel.Application
Dim wrkBook As Excel.Workbook

Set appExcel = New Excel.Application
Set wrkBook = appExcel.Workbooks.Open("C:\test.xlsx")

wrkBook.Worksheets("Sheet 1").Activate

With wrkBook.ActiveSheet

.... etc.

answered Aug 6, 2015 at 12:57

Mark Butler

8951 gold badge5 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Over a year ago

No reference needed if you use late binding (especially when users can have different versions): Dim appExcel as Object Set appExcel=CreateObject("Excel.Application")

Collectives™ on Stack Overflow

Easy way of using excel vba in access vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related