Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Question Unanswered: Running Access Macro from Excel

    I have a model, running from a macro in Excel. The model cycles through hundreds of thousands of iterations for forecasting purposes. I am taking the output from the model after each iteration and dumping it into Access.

    I am experiencing two problems calling the Access macro (that imports the data and places it in the appropriate table) from Excel:

    1.) The database connection times out. The speed of the Excel macro is drastically reduced if I open Access after each iteration to dump data in. Therefore I have connected to Access in the beginning of my Excel macro and am only calling the Access macro after each iteration.

    2.) I call the Access macro in the following manner:

    In the beginning of the macro the following is established:
    Dim cn As New Access.Application
    cn.OpenCurrentDatabase ("C:\name.mdb")
    cn.Visible = False

    Then after each iteration:
    SendKeys "MacroName{ENTER}"
    cn.RunCommand acCmdRunMacro

    Then at the very end:
    cn.CloseCurrentDatabase
    cn.Quit acQuitSaveNone
    Set cn = Nothing

    The problem is with the "SENDKEYS" statement. Sometimes, about 1% of the time, the macro seems to skip over the line. The macro window will open in Access prompting me to enter the macro name and "ENTER".

    Any wisdom that could be imparted regarding these two issues would be greatly appreciated! Thanks for your help.

  2. #2
    Join Date
    Feb 2004
    Posts
    13

    Re: Running Access Macro from Excel

    Originally posted by eebuck
    I have a model, running from a macro in Excel. The model cycles through hundreds of thousands of iterations for forecasting purposes. I am taking the output from the model after each iteration and dumping it into Access.

    I am experiencing two problems calling the Access macro (that imports the data and places it in the appropriate table) from Excel:

    1.) The database connection times out. The speed of the Excel macro is drastically reduced if I open Access after each iteration to dump data in. Therefore I have connected to Access in the beginning of my Excel macro and am only calling the Access macro after each iteration.

    2.) I call the Access macro in the following manner:

    In the beginning of the macro the following is established:
    Dim cn As New Access.Application
    cn.OpenCurrentDatabase ("C:\name.mdb")
    cn.Visible = False

    Then after each iteration:
    SendKeys "MacroName{ENTER}"
    cn.RunCommand acCmdRunMacro

    Then at the very end:
    cn.CloseCurrentDatabase
    cn.Quit acQuitSaveNone
    Set cn = Nothing

    The problem is with the "SENDKEYS" statement. Sometimes, about 1% of the time, the macro seems to skip over the line. The macro window will open in Access prompting me to enter the macro name and "ENTER".

    Any wisdom that could be imparted regarding these two issues would be greatly appreciated! Thanks for your help.

Posting Permissions

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