Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Calling Access VBA functions from Excel

    Rightio I have a macro in Excel that creates a bunch of datatables depended on a year you enter, when this is called I want to call a macro in Access that checks whether the query of that specific year exists and if not create it.

    Now I can't do this with a Macro so I've used a VBA macro
    Klasgeld_Query(syear As String)

    BUT now here my question, I know Access Macros can be called with doCmd.RunMacro but this doesn't work with a vba macro.

    So is there any way to call a VBA macro from Excel?

    (I have thought about calling the function with a Access Macro and then calling the macro with Excel, but I don't know how to work the parameter into that?)

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    My suggestion: don't use macros.

    Through automation, you can "run" Access from Excel, or "run" Excel from Access, which ever works best for your application.

    When you say "create a bunch of datatables", I begin to suspect there may be issues with your table designs - a new set of data shouldn't require a new data table.

    tc

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    Hes some code that I use
    This is from MSaccess 97

    this code is inside excel module

    Code:
    Dim appAccess As New Access.Application
    Const conPath As String = "C:\event\EVENT REPORT97.MDB"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    With appAccess
        .OpenCurrentDatabase conPath
        .DoCmd.SetWarnings False
        .DoCmd.RunMacro "Update Data"
        .DoCmd.SetWarnings True
        .Quit
    End With
    
    Set appAccess = Nothing
    ...
    ...
    ....
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •