Late Binding: What they don't tell you!

Introduction

In Access VBA Variable = Maximum Access table value I introduced one of my projects.  It uses MS-Access to store many months of data.  Each month I receive two MS-Excel files, link them to tables in the Access database, append one to the table that includes all monthly data, do a little more processing.  As a final step, another workbook is opened and queries linking back to the Access DB are updated.

The Challenge

Because these are Excel files produced by another department, and I have no control over them, I must do some clean-up before linking them.

• One has apostrophes in the worksheet names.  This causes Access to crash the attempt to link them.
• There are empty rows.  This causes the upload process to try to add empty rows to a database table that requires a key field found in the input.

Solution So Far

Prior to this I created a workbook with a couple of macros to resolve this problem.  One is an adaptation of Ron deBruin's VBA to reset the UsedRange of a worksheet.  The other simply replaces apostrophes with spaces, then double spaces with single spaces.

Solution: General Description

One objective is to concentrate VBA in one place, either in the MS-Access DB or the Excel "toolkit". This improves maintainability. Whether to concentrate the VBA Code in Excel or Access, the solution was going to require executing code from one environment and affecting the other.  That means binding with the non-native object.

 I decided that since I had more code already in the MS-Access DB it made more sense to execute it all there.  This is an application of the Alice's Restaurant Dumping The Garbage Rationale.

Since I prefer Late Binding for it's advantages in migration and file size, this is batch processing requiring only one round of instantiation of each object (as opposed to re-instantiating based on user inputs), and I had experience in Late and Early Binding,  I decided Late Binding was the way to go. 

Wrong Paths

Copy, Paste, Modify

The first attempt was simply to bind the workbooks to be manipulated, then clean them up using code in MS-Access. 

The binding worked fine but the code was too complex to work through the changes: Every Excel-native object needed to be redefined as an object. 

Modifying the Worksheet Name Clean-up procedure was simple.  This is what I started with in Excel

Sub CleanUpWorksheets()

    Const BillingFileNameStart As String = "201409 Query"

    Const RevenueFileNameStart As String = "Monthly Revenue Report"

    Dim wkbk As Workbook

    Dim wksht As Worksheet

    

    For Each wkbk In Workbooks

'        MsgBox wkbk.Name

'        If Left(wkbk.Name, Len(BillingFileNameStart)) = BillingFileNameStart _

                Or Left(wkbk.Name, Len(RevenueFileNameStart)) = RevenueFileNameStart _

                Or Left(Replace(wkbk.Name, "_", " "), Len(RevenueFileNameStart)) = RevenueFileNameStart Then

            For Each wksht In wkbk.Sheets

'                MsgBox wkbk.Name & Chr(10) & wksht.Name

                wksht.Name = Replace(Replace(wksht.Name, "'", " "), "  ", " ")

                procClearExcessRowsAndColumns wb:=wkbk

            Next wksht

            wkbk.Save

'        End If

    Next wkbk

End Sub

The result, in Access (and with a bit of clean-up)

Sub CleanWorksheetNames(wkbk As Object) ' Passed the workbook instead of running through all workbooks

    Dim oSheet As Worksheet

    For Each oSheet In wkbk.Sheets

        wksht.Name = Replace(Replace(wksht.Name, "'", " "), "  ", " ")

'        procClearExcessRowsAndColumns wb:=wkbk ' Runs sub mentioned below

    Next wksht

End Sub

Updating the procedure for cleaning up the UsedRange was a bit more of a challenge.  Here is the code that I modified slightly to run in Excel.  Each object would need to be re-declared and Set.

I decided the risk-effort-reward was way less than 0.

Write New Proc for Trimming Worksheets

After deciding the risk-effort-reward for modifying the code above wasn't right for me I thought I considered re-writing it from scratch.  While I was comfortable with an approach that would work with what I needed for this project, what I really wanted was an all-purpose procedure, one I could use with all worksheets.

Thinking it through a little more I realized that non-range objects attached to the worksheets, e.g. graphs and inserted shapes/pictures, affected the UsedRange, too.

Late-Binding the Toolkit, too

I had been successful, in the MS-Access VBA, to late-bind Excel and the input workbooks, then update the workbook using a called Sub in Access .  I proved this to myself by running the following

Sub UpdateExcelWorkBookFromAccess()
    Const strFileDirectory As String = "C:\TestFiles\"
    Const strTargetFile As String = "Binding File 01.xlsx"
    Dim appExcel As Object
        Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True
    Dim wkbks As Object
        Set wkbks = appExcel.workbooks
    Dim wkbk As Object
    Set wkbk = wkbks.Open(strFileDirectory & strTargetFile)
    Call PostUpdate(wkbk.Worksheets("Sheet1"))
    wkbk.Close  ' Put Breakpoint here to see the changes made to the target workbook
    appExcel.Quit
End Sub

Sub PostUpdate(wksht As Object)

    wksht.Range("A1").Value = "Updated from Access"

End Sub

However, when I tried binding the Excel-based toolkit, the workbook with the macros and separate from the input files, I would get an error that either the macro could not be found or that the permissions were set to prevent running macros. 

For the next step I wanted to open the data (input) file and the Excel file with the macros, what I call a toolkit.  So I created Binding ToolKit 01.xlsm and added a module for a macro to use instead of  PostUpdate().

Sub UpdateWksheet(wb As Workbook)

    wb.Worksheets("Sheet1").Range("A1").Value = "Updated from BindingToolKit 01.Module1.UpdateWksheet"

End Sub

When I tried doing the same thing from Excel, I got the same error.  Searching the web and all of my print resources I could find no mention of this.  Early binding did not have this problem.

Sub UpdateExcelWorkBookFromOneExcelAppObject_Fail()
    Const strFileDirectory As String = "C:\TestFiles\"
    Const strTargetFile As String = "Binding File 01.xlsx"
    Const strToolKitFile1 As String = "Binding ToolKit 01.xlsm"
    Const MacroName As String = "UpdateWksheet"

    Dim appExcel As Object
        Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True
    Dim wkbks As Object
        Set wkbks = appExcel.workbooks
    Dim wkbkToolkit As Object
        Set wkbkToolkit = wkbks.Open(strFileDirectory & strToolKitFile1)
    Dim wkbkData As Object
        Set wkbkData = wkbks.Open(strFileDirectory & strTargetFile)
    appExcel.Run "UpdateWksheet", wkbkData
    wkbkData.Close  ' Put Breakpoint here to see the changes made to the target workbook
    wkbkToolkit.Close
    appExcel.Quit
End Sub

Solution: Two Late-Bound Instances

After some thought I decided to try Late-Binding two instances of the Excel Application, One instance has the input file, the other the file with the macro.

Sub UpdateExcelWorkBookFromTwoExcelAppObjects() ' Intent is to work with 1 Excel object, and choosing which Sub to run

    Const strFileDirectory As String = "C:\TestFiles\"

    Const strTargetFile As String = "Binding File 01.xlsx"

    Const strToolKitFile1 As String = "Binding ToolKit 01.xlsm"

    Const MacroName As String = "UpdateWksheet"

    

    Dim oExcelAppTarget As Object

        Set oExcelAppTarget = CreateObject("Excel.Application")

        oExcelAppTarget.Visible = True

    Dim oTgtWorkbook As Object

        Set oTgtWorkbook = oExcelAppTarget.workbooks.Open(strFileDirectory & strTargetFile)

    Dim oExcelAppTK As Object

        Set oExcelAppTK = CreateObject("Excel.Application")

        oExcelAppTK.Visible = True

    Dim oExcelTK1 As Object

        Set oExcelTK1 = oExcelAppTK.workbooks.Open(strFileDirectory & strToolKitFile1)

    oExcelAppTK.Run MacroName, oTgtWorkbook  '

    oExcelAppTarget.Quit
    oExcelAppTarget.Quit ' Put Breakpoint here to see the changes made to the target workbook

    oExcelAppTK.Quit

End Sub

This worked!

Next step: Two Toolkits

The next attempt was to add a second toolkit to the instance for the toolkit.  I did not want to have a third instance of Excel.

To add some complexity I included a version of UpdateWksheet in both and a second module in the second toolkit, again with the same UpdateWksheet   Subroutine.  The differences between the three was which cell would be updated and a change to the message, making it specific to the workbook and module.

The problem I ran into was specifying which Toolkit to use.  What I discovered was that the workbook with the macro I wanted to run needed to be the active workbook.

Sub UpdateExcelWkbkusingSecondExcelInstanceWithTwoToolkits() ' Intent is to work with 1 Excel object, and choosing which Sub to run

    Const strFileDirectory As String = "C:\TestFiles\"

    Const strTargetFile As String = "Binding File 01.xlsx"

    Const strToolKitFile1 As String = "Binding ToolKit 01.xlsm"

    Const strToolKitFile2 As String = "Binding ToolKit 02.xlsm"

    Const MacroName As String = "UpdateWksheet"

    

    Dim oExcelAppTarget As Object

        Set oExcelAppTarget = CreateObject("Excel.Application")

        oExcelAppTarget.Visible = True

    Dim oTgtWorkbook As Object

        Set oTgtWorkbook = oExcelAppTarget.workbooks.Open(strFileDirectory & strTargetFile)

    Dim oExcelAppTK As Object

        Set oExcelAppTK = CreateObject("Excel.Application")

        oExcelAppTK.Visible = True

    Dim oExcelTK1 As Object

        Set oExcelTK1 = oExcelAppTK.workbooks.Open(strFileDirectory & strToolKitFile1)

    Dim oExcelTK2 As Object

        Set oExcelTK2 = oExcelAppTK.workbooks.Open(strFileDirectory & strToolKitFile2)

    oExcelTK1.Activate ' Workbook activated before Run statement

    oExcelAppTK.Run MacroName, oTgtWorkbook  '

    

    oExcelTK2.Activate ' Workbook activated before Run statement

    oExcelAppTK.Run "Module1" & "." & MacroName, oTgtWorkbook   '

    oExcelAppTK.Run "Module2" & "." & MacroName, oTgtWorkbook   '

    oExcelApp.Quit ' Put Breakpoint here to see the changes made to the target workbook

End Sub

Earlier Problem Solved

Taking the lesson learned about activating the workbook before the Run statement, I realized I could now have one instance of Excel.  When I looked back at the code I realized the problem was I was opening the toolkit first, then the inpuit file.  That meant the input file was active when I needed the toolkit to be active.

Sub UpdateExcelWkbkUsingOneExcelInstanceWithTwoToolkits() ' Intent is to work with 1 Excel object, and choosing which Sub to run

    Const strFileDirectory As String = "C:\TestFiles\"

    Const strTargetFile As String = "Binding File 01.xlsx"

    Const strToolKitFile1 As String = "Binding ToolKit 01.xlsm"

    Const strToolKitFile2 As String = "Binding ToolKit 02.xlsm"

    Const MacroName As String = "UpdateWksheet"

    Dim oExcelApp As Object

        Set oExcelApp = CreateObject("Excel.Application")

        oExcelApp.Visible = True

    Dim oTgtWorkbook As Object

        Set oTgtWorkbook = oExcelApp.workbooks.Open(strFileDirectory & strTargetFile)

    Dim oExcelTK1 As Object

        Set oExcelTK1 = oExcelApp.workbooks.Open(strFileDirectory & strToolKitFile1)

    Dim oExcelTK2 As Object

        Set oExcelTK2 = oExcelApp.workbooks.Open(strFileDirectory & strToolKitFile2)

    oExcelTK1.Activate

    oExcelApp.Run MacroName, oTgtWorkbook  '

    oExcelTK2.Activate

    oExcelApp.Run "Module1" & "." & MacroName, oTgtWorkbook   '

    oExcelApp.Run "Module2" & "." & MacroName, oTgtWorkbook   '

    oExcelApp.Quit ' Put Breakpoint here to see the changes made to the target workbook

End Sub