none
Cant run an Access module from Excel RRS feed

  • Question

  • Hi

    I have a code that does what it should, but dont run the wanted module in Access.

    Sub Knapp1_Klicka()
    'RUN MS ACCESS MODULE FROM EXCEL:
          Dim appAccess As Object
          Set appAccess = CreateObject("Access.Application")
          appAccess.Visible = False
          appAccess.OpenCurrentDatabase ("C:\databaser\AXAHjälpDatabas.accdb")
          DoCmd.OpenModule "AXAKartonger"
         appAccess.Quit
         Set appAccess = Nothing
    End Sub
    

    Getting error "Object required (error 424):

    The name of the Module is "ExporteraAXAKartonger"
    The name of the only Function i the module is "AXAKartonger()"

    DoCmd.OpenModule "AXAKartonger"


    The module in Access that Excel dont run is, as below:

    Option Compare Database
    Option Explicit
    
    '------------------------------------------------------------
    ' AXAKartonger
    '------------------------------------------------------------
    Function AXAKartonger()
    On Error GoTo AXAKartonger_Err
    
        DoCmd.OutputTo acOutputReport, "AXAHurLängeRäckerKartonger", "Excel97-Excel2003Workbook(*.xls)", "C:\databaser\AXAHurLängeRäckerKartonger.xls", False, "", , acExportQualityScreen
    
    AXAKartonger_Exit:
        Exit Function
    AXAKartonger_Err:

    What am I doing wrong?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 18, 2016 11:29 AM

Answers

  • How about

    Sub Knapp1_Klicka()
    'RUN MS ACCESS MODULE FROM EXCEL:
          Dim appAccess As Object
          Set appAccess = CreateObject("Access.Application")
          appAccess.Visible = False
          appAccess.OpenCurrentDatabase ("C:\databaser\AXAHjälpDatabas.accdb")
          appAccess.Run "AXAKartonger"
         appAccess.Quit
         Set appAccess = Nothing
    End Sub


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Sunday, December 18, 2016 12:15 PM

All replies

  • How about

    Sub Knapp1_Klicka()
    'RUN MS ACCESS MODULE FROM EXCEL:
          Dim appAccess As Object
          Set appAccess = CreateObject("Access.Application")
          appAccess.Visible = False
          appAccess.OpenCurrentDatabase ("C:\databaser\AXAHjälpDatabas.accdb")
          appAccess.Run "AXAKartonger"
         appAccess.Quit
         Set appAccess = Nothing
    End Sub


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Sunday, December 18, 2016 12:15 PM
  • On a side note, I'd urge you to implement proper error handling to render the hidden Access instance visible should a fatal error occur to avoid orphan instances running without the user being aware of it.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Sunday, December 18, 2016 12:17 PM
  • Thanks Daniel

    code works fine.

    Error handling included.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 18, 2016 1:15 PM
  • How about

    Sub Knapp1_Klicka()
    'RUN MS ACCESS MODULE FROM EXCEL:
          Dim appAccess As Object
          Set appAccess = CreateObject("Access.Application")
          appAccess.OpenCurrentDatabase ("C:\databaser\AXAHjälpDatabas.accdb")
          appAccess.Visible = False
          appAccess.Run "AXAKartonger"
         appAccess.Quit
         Set appAccess = Nothing
    End Sub

    Hi Peter and Daniel,

    It gives some better result if you first open the database, and then set the Visible property to False.

    An other way to work, is to use:

            Run "C:\databaser\AXAHjälpDatabas.AXAKartonger"

    This works when the extension of the database is "mde" or "accde". For this purpose it in not necessary to convert to accde, but just a rename is sufficient.

    Imb.

     

    Sunday, December 18, 2016 2:21 PM
  • Hi Imb

    Thanks for observing the "visible line"

    I am not sure on how, or where, to use your line Run "C:\databaser\AXAHjälpDatabas.AXAKartonger". What can be excluded by using that line?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, December 19, 2016 7:17 AM
  • I am not sure on how, or where, to use your line Run "C:\databaser\AXAHjälpDatabas.AXAKartonger". What can be excluded by using that line?

    Hi Peter,

    If you just copy your database "C:\databaser\AXAHjälpDatabas.accdb" to "C:\databaser\AXAHjälpDatabas.accde", then you can use:

        Sub Knapp1_Klicka()
            Run "C:\databaser\AXAHjälpDatabas.AXAKartonger"
        End Sub

    This can easily be automated to any kind of reference using late binding.

    Imb.

    Monday, December 19, 2016 9:44 AM
  • Hi Imb

    Sorry but I get runtime error 1004

        Sub Knapp1_Klicka()
            Run "C:\databaser\AXAHjälpDatabas.AXAKartonger"
        End Sub

    And I have saved the accdb as accde


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, December 19, 2016 4:01 PM
  •     Sub Knapp1_Klicka()
            Run "C:\databaser\AXAHjälpDatabas.AXAKartonger"
        End Sub

    Hi Peter,

    I don't know what error 1004 is. I never use those numbers.

    Is AXAKartonger a Sub in a standard module? It should.

    I only use Access2003, and there it works for mde-databases. Perhaps I can test next Thursday on a computer with A2010.

    Imb.

    Monday, December 19, 2016 4:16 PM
  • Hi Imb

    Thanks for your willingness to help!

    Error 1004 says "Can not run the macro "C:\databaser\AXAHjälpDatabas.AXAKatonger" The macro may not be available in this workbook or all macros are disabled"

    - Macros are not disabled.
    - Its a Sub in a standard module.

    Will the "Run" close the database after running the module?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Tuesday, December 20, 2016 6:12 AM
  • Error 1004 says "Can not run the macro "C:\databaser\AXAHjälpDatabas.AXAKatonger" The macro may not be available in this workbook or all macros are disabled"

    Hi Peter,

    Perhaps it is a problem calling from Excel.

    For curiousity, did you try this calling from Access, and did that work?

    Imb.

    Tuesday, December 20, 2016 8:18 AM
  • Hi Imb

    Calling from another Access database works without errors :-)

    But the database opened with  - Run "C:\databaser\AXAHjälpDatabas.AXAKartonger" is opened and locked after the call.

    I add I line in "AXAKartonger" it closes the called database, but also close the calling database.:

    DoCmd.RunCommand acCmdCloseDatabase


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, December 20, 2016 12:18 PM
  • RUN MS ACCESS MACRO FROM EXCEL:

    Sub AccessTest1()
    
          Dim A As Object
    
          Set A = CreateObject("Access.Application")
    
          A.Visible = False
    
          A.OpenCurrentDatabase ("C:\your_path_here\Northwind.mdb")
    
          A.Application.Run "ExportToExcelTest"
    
    End Sub

     

    If you need to run the code from a Macro, it needs to be a Public Function (rather than Sub), and it needs to be in a standard module (not a Form, Report or Class module).


    MY BOOK

    Monday, January 16, 2017 5:53 PM