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.
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.