converting early binding to late binding

[adhoustonj]

Hey hey,
Sup AWF. I was wondering if it was possible to modify this example access file to be late binding vs early binding.
I modified the sample file quite a bit to get everything that i need -- but now that I have a good solution in place, I would also like to change this file from early binding to late binding.

My question is - is it even possible? The way functions are called with 'Worksheet' as input, and functions declared as 'worksheet', 'sheet', etc.
I can probably put this in one sub/function to return what I need - but for learning purposes, my question would be is possible to keep the 95% of the code base/procedures/subs the same, and just modify binding syntax, like set xlApp = new excel.application vs set xlapp create.object("excel.application"), etc.

And i have tried! Spent the last half of my day trying to do this and for once, google was not much help. I'm a SQL guru with a minor in VBA, but everyone just calls me the DB guru because I am the only one at my org with >2000 access db's.


What I am trying to achieve:

1. user navigates to and selects a file via file dialog
2. user selects sheet out of all available sheets in excel workbook
3. once selected, sheet is loaded to temp table
4. from temp table - further processing

and the user would be selecting 5 files each day which are similar to departmental production schedules. Sometimes these are modified outside of a database/ERP/MRP system where they have last minute deviations.

I could roll out what I have, but i don't want to give them a version with early binding where IT wakes up in the middle of the bed instead of the left/right side, and rolls out an update that makes the excel library reference incompatible for whatever reason.

I'm battling the mixed column datatype as well, but I think I am over that speedbump with docmd.transferspreadsheet HEADERS = no, and parsing from there.

Selecting and Importing Excel File and Sheet for import into Access

This is a tip that has the code for selecting an Excel file and sheet for import into Microsoft Access. This includes the code required to browse for a file, and populate a ListBox with the sheets of the Excel Workbook, and the code to import an Excel spreadsheet.

www.codeproject.com

 

[MajP]

My question is - is it even possible? The way functions are called with 'Worksheet' as input, and functions declared as 'worksheet', 'sheet', etc.
I can probably put this in one sub/function to return what I need - but for learning purposes, my question would be is possible to keep the 95% of the code base/procedures/subs the same, and just modify binding syntax, like set xlApp = new excel.application vs set xlapp create.object("excel.application"), etc.

Yes. Basically 100% of your code base stays the same the only change is in declaration of the variables and instantiation of the object.
Maybe an example of where you are having problems converting from early to late would help.

The way I often do it is build the application and get it working. Then remove the references and hit compile. Then start fixing the places where it breaks.
Obviously wherever you had a specific object name you will replace with "Object"
dim wrkSheet as worksheet
to
dim wrkSheet as object

 

[MajP]

Maybe I do not understand your question, because it truly took me less than 30 seconds to convert that demo from early to late binding because it was pretty much already set up to convert to late binding. Simply dropped the references and use find and replace to replace "worksheet" with object. I even got rid to the file dialog, but the weird thing was it was really already set up for late binding.
I assume there is something more to the question that I am missing, because that could not take half a day and I am sure Mr. Google would provide guidance.
Is this what you are asking?

 

[Pat Hartman]

If you don't have any say in the version of Office that your users have installed, the best solution is to make sure that you develop one version behind. Then you can stick with early binding. Access will "promote" the bound version to a newer version but it will not "demote" it. So, if you develop using O2016 and everyone uses O2016 or newer, there won't be issues. The issue only arises if some random user has O2013.

Why? Early binding is more efficient since references are resolved at compile time rather than over and over and over again at run time, plus you get intellisense. So, if you can use early binding, there is no benefit to going with late binding. If you can't control the version situation, then you're stuck and have to use late binding.

 

[Josef P.]

Note: I usually use compiler constants to switch to late binding, as I want to use early binding during development.

Example:

#Const ExcelEarlyBinding = 1  '<-- or define in the project properties

#If ExcelEarlyBinding Then
   Dim m_OpenExcel As Excel.Application
   Dim m_OpenWorkbook As Excel.Workbook
#Else
   Dim m_OpenExcel As Object
   Dim m_OpenWorkbook As Object
#End If

[OT]
The NewImportUtilitiesModule module would benefit from a code review.
e.g. Add Option Explicit
or you can also think about this:

Public Sub DeleteTableSafe(name As String)
    On Error Resume Next
    DoCmd.DeleteObject acTable, name
    Exit Sub
Error:
    MsgBox Err.Description, vbInformation, "Error No: " & Err.Number
End Sub

What is the probability that the MsgBox will be called?

Something like this also "disturbs" my eye:

Public Function ExcelSheetsNameList(path As String) As String()
    OpenExcelWorkbook (path)
...

Round brackets that have nothing to do with the function. (Note the space before the opening bracket.)

 

[adhoustonj]

Wow @MajP . Well, I don't know what I was doing on Friday.
My first error was on the function below, that originally was 'As Worksheet', and I thought I had changed all dim xlapp to be object and setting as create.object("excel.application") object instead of excel.application. Maybe I missed one.


-- I know what I did wrong. In some of the functions being called like below - I changed some to string instead of object for some reason.

I just went back and tried to modify the original and it worked like a charm. Thanks everyone for the help.

Public Function GetExcelWorksheet(path As String, sheetName As String) As Worksheet
    OpenExcelWorkbook (path)
    Set GetExcelWorksheet = m_OpenWorkbook.Sheets(sheetName)
End Function

 

[adhoustonj]

If you don't have any say in the version of Office that your users have installed, the best solution is to make sure that you develop one version behind. Then you can stick with early binding. Access will "promote" the bound version to a newer version but it will not "demote" it. So, if you develop using O2016 and everyone uses O2016 or newer, there won't be issues. The issue only arises if some random user has O2013.

Why? Early binding is more efficient since references are resolved at compile time rather than over and over and over again at run time, plus you get intellisense. So, if you can use early binding, there is no benefit to going with late binding. If you can't control the version situation, then you're stuck and have to use late binding.

We do not unfortunately. Our IT has a lot of systems locked down and limit 'generic' workstations to be some version of office 2013, while all AD users are on semi-annual enterprise releases. I've found some workstations that havent been updated since 2009. Usually things are not too much of an issue with new revisions of production db's, but still.... Not the most ideal sea to be navigating!

 

[Pat Hartman]

Then you're stuck with late binding. I was only trying to point out that there is an advantage to late binding and so you shouldn't switch unless you need to. But as Maj and the others showed, it isn't all that hard to switch.

 

[adhoustonj]

Note: I usually use compiler constants to switch to late binding, as I want to use early binding during development.

Example:

#Const ExcelEarlyBinding = 1  '<-- or define in the project properties

#If ExcelEarlyBinding Then
   Dim m_OpenExcel As Excel.Application
   Dim m_OpenWorkbook As Excel.Workbook
#Else
   Dim m_OpenExcel As Object
   Dim m_OpenWorkbook As Object
#End If

[OT]
The NewImportUtilitiesModule module would benefit from a code review.
e.g. Add Option Explicit
or you can also think about this:

Public Sub DeleteTableSafe(name As String)
    On Error Resume Next
    DoCmd.DeleteObject acTable, name
    Exit Sub
Error:
    MsgBox Err.Description, vbInformation, "Error No: " & Err.Number
End Sub

What is the probability that the MsgBox will be called?

Something like this also "disturbs" my eye:

Public Function ExcelSheetsNameList(path As String) As String()
    OpenExcelWorkbook (path)
...

Round brackets that have nothing to do with the function. (Note the space before the opening bracket.)

This is great. I missed the intellisense once I switched to late binding..

Are you saying the path should just be called like below? This was my first time converting late to early binding. Mostly I have just converted api calls to be compliant with 32 & 64 bit machines. And I have many examples of late binding where we pick up an excel or powerpoint template and write recordsets or reports to, but this was the first I have used functions and passing objects/worksheets between. Grateful for you all. Thanks!

And also - how do you update/define the constraint between dev/prod db? Just a start up or username check, or table field? This piques my interest on App Titles - I might try and implement something based off of application path to determine whether prod/dev/stagining/testing, etc. Curious about that but I like it.

Public Function ExcelSheetsNameList(path As String) As String()
    OpenExcelWorkbook path
...

 

[adhoustonj]

Then you're stuck with late binding. I was only trying to point out that there is an advantage to late binding and so you shouldn't switch unless you need to. But as Maj and the others showed, it isn't all that hard to switch.

Thank you for the insight.

It does sound like there are more advantages to early binding as you pointed out, but yes in this case, I have no choice in late binding - with the advantage of being... It works!! Lol.

 

[Josef P.]

[OT: Procedure (param)]

Are you saying the path should just be called like below?

Yes

To compare:

Private sub Test()

   Dim TestString As String

   TestString = "abc"

   Debug.Print StrPtr(TestString)
   PrintStrPtr TestString
   Call PrintStrPtr(TestString)
   PrintStrPtr (TestString)

End Sub

Private Sub PrintStrPtr(ByRef StringToCheck As String)
   Debug.Print StrPtr(StringToCheck)
End Sub