Consume variables into Excel Macro called from Access

[polarisking]

I hope this question is "in bounds" for this forum.

I've taken an awkwardly written VBA-driven process in Access and re-written it in Excel. How awkward you ask? What took 35 min now takes 8 seconds.

The "old" Access process sets things like Run Date, File Paths, etc via an input box or by Public variables. My Excel Macro derives all these variables either via Input Boxes or Named Ranges from the Macro workbook..

Anyone know of a compact, elegant way for the Excel Macro Workbook to consume these values from Access?

Thanks, in advance.

 

[Marc L]

As Excel is far slower than Access …​

​

 

[Chihiro]

Full scope isn't clear. So can't really comment. But can't you use Access tables as source of the variables?

 

[polarisking]

Thanks for both replies. Here's what I ended up doing.

1. Started the module in Access
2. Grabbed the Public variable denoting File Path
3. Grabbed the File Date from the existing Input Box prompt
4. Started an Excel instance
   1. Opened my Macro Workbook
   2. My run page had been pulling in Path and Dates from a separate workbook, and I was able to update those cells in the workbook from the values to which I now had access
   3. Saved the workbook (with changes)
5. Terminated the Excel instance: set to Nothing and Quit
6. Ran the macro from the now-updated workbook within the Access instance

I'm sure there's a better way to do this, but this was just several lines of code and added no overhead to the total run time.