+ Reply to Thread
Results 1 to 5 of 5

Access Excel Automation

  1. #1
    Registered User
    Join Date
    11-10-2009
    Location
    Sugar Land, TX USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Access Excel Automation

    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")

  2. #2
    Registered User
    Join Date
    03-20-2008
    Location
    Buffalo, NY USA
    Posts
    43

    Re: Access Excel Automation

    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.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-10-2009
    Location
    Sugar Land, TX USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Access Excel Automation

    Thanks for the response. I will try your suggestion.

  4. #4
    Registered User
    Join Date
    03-05-2009
    Location
    Vail, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Access Excel Automation - create code procedure

    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.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-05-2009
    Location
    Vail, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Access Excel Automation

    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.
    Please Login or Register  to view this content.
    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.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1