Help with converting early binding to late binding

[crarbo1]

Hello,
I'm hoping someone will be able to help me fix my issue. I have inherited some word documents that update an Excel spreadsheet. I'm not an expert in VBA but can do some basic things. The issue I have is that the word documents were created in Word 2000 and reference the Excel library. If someone opens the file in Word 2010 and saves it, then there is a compile error when someone else opens it up in Word 2000. It appears that reference to the Excel library is missing when it is opened back up in Word 2000. In trying to find a solution, it appears that taking code that uses early binding and converting it to late binding would resolve my issue. Is that correct? I really have no idea as to what to do but I have attached my code for your reference. From what I read, it is using early binding.
Thanks,
Chuck

Sub UpdateTrainingLog()
Dim xlApp As Object
Dim xlb As Object
Dim wksht As Object
Dim r As Integer
Dim LogName As String
Dim ShortLogName As String
Dim SSO As String
Dim TName As String
Dim Standard As String
Dim mrange As Object
Dim uresp As Integer

On Error GoTo XLError
ThisDocument.FormFields("Approval").Range.Tables(1).Cell(1, 1).Range.Text = "Updating training log..."
LogName = "\\TestFolder\EMC Training Log.xls"
ShortLogName = "EMC Training Log.xls"
Set xlApp = New Excel.Application
Set xlb = xlApp.Workbooks.Open(FileName:=LogName, writerespassword:="straub") ' Excel.Workbooks.Open(rffile)
If xlb.ReadOnly = True Then 'User does not have read/write permission
MsgBox "You do not have permission to access the server where the EMC Training Log is located, therefore your training record has NOT been updated as required." & vbCrLf & vbCrLf & "Corrective action: Please contact Dave Terrell to have your name added to the server permissions list.", vbCritical + vbOKOnly, "Access denied to training log"
xlb.Close SaveChanges:=False
GoTo XLBail
End If
Set wksht = xlb.Worksheets("Training Log")
SSO = GetSingleDataItem(ThisDocument, "SSO#:")
TName = GetSingleDataItem(ThisDocument, "sonnel:")
If Trim(TName) = "" Then
TName = "( No name entered )"
End If
Standard = GetSingleDataItem(ThisDocument, "Basic Standard:")
TryAgain:
If (Not IsNumeric(SSO)) Or (Len(SSO) <> 9) Then 'Bad SSO number
SSO = GetUserSSO()
uresp = MsgBox("Unable to update training records for " & TName & " due to unrecognizable SSO#." & vbCrLf & vbCrLf & "Would you like to try again using the following SSO#: " & SSO, vbYesNo, "Bad SSO#")
If uresp = vbNo Then
GoTo XLBail
Else
PutSingleDataItem ThisDocument, "SSO#:", SSO
GoTo TryAgain
End If
End If
If TName = "( No name entered )" Then
'If possible get name from log file
For r = 2 To 32000
If wksht.Range("A" & Trim(Str(r))).Value = SSO Then 'Get user name
TName = Trim(wksht.Range("B" & Trim(Str(r))).Value)
If TName > "" Then
uresp = MsgBox("Unable to update training records for SSO# " & SSO & " because no name has been entered in the Test Personnel field. Would you like to try again using the following name: " & TName, vbYesNo, "Missing user name")
If uresp = vbNo Then
GoTo XLBail
Else
PutSingleDataItem ThisDocument, "sonnel:", TName
GoTo TryAgain
End If
End If
End If
Next r
End If
If TName = "( No name entered )" Then 'Could not find name in database
MsgBox "Unable to update training records for SSO# " & SSO & " because no name has been entered in the Test Personnel field. Please complete this field, and then have your setup approved again in order to update your training records.", vbOKOnly, "Missing user name"
GoTo XLBail
End If
'Ready to update log
wksht.Range("A1:A32000").Sort Key1:=wksht.Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For r = 2 To 32000
If wksht.Range("A" & Trim(Str(r))).Value = SSO Then 'Check for Standard match
If wksht.Range("C" & Trim(Str(r))).Value = Standard Then 'This is it
wksht.Range("B" & Trim(Str(r))).Value = TName
wksht.Range("C" & Trim(Str(r))).Value = Standard
wksht.Range("D" & Trim(Str(r))).Value = Date
GoTo XLBail
End If
Else
If (wksht.Range("A" & Trim(Str(r))).Value > SSO) Or (wksht.Range("A" & Trim(Str(r))).Value = "") Then 'No more matching entries
wksht.Range("A" & Trim(Str(r))).EntireRow.Insert
wksht.Range("A" & Trim(Str(r))).Value = SSO
wksht.Range("B" & Trim(Str(r))).Value = TName
wksht.Range("C" & Trim(Str(r))).Value = Standard
wksht.Range("D" & Trim(Str(r))).Value = Date
Set mrange = wksht.Range("A" & Trim(Str(r)) & ":D" & Trim(Str(r)))
With mrange
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = xlNone
.Font.Bold = False
End With
GoTo XLBail
End If
End If
Next r
XLBail:
On Error Resume Next
xlb.Close SaveChanges:=True
Set wksht = Nothing
Set xlb = Nothing
xlApp.Quit
Set xlApp = Nothing
Set mrange = Nothing
Exit Sub
XLError:
MsgBox "Unable to update training records.", vbOKOnly + vbInformation, "File access error"
Resume XLBail
Resume
End Sub

[Frosty]

Instead of this line:

Set xlApp = New Excel.Application

You should use this...

Set xlApp = GetObject("Excel.Application")
or
Set xlApp = CreateObject("Excel.Application")

I have not looked through your code to see which is better, but this is the direction to look.

This will allow you to remove references to the Excel object model in your project, under Tools > Options > References

[Frosty]

Just to clarify... it looks to me like you've declared all your references as Objects (rather than Excel.Workbook, etc), so the only thing left was to actually create your Excel object, rather than rely on setting to a new Excel.Application.

You probably want to use CreateObject (which will start Excel for you) rather than GetObject (which would use an existing Excel process, but fail if Excel wasn't already open).

However, there are other issues with using late binding rather than early binding (mostly on the development side), so it can be good practice to leave something like the following in your code...

'Dim xlApp as Excel.Application
Dim xlApp as Object

And then all you have to do is switch which dimension is commented, and set a reference in your word project to the Excel object model... and then the autocomplete will work during development (which can be handy, obviously).

[crarbo1]

Frosty,
Thanks for your reply. I had done some research on early and late binding and knew that I had to change my variables to objects, so I went ahead and did that. Your help on the application is great. How would I handle the workbook and worksheet references in the code. I apologize for this type of question, but I have little experience with this.

Thanks,
Chuck

[Frosty]

They are just properties of the excel app. Since you've already changed the variables to objects, and you set them off of xlApp... I think you're good to go. Make sure you're using option explicit, compile the project, and run the code.

Let us know if & where it breaks

[crarbo1]

Frosty,
Again, thanks for the help. I will try this tonight and report back.

Thanks,
Chuck

They are just properties of the excel app. Since you've already changed the variables to objects, and you set them off of xlApp... I think you're good to go. Make sure you're using option explicit, compile the project, and run the code.

Let us know if & where it breaks

[crarbo1]

Frosty,

After inserting Set xlApp = CreateObject("Excel.Application") in my code, I get a "variable not defined" error when I compile at the " Order1:=xlAscending" part of the last line of the code below. Not sure what I need to do.

Thanks,
Chuck

Sub UpdateTrainingLog()
Dim xlApp As Object
Dim xlb As Object
Dim wksht As Object
Dim r As Integer
Dim LogName As String
Dim ShortLogName As String
Dim SSO As String
Dim TName As String
Dim Standard As String
Dim mrange As Object
Dim uresp As Integer

On Error GoTo XLError
ThisDocument.FormFields("Approval").Range.Tables(1).Cell(1, 1).Range.Text = "Updating training log..."
LogName = "\\TestFolder\EMC Training Log.xls"
ShortLogName = "EMC Training Log.xls"
'Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")
Set xlb = xlApp.Workbooks.Open(FileName:=LogName, writerespassword:="straub") ' Excel.Workbooks.Open(rffile)
If xlb.ReadOnly = True Then 'User does not have read/write permission
MsgBox "You do not have permission to access the server where the EMC Training Log is located."
xlb.Close SaveChanges:=False
GoTo XLBail
End If
Set wksht = xlb.Worksheets("Training Log")
SSO = GetSingleDataItem(ThisDocument, "SSO#:")
TName = GetSingleDataItem(ThisDocument, "sonnel:")
If Trim(TName) = "" Then
TName = "( No name entered )"
End If
Standard = GetSingleDataItem(ThisDocument, "Basic Standard:")
TryAgain:
If (Not IsNumeric(SSO)) Or (Len(SSO) <> 9) Then 'Bad SSO number
SSO = GetUserSSO()
uresp = MsgBox("Unable to update training records for " & TName & " due to unrecognizable SSO#."
If uresp = vbNo Then
GoTo XLBail
Else
PutSingleDataItem ThisDocument, "SSO#:", SSO
GoTo TryAgain
End If
End If
If TName = "( No name entered )" Then
'If possible get name from log file
For r = 2 To 32000
If wksht.Range("A" & Trim(Str(r))).Value = SSO Then 'Get user name
TName = Trim(wksht.Range("B" & Trim(Str(r))).Value)
If TName > "" Then
uresp = MsgBox("Unable to update training records for SSO# " & SSO)
If uresp = vbNo Then
GoTo XLBail
Else
PutSingleDataItem ThisDocument, "sonnel:", TName
GoTo TryAgain
End If
End If
End If
Next r
End If
If TName = "( No name entered )" Then 'Could not find name in database
MsgBox "Unable to update training records for SSO# " & SSO"
GoTo XLBail
End If
'Ready to update log
wksht.Range("A1:A32000").Sort Key1:=wksht.Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

[Frosty]

The quick and dirty answer to converting those Excel public enums is to either a) set a Constant in your project to their values or b) just change it to the actual value.

You can find out the values by opening up Excel > VBA window > Immediate Window and typing "?xlAscending"

And then setting a bunch of constants to those values, where appropriate.

I prefer that to just changing to the actual values, because it keeps your code somewhat readable.

I would have thought there was a way to programmatically grab the right info, but I can't seem to find it.

[crarbo1]

Frosty,
Thanks again. I will go through this getting the right values and just making those constants. I think this is the only code that references Excel, so it shouldn't take too long.

Thanks,
Chuck

[crarbo1]

Frosty,
Doing that seems to make things work.

Thanks for your help,
Chuck

[fumei]

That is the thing with late-binding, you need the actual values. You can also get them using the Object Browser.

[mdmackillop]

You could add a reference to Excel in the code. Check out this KB Item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=267)

[fumei]

Which would bring it back to early binding.

[mdmackillop]

Hi Gerry,
According to the item description, it will add a reference which is not Version Specific.

[Frosty]

Programmatically adding the right reference would be "good" because it would allow you to use early binding in your code, while still being able to save the project in different versions of the application.

The downside is that you'd need to expose your macro security more, by trusting VBProject... which may be a show stopper, depending on the security requirements of the end-user (and if they aren't local admins, some of this stuff tends to break, I think).

Programmatically adding references is neat, but it's always scared me a little.

[fumei]

It works, but requires careful testing in a multi-version environment. AND there can be, as Frosty points out, security issues - as well as possible Permission issues.