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,424
    Provided Answers: 8
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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