Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Run Excel from Access

    I have an Excel app that has a button, that when clicked, will run some VBA code. I also have an Access app. I am trying to create a button on a form in Access that when clicked, will run the VBA code that exists in the Excel workbook. Can anyone clue me in on how to do this?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This seems to work

    Code:
    Private Sub cmdRunExcelCode_Click()
    On Error GoTo Err_cmdRunExcelCode_Click
        Dim XL As Object
        Dim XLOpen As Boolean
            
        XLOpen = False
    
        If OpenExcelOK(XL, "C:\Test\TestRunFrom Access.xls") Then
            XLOpen = True
            XL.Run ("RunFromAccess")
        End If
        
        XL.Quit
    Exit_cmdRunExcelCode_Click:
        Exit Sub
        
        
    Err_cmdRunExcelCode_Click:
        MsgBox Err.Description
        If XLOpen Then XL.Quit
        Resume Exit_cmdRunExcelCode_Click
        
    End Sub
    The function used above to open excel is

    Code:
    Function OpenExcelOK(ByRef oExcel As Object, Optional ByVal FileToOpen As String = "") As Boolean
        
        OpenExcelOK = False
        On Error Resume Next
        
        Set oExcel = GetObject(, "Excel.Application")
    
        If Err.Number <> 0 Then
            Err.Clear
            Set oExcel = CreateObject("Excel.application")
        End If
        
        If Err.Number <> 0 Then
            MsgBox "Could not open Excel!", vbCritical
            Exit Function
        End If
        
        If FileToOpen = "" Then
            oExcel.Workbooks.Add
        Else
            oExcel.Workbooks.Open (FileToOpen)
            If Err.Number <> 0 Then
                MsgBox "Could not open workbook '" & FileToOpen & "' !", vbCritical
                If oExcel.Workbooks.Count = 0 Then oExcel.Quit
                Exit Function
            End If
        End If
        
        
        On Error GoTo 0
        OpenExcelOK = True
    End Function
    The above uses later binding (as this is written in A2k3 and I sometime create MDE files on a PC running A2k0 and save compile errors as it cannot find the Excel 2k3 library)

    In the Excel file there is a CODE MODULE (not ThisWorkbook or a Sheet Module) with this code

    Code:
    Sub RunFromAccess()
        MsgBox "This is run in Excel form Access"
    End Sub

    HTH



    MTB

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Thanks a lot. I tried to run this but I get an 'ActiveX component can't create object' error

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I have tried to provoke this error but have failed.

    I seem to vaguely remember having this error once, but can’t remember where or when, and certainly not the cause or fix.

    I did find another bug but this is irrelevant if the code never reaches it, ie. you cannot close Excel if it is not open.(XL.Quit should be inside the if statement!).

    So, has anyone else a cause/solution?


    MTB

  5. #5
    Join Date
    Mar 2006
    Posts
    3
    Unfortunatley my memory on this is hazy, but here is some code from one of my databases that runs a code module in an Excel workbook called importXML and then exits Excel.

    Code:
    Function updateExcel()
    
    Dim objXL As Object, x
        On Error Resume Next
        Set objXL = CreateObject("Excel.Application")
        With objXL.Application
            .Workbooks.Open "S:\Planning\Policy\Preferred Options Consultation\XML Spreadsheet\XML Spreadsheet.xls"
            x = .Run("importXML")
            .Quit
        End With
        Set objXL = Nothing
        
    End Function

Posting Permissions

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