• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consume variables into Excel Macro called from Access

polarisking

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

Chihiro

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

polarisking

Member
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.
 
Top