missing references again/late binding

[Guest]

As I was advised from my previous posting about applying the late binding
(thanks to Doug Steele and Tony Toews) I've changed everything to As Object,
as an example the code below. It's working good on my computer. This is why I
thought it's fine and ended my post. But, when I've checked today it from the
another computer I've got the missing references again.

Please look at the code may be something is still wrong there.
......
Dim xlApp As Object
''''Dim xlbook As New Excel.Workbook
Dim xlbook As Object
Dim varCost As Variant
Dim curCost As Currency
''''Dim myRng As Excel.Range
Dim myRng As Object

strRecipeNum = Str_RecipeNum
strFileName = "FileName"

''''Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.AskToUpdateLinks = False
DoCmd.SetWarnings False
xlApp.DisplayAlerts = False

'''xlApp.Visible = True 'nice for testing

Set xlbook = xlApp.Workbooks.Open(strFileName, True)
Set myRng = xlbook.Worksheets("Legend").Range("C3:L500")

varCost = xlApp.VLookup(strRecipeNum, myRng, 10, False)
....

or
....
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
''''Dim xlbook As Excel.Workbook
Dim xlbook As Object
''''Dim xlSheet As Excel.Worksheet
Dim xlSheet As Object

Dim ExcelWasNotRunning As Boolean ' Flag for final release.

Dim strFileName As String, strRecipeNumber As String
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.

Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

strFileName = Var_FileName
strRecipeNumber = Var_RecipeNumber
' Set the object variable to reference the file you want to see.
Set MyXL = GetObject(strFileName)

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
'Do manipulations of your file here.
' ...
Set xlSheet = MyXL.Worksheets(strRecipeNumber)
'Set xlSheet = xlBook.Worksheets(strRecipeNum)

xlSheet.Activate
DoCmd.SetWarnings True
MyXL.Application.DisplayAlerts = True

MyXL.Application.AskToUpdateLinks = True
MyXL.Application.Visible = True
xlSheet.Visible = xlSheetVisible
.....
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

Thanks

 

[Guest]

I think, I've got it. I need to delete any Excel/Wordreferences from the VB
Tools/References.

Please confirm if it's correct.

Thanks

 

[Douglas J. Steele]

Yes, that's correct. You use Late Binding instead of setting a reference.
Even if you're not using the reference, a problem with it is going to affect
all your other references.