PDA

View Full Version : Passing Excel macro variable to access macro



xen32
03-31-2016, 04:27 AM
Hello,
I have some information in Excel that I want to pass to Access macro, I found some examples on the internet, but could not get any of them to work.

Here is the Excel code:


Sub DB_macro()


Dim A As Object
Dim xlVar As String
Dim myRng As Range, myC As Range


Set myRng = ThisWorkbook.Worksheets("List").Range("List[DB]")


For Each myC In myRng


xlVar = myC.Value2
Set A = CreateObject("Access.Application")
A.Visible = True
A.OpenCurrentDatabase "C:\DB.accdb"
A.DoCmd.RunMacro "Access_macro", xlVar
DoEvents
A.CloseCurrentDatabase
A.Quit
Set A = Nothing


Next myC


End Sub


Access function:

Function ChangeA(xlVar As String)


DBA = "DB_" & xlVar
Call ChangeB
DoEvents


End Function


Macro Access_macro I am calling has just this: ChangeA(xlVar)

But it does not work, I get error saying that data type is wrong for one of the arguments.

xen32
03-31-2016, 04:35 AM
Nevermind, I change A.DoCmd.RunMacro to A.Run to run function itself and it worked fine.