Run access module from Excel VBA

Question

I am tryng to run this module on Access:

Public Sub Retriever_P(path)

DoCmd.TransferSpreadsheet acImport, 10, "Product_Details", path, True, ""

End Sub

From this VBA code in Excel

 Private Sub CommandButton210_Click()

Dim appAccess As Access.Application
Set appAccess = New Access.Application
Dim Target As String: Target = ThisWorkbook.Sheets("CODE").Cells(8, 4).Value
Dim path As String: path = ThisWorkbook.Sheets("CODE").Cells(5, 13).Value
appAccess.OpenCurrentDatabase Target
appAccess.Visible = True
appAccess.DoCmd.SetWarnings False
appAccess.UserControl = True
appAccess.DoCmd.OpenQuery "Clean Product_Details"
appAccess.Run "Retriever_P", path
appAccess.CloseCurrentDatabase

End Sub

But I get this Error 2517, "Microsoft Access cannot find the procedure "Retriever_P"

The Excel code is part of a bigger macro that creates a DB and then sends it to access. Any Ideas?

Thank you in advance!

I think, Run method takes in a Function, not a Sub. So try changing the Retriever_P to a function. Then try again. Also make sure the Function is placed in a standard module not behind a Form. — PaulFrancis
– PaulFrancis, Commented Apr 17, 2015 at 10:14
I tried but still same error! The Module is a converted Macro created with Access 2013. I Created the macro then converted it to VBA and edited, so it placed it in under Modules. — Jack
– Jack, Commented Apr 17, 2015 at 10:20
It's not an issue of sub vs function. I tried with sub it works. Try to change CommandButton210_Click to Private Sub CommandButton210_Click() Dim appAccess As Access.Application Set appAccess = CreateObject("Access.Application") appAccess.OpenCurrentDatabase ThisWorkbook.Sheets("CODE").Cells(5, 13).Value, False appAccess.Run "Retriever_P", "PATH..." Set appAccess = Nothing End Sub See the below link: msdn.microsoft.com/en-us/library/office/ff193559.aspx — Guilhem Hoffmann
– Guilhem Hoffmann, Commented Apr 17, 2015 at 10:24

Jack · Accepted Answer · 2015-04-17 10:45:22Z

1

I have found the solution: the problem was that the sub name was the same as the module name. I didn't know it had to be different, but I hope someone will find this useful! Thanks for all the answers guys!

answered Apr 17, 2015 at 10:45

Jack

5076 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mrduncle1 · Accepted Answer · 2017-09-07 04:53:02Z

I came across another issue while running a simple test attempting to run an Access VBA function in an empty database (except for the VBA code) from an Excel procedure. Without any other objects (tables/forms/etc), the Access VBA function was unable to be found. Once I put in a nominal table, the call to the Access VBA function from Excel VBA was successful. Not a usual situation to have an empty database but as I said I was just running a trial to see if the method worked.

Collectives™ on Stack Overflow

Run access module from Excel VBA

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related