General discussion

  • Creator
    Topic
  • #2257456

    Bridging access and excel macros?

    Locked

    by davidson.dj ·

    I have a project in which I need to go back and forth between access and excel over and over (200+ times each time I run the project). Within each application, I run a macro, then go to the other and run a macro, then back to the former… and so on. This is the only way it can be done. How do I build/write a larger macro that can bridge both applications where I can combine both and just press the run button once?

All Comments

  • Author
    Replies
    • #3212864

      Reply To: Bridging access and excel macros?

      by maevinn ·

      In reply to Bridging access and excel macros?

      Have you tried linking the Excel spreadsheets into Access as tables and run everything from Access?

    • #3212830

      Reply To: Bridging access and excel macros?

      by davidson.dj ·

      In reply to Bridging access and excel macros?

      I don’t think that will work. What I am doing is using an access macro to manage and sort through a bunch of data. Then export it it excel. In excel, I am using matrices, calculus, and other higher level math that access can’t do via excel macros. I then sort through access using another access macro to pull different, similar data and export it to excel. I then use the results of the data manupulation and and math from the previous excel macro to do more equations to test the similarity of the results. I want to press one button so I dont have to do this over and over, is this possible? Thanks.

    • #3212805

      Reply To: Bridging access and excel macros?

      by maevinn ·

      In reply to Bridging access and excel macros?

      Ah–the data is in Access, the crunching is in Excel. Linking only works if it’s the other way around–the data in Excel, crunching (as such) done in Access.

      So, alternate option…Build the query against the database in Excel. Data | Get External Data | Create New Database Query. Connect to your Access database, and either use an existing query in Access, or rebuild it (I’d probably use the existing one, particularly if it does any formatting or concantonating). Save the Query, and next time, you can just open up Excel and run it again.

      If this is something you do all the time, add some parameters to the Access query allowing you to auto filter (ie, Between [Enter begin date] and [Enter end date] criteria). The Excel spreadsheet can then be opened, and populated.

    • #2485772

      object automation

      by bryanreyn ·

      In reply to Bridging access and excel macros?

      This may be too late to help, but.
      Have you tried automation?

      In Access, you create an instance of Excel, and control it thru automation. You can create new sheets, pump values, get values, set formatting, etc. The excel object model is available to you from within Access.

      Hope this helps.
      b

      • #2584272

        re:object automation

        by broncotedk ·

        In reply to object automation

        Quick question, do you know any place that’s a good resource for transferring Excel Macros to Access. I have code in Access to runs Excel Macros that format, but methinks its better if I write everything in Access.

    • #2521325

      access/excel macros

      by plynch ·

      In reply to Bridging access and excel macros?

      not a very useful reply but i have a very similar query – need to write some code that can run functions in access and excell simultaneously – and if possible link parameter values in access to cells in excell.

    • #2932197

      Reply To: Bridging access and excel macros?

      by heifler ·

      In reply to Bridging access and excel macros?

      There is a tool to control Excel macros and data export from MS Access. See http://www.AccessToExcel.com

Viewing 5 reply threads