Access Excel Automation

**JesseH** · 11-10-2009, 02:04 PM

I have created an excel workbook using Access. Everything works fine. The problem is that the references are not passed from Access to Excel (a known MS Problem). I get undefined data type. I know that it is the references because I can save the workbook and open it manually. When I do this, it works. I have tried late binding, but I can not get the snippet that follows to late bind. If I could stop the Excel from compiling the VBA, that would work also.

Question: How to you get the code below to late bind?

Thanks

Dim cm As VBIDE.CodeModule
Dim x As Long
Set cm = sht.Parent.VBProject.VBComponents("ThisWorkBook").CodeModule
x = cm.CreateEventProc("Open", "Workbook")

**bhill** · 11-11-2009, 08:41 AM

If you are using Access to create a new Excel workbook, I'd just use the below automation code. You can control pretty much every aspect of Excel from Access (you'll need to research all of the methods, obviously). Here's the basics - open a new workbook, add a value to a cell and display the workbook for the user to see. It does get a little more complex when you want to add queried data, subtotals, colors or charts, but it's all possible. You'll need to make a reference to MS Excel in your databases references for this to work.

Code:

    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht1 As Excel.Worksheet

    Set objXL = Excel.Application
    Set objWkb = objXL.Workbooks.Add
    Set objSht1 = objWkb.Worksheets("Sheet1")
    objWkb.Activate

    With objSht1
        Cells(1, 1) = "Hello There"
    End With

    objXL.Visible = True

    'Clean up
    Set objSht1 = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing

**JesseH** · 11-11-2009, 08:49 AM

Thanks for the response. I will try your suggestion.

**RxMiller** · 12-10-2009, 08:09 PM

This was interesting. Thought this information would be useful for others. My code attachment includes some further instructions in addition to code due to the complexity. Not for those new to VBA programmin, but it does include some "must know" steps.

When writing code in VBA that reads or modifies other VBA projects, modules, or procedures (i.e. code that writes code). This is called extensibility because it extends the editor using VBA code to create new VBA code.
Use for these features includes writing custom procedures that create, change, or delete VBA modules and code procedures.

To use this code in your projects, change two settings.

•First, set a new reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In the Reference dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. Failure to set this reference to the type library creates a User-defined type not defined compiler error.

•Next, enable programmatic access to the VBA Project. In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.

The VBA Project that will be change with these procedures must be unlocked. There is no programmatic way to unlock a VBA project (other than using SendKeys). If the project is locked, you must manually unlock. Otherwise, the procedures will not work.

Referencing VBIDE Objects

The code below illustrate various ways to reference Extensibility objects.

Code:

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBAEditor = Application.VBE
'''''''''''''''''''''''''''''''''''''''''''
Set VBProj = VBAEditor.ActiveVBProject
' or
Set VBProj = Application.Workbooks("Book1.xls").VBProject
'''''''''''''''''''''''''''''''''''''''''''
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module1")
' or
Set VBComp = VBProj.VBComponents("Module1")
'''''''''''''''''''''''''''''''''''''''''''
Set CodeMod = ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
' or
Set CodeMod = VBComp.CodeModule



In the code and descriptions on this page, the term Procedure means a Sub, Function, Property Get, Property Let, or Property Set procedure. The Extensibility library defines four procedures types, identified by the following constants. The numeric value of each constant is shown within parentheses. 

•vbext_pk_Get (3). A Property Get procedure.
•vbext_pk_Let (1). A Property Let procedure.
•vbext_pk_Set (2). A Property Set procedure.
•vbext_pk_Proc (0). A Sub or Function procedure.

The rest of this page describes various procedures that modify the various objects of a VBProject.

Adding A Module To A Project

This code adds new code module named NewModule to the VBProject of the active workbook. The type of VBComponent is specified by the value of the parameter passed to the Add method. 

    Sub AddModuleToProject()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
        VBComp.Name = "NewModule"
    End Sub

Adding A Procedure To A Module

This code adds a simple "Hello World" procedure named SayHello to the end of the module named Module1.

    Sub AddProcedureToModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub SayHello()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
    
    End Sub

**RxMiller** · 12-10-2009, 08:16 PM

In addition to having VBA create code modules, it might be easier to have a template somewhere on the network, copy the code module (with some minor custom information in code).

How to Copy A Module From One Project To Another
( I am not going to post the code on how to create a new Event Procedure, but with this information poeple can search MSDN)

Note: There is no direct way to copy a module from one project to another. To accomplish this task, export the module from the Source VBProject and then import that file into the Destination VBProject.

Code:

Function CopyModule(ModuleName As String, _
    FromVBProject As VBIDE.VBProject, _
    ToVBProject As VBIDE.VBProject, _
    OverwriteExisting As Boolean) As Boolean

ModuleName is the name of the module you want to copy from one project to another.

FromVBProject is the VBProject that contains the module to be copied. This is the source VBProject.

ToVBProject is the VBProject in to which the module is to be copied. This is the destination VBProject.

OverwriteExisting indicates what to do if ModuleName already exists in the ToVBProject. If this is True the existing VBComponent will be removed from the ToVBProject. If this is False and the VBComponent already exists, the function does nothing and returns False.

The function returns True if successful or False is an error occurs. The function will return False if any of the following are true:

•FromVBProject is nothing.
•ToVBProject is nothing.
•ModuleName is blank.
•FromVBProject is locked.
•ToVBProject is locked.
•ModuleName does not exist in FromVBProject.
•ModuleName exists in ToVBProject and OverwriteExisting is False.

Code:

Function CopyModule(ModuleName As String, _
    FromVBProject As VBIDE.VBProject, _
    ToVBProject As VBIDE.VBProject, _
    OverwriteExisting As Boolean) As Boolean
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' CopyModule
    ' This function copies a module from one VBProject to
    ' another. It returns True if successful or  False
    ' if an error occurs.
    '
    ' Parameters:
    ' --------------------------------
    ' FromVBProject         The VBProject that contains the module
    '                       to be copied.
    '
    ' ToVBProject           The VBProject into which the module is
    '                       to be copied.
    '
    ' ModuleName            The name of the module to copy.
    '
    ' OverwriteExisting     If True, the VBComponent named ModuleName
    '                       in ToVBProject will be removed before
    '                       importing the module. If False and
    '                       a VBComponent named ModuleName exists
    '                       in ToVBProject, the code will return
    '                       False.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim VBComp As VBIDE.VBComponent
    Dim FName As String
    Dim CompName As String
    Dim S As String
    Dim SlashPos As Long
    Dim ExtPos As Long
    Dim TempVBComp As VBIDE.VBComponent
    
    '''''''''''''''''''''''''''''''''''''''''''''
    ' Do some housekeeping validation.
    '''''''''''''''''''''''''''''''''''''''''''''
    If FromVBProject Is Nothing Then
        CopyModule = False
        Exit Function
    End If
    
    If Trim(ModuleName) = vbNullString Then
        CopyModule = False
        Exit Function
    End If
    
    If ToVBProject Is Nothing Then
        CopyModule = False
        Exit Function
    End If
    
    If FromVBProject.Protection = vbext_pp_locked Then
        CopyModule = False
        Exit Function
    End If
    
    If ToVBProject.Protection = vbext_pp_locked Then
        CopyModule = False
        Exit Function
    End If
    
    On Error Resume Next
    Set VBComp = FromVBProject.VBComponents(ModuleName)
    If Err.Number <> 0 Then
        CopyModule = False
        Exit Function
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' FName is the name of the temporary file to be
    ' used in the Export/Import code.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    FName = Environ("Temp") & "\" & ModuleName & ".bas"
    If OverwriteExisting = True Then
        ''''''''''''''''''''''''''''''''''''''
        ' If OverwriteExisting is True, Kill
        ' the existing temp file and remove
        ' the existing VBComponent from the
        ' ToVBProject.
        ''''''''''''''''''''''''''''''''''''''
        If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
            Err.Clear
            Kill FName
            If Err.Number <> 0 Then
                CopyModule = False
                Exit Function
            End If
        End If
        With ToVBProject.VBComponents
            .Remove .Item(ModuleName)
        End With
    Else
        '''''''''''''''''''''''''''''''''''''''''
        ' OverwriteExisting is False. If there is
        ' already a VBComponent named ModuleName,
        ' exit with a return code of False.
        ''''''''''''''''''''''''''''''''''''''''''
        Err.Clear
        Set VBComp = ToVBProject.VBComponents(ModuleName)
        If Err.Number <> 0 Then
            If Err.Number = 9 Then
                ' module doesn't exist. ignore error.
            Else
                ' other error. get out with return value of False
                CopyModule = False
                Exit Function
            End If
        End If
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Do the Export and Import operation using FName
    ' and then Kill FName.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    FromVBProject.VBComponents(ModuleName).Export Filename:=FName
    
    '''''''''''''''''''''''''''''''''''''
    ' Extract the module name from the
    ' export file name.
    '''''''''''''''''''''''''''''''''''''
    SlashPos = InStrRev(FName, "\")
    ExtPos = InStrRev(FName, ".")
    CompName = Mid(FName, SlashPos + 1, ExtPos - SlashPos - 1)
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Document modules (SheetX and ThisWorkbook)
    ' cannot be removed. So, if we are working with
    ' a document object, delete all code in that
    ' component and add the lines of FName
    ' back in to the module.
    ''''''''''''''''''''''''''''''''''''''''''''''
    Set VBComp = Nothing
    Set VBComp = ToVBProject.VBComponents(CompName)
    
    If VBComp Is Nothing Then
        ToVBProject.VBComponents.Import Filename:=FName
    Else
        If VBComp.Type = vbext_ct_Document Then
            ' VBComp is destination module
            Set TempVBComp = ToVBProject.VBComponents.Import(FName)
            ' TempVBComp is source module
            With VBComp.CodeModule
                .DeleteLines 1, .CountOfLines
                S = TempVBComp.CodeModule.Lines(1, TempVBComp.CodeModule.CountOfLines)
                .InsertLines 1, S
            End With
            On Error GoTo 0
            ToVBProject.VBComponents.Remove TempVBComp
        End If
    End If
    Kill FName
    CopyModule = True
End Function