Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Unanswered: Pass string from Excel to Access function

    I have an Excel worksheet that at one point generates a simple date string. (ex. "04252011") I have a seperate Access DB with a function that takes a date to update some records. Instead of having to take the date generated in Excel and type it into Access I would like to have the Excel string passed to the Access function. Here is the code I have in Excel VBA to activate the Access function, but can't figure out how to pass a string along with it.

    Sub Run_Access_Macro()
    Chan = DDEInitiate("MSACCESS", "Navigator")
    Application.ActivateMicrosoftApp xlMicrosoftAccess
    Application.DDEExecute Chan, "mcrUpdate_Compliance"
    Application.DDETerminate Chan
    End Sub

    Some of the issues is that the Access DB is always the same, but the Excel filename may be different. I thought of just having Access get a value from a cell in Excel, but can't figure out how to do that. Any help/ideas??

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You should use Automation (COM) instead of DDE. DDE is an ancient technology that probably won't be supported in the future (according to Microsoft).

    In Excel, open the VBA Editor (Alt+F11) and create a reference to Access (Tools --> References) Ex. Microsoft Access 11.0 Object Library for Office 2003.

    Now, let's suppose you have a function Half() in an Access database named ScratchPad.mdb, like this:
    Code:
    Public Function Half(ByVal Argument As Long) As Long
    
        Half = Argument \ 2
        
    End Function
    Here's how to call it from Excel:
    Code:
    Sub CallAccessFunction()
    
        Dim appAccess As Access.Application
        
        Set appAccess = New Access.Application
        With appAccess
            .OpenCurrentDatabase "C:\Documents and Settings\SinnDHo\My documents\Access\ScratchPad.mdb"
            .Visible = True ' Useful for debugging
            .Eval "Half(2)"
            .CloseCurrentDatabase
            .Quit
        End With
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    5

    Pass string from Excel to Access function

    Perfect!! Just what I needed. I have one issue. The Access DB will already be open when this is run. How would I specify the open DB to run the function?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can't, at least not easily, but it does not matter: an Access database (.mdb) can be open more than once simultaneously provided it is not open in Exclusive mode.
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    5
    Ok, so here is what I have come up with. I get no errors, but it doesn't actually run the Access function. What am I missing?

    This is the Excel VBA
    Code:
    Sub RunAccessMacro()
    
        Dim myYear, myMonth, myDay As String
        Dim appAccess As Access.Application
        Const ERR_APP_NOTRUNNING As Long = 429
        
        myYear = "2011"
        myMonth = "04"
        myDay = "12"
        
        On Error Resume Next
        
       Set appAccess = GetObject(, "Access.Application")
       
       If Err = ERR_APP_NOTRUNNING Then
          Set appAccess = New Access.Application
          appAccess.OpenCurrentDatabase "R:\SHARED\OPUSSuite\Navigator\Navigator Combined\Navigator.mdb"
       End If
    
       With appAccess
          .Visible = True
          .Eval "Issues_Update_Compliance(myYear, myMonth, myDay)"
       End With
    
       If Not appAccess.UserControl Then
          appAccess.Quit
          Set appAccess = Nothing
       End If
        
    End Sub
    This is the Access function VBA

    Code:
    Public Function Issues_Update_Compliance(ByVal myYear As String, ByVal myMonth As String, ByVal myDay As String) As String
       ...code  
       strPath = strPath + "\History\" + myYear + myMonth + myDay  "ComplianceHx.xls" 
       code...
    End Function

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are several issues with your code.

    1. The Function Issues_Update_Compliance() is bogus and the module that contains it will never compile. In the expression:
    Code:
    strPath = strPath + "\History\" + myYear + myMonth + myDay  "ComplianceHx.xls"
    an operator is missing between myDay and "ComplianceHx.xls" . It should be:
    Code:
    strPath = strPath + "\History\" + myYear + myMonth + myDay + "ComplianceHx.xls"
    (About that, notice that you should use the concatenation operator (&), not the addition operator (+) when dealing with strings or with variant of sub-type string.

    2. Though it can work, I don't like using GetObject() the way you do, specially because absolutely nothing can garantee that even if there is an instance of Access running when you call it, this instance will have the proper database open. Moreover, there can be more than one instance of Access running: which one will you get? Also this forces you to issue a "On Error Resume Next" statement that I tend to avoid like the plague and that masks any subsequent error, not just the one you're expecting to occur. This is precisecely what happens here: The error is obvious but you cannot receive an error notification:
    ...I get no errors, but it doesn't actually run the Access function. What am I missing?
    3. the line appAccess.Visible = True should be inside the If Err = ERR_APP_NOTRUNNING control structure:
    Code:
       If Err = ERR_APP_NOTRUNNING Then
          Set appAccess = New Access.Application
          appAccess.OpenCurrentDatabase "R:\SHARED\OPUSSuite\Navigator\Navigator Combined\Navigator.mdb"
          appAccess.Visible = True
       End If
    . Otherwise it will cause an error when you receive the instance of Access from the GetObject method (error that is masked by the On error Resume Next instruction, see above).

    4. It should not be a problem here but be aware that when you write:
    Code:
    Dim myYear, myMonth, myDay As String
    it means:
    Code:
    Dim myYear As Variant, myMonth As Variant, myDay As String
    Under certain circumstances this can cause subtle errors that can be difficult to identify, specially when you use + in place of & as the concatenation operator.

    5. In the line:
    Code:
          .Eval "Issues_Update_Compliance(myYear, myMonth, myDay)"
    myYear, myMonth and myDay are string litterals ("MyYear", "MyMonth", "MyDay"), not the values you assign to the corresponding variables. For the code to work properly this line should be:
    Code:
    .Eval ("Issues_Update_Compliance(" & myYear & ", " & myMonth & ", " & myDay & ")")
    Alltogether, here's a functional version on the code:
    Code:
    Sub RunAccessMacro()
    
        Dim myYear As String, myMonth As String, myDay As String
        Dim appAccess As Access.Application
        Const ERR_APP_NOTRUNNING As Long = 429
        
        myYear = "2011"
        myMonth = "04"
        myDay = "12"
        On Error Resume Next
        Set appAccess = GetObject(, "Access.Application")
        On Error GoTo 0
        If appAccess Is Nothing Then
            Set appAccess = New Access.Application
            appAccess.OpenCurrentDatabase "R:\SHARED\OPUSSuite\Navigator\Navigator Combined\Navigator.mdb"
            appAccess.Visible = True
        ElseIf appAccess.CurrentDb.Name <> "R:\SHARED\OPUSSuite\Navigator\Navigator Combined\Navigator.mdb" Then
            Set appAccess = New Access.Application
            appAccess.OpenCurrentDatabase "R:\SHARED\OPUSSuite\Navigator\Navigator Combined\Navigator.mdb"
            appAccess.Visible = True
        End If
        appAccess.Eval ("Issues_Update_Compliance(" & myYear & ", " & myMonth & ", " & myDay & ")")
        If Not appAccess.UserControl Then
            appAccess.Quit
            Set appAccess = Nothing
        End If
        
    End Sub
    And for the function Issues_Update_Compliance():
    Code:
    Public Function Issues_Update_Compliance(ByVal myYear As String, ByVal myMonth As String, ByVal myDay As String) As String
    
        Dim strpath As String
        
        strpath = strpath & "\History\" & myYear & myMonth & myDay & "ComplianceHx.xls"
        Stop
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    Apr 2011
    Posts
    5

    Thumbs up

    Ahhh, it's so beautiful!! You just saved me hours of banging my head on the desk. Thanks for the how AND the why. It's a big help for a novice.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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