Library Reference Version Differences

[Klatuu]

At the moment, we are in a state of change where some users are still on
Office 2000 and others have been upgraded to 2003. All we be upgraded within
the next couple of months. I have been upgraded, and herein lies the problem.

We do a lot of communication between Access and Excel. Because the Excel
data is sloppy (kindest word I can think of), Linking the spreadsheets as
tables or using TransferSpreadsheet often brings in incomplete or incorrect
data. For that reason, I have been programmatically opening and reading data
directly from the spreadsheets (slow but accurate). Our application is still
in 2000 format. I am working in 2003, but using the 2000 format in my Access
application.

I had been using early binding. So, when I ported a new form and a new
module to the production mdb, the first user who tried to use it got an
error. We had to delete the mdb and get the back up.

What appears to have happened is that when the app opened, it could not find
the 2003 references, so it destroyed them, but could not find the correct
references. We found that the only way to get it to work was for someone
with 2000 to import my objects into the production mdb. It worked okay.

So, I decided to use late binding, thinking that because references are not
established until runtime, it would take of of the problem. It did not.

Now, with all that having been said, is there a preferred technique that
will allow me to continue working in 2003 (using 2000 format) and port my
objects into 2000 and avoid the library reference problems?

 

[Rick Brandt]

At the moment, we are in a state of change where some users are still
on Office 2000 and others have been upgraded to 2003. All we be
upgraded within the next couple of months. I have been upgraded, and
herein lies the problem. [snip]
So, I decided to use late binding, thinking that because references
are not established until runtime, it would take of of the problem.
It did not.

[snip]

I cannot think of a reason why late binding would not have fixed your
problem. Did you change your code AND remove the reference to Excel or just
change the code? You have to do both.

 

[Klatuu]

Here is what I have now. This was the one that suprised me:

Dim xlApp As Object ' Excel Object
Dim xlBook As Object ' Workbook Object

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select

At the moment, we are in a state of change where some users are still
on Office 2000 and others have been upgraded to 2003. All we be
upgraded within the next couple of months. I have been upgraded, and
herein lies the problem. [snip]
So, I decided to use late binding, thinking that because references
are not established until runtime, it would take of of the problem.
It did not.

[snip]

I cannot think of a reason why late binding would not have fixed your
problem. Did you change your code AND remove the reference to Excel or just
change the code? You have to do both.

 

[Rick Brandt]

Here is what I have now. This was the one that suprised me:

Dim xlApp As Object ' Excel Object
Dim xlBook As Object ' Workbook Object

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select

Yes, but in Tools - References did you uncheck the reference to Excel? You
have to do that.

 

[Klatuu]

No, I did not do that. I will give it a try, thanks. With that in mind, If
I wanted to go back to early binding, do you think this would keep me out of
trouble:

1. Remove reference to Excel in 2003 mdb
2. Comment out explict references (ie. Dim xlApp as New Excel.Application)
3. Remove reference to Excel in 2000 mdb (on machine with 2000)
4. Import object from 2003 mdb to 2000 mdb
5. Re-establish Excel reference in 2000 mdb
6. Remove comment mark
7. Compile

 

[Rick Brandt]

No, I did not do that. I will give it a try, thanks. With that in
mind, If I wanted to go back to early binding, do you think this
would keep me out of trouble:

1. Remove reference to Excel in 2003 mdb
2. Comment out explict references (ie. Dim xlApp as New
Excel.Application)
3. Remove reference to Excel in 2000 mdb (on machine with 2000)
4. Import object from 2003 mdb to 2000 mdb
5. Re-establish Excel reference in 2000 mdb
6. Remove comment mark
7. Compile

Perhaps, but I never use or recommend anything but late binding on a
distributed app. Late binding just solves too many problems and is too easy
to set up to consider anything else.

 

[Klatuu]

I think I am beginning to agree. Microsoft and other reference material
suggests early binding is better and faster. In testing, I found that it is
fater; however, running slow beats not running at all

Thanks again for the help.