MIssing References

  • Thread starter Thread starter RJ
  • Start date Start date
R

RJ

When i use Early Bound setup on my Office 2003 and move
the VBA to the Client's system - using Office XP, the
Excel 11.0 Object Library does not change to Excel 10.0
(which is on their PC).

Q - I understand that this is to do with setting up Late
Bound - by why does Word work fine amd Excel not?

Q - I have in the Declarations:
Private oXL as Object
Private oWB as Object
Private oWS as Object

then... ' Create Excel Objects
Set oXL = CreateObject("Excel.Application")
Set oWB = CreateObject("Excel.Workbook")
Set oWS = CreateObject("Excel.Worksheet")

but it stops with an Active-X error on the second Set.
Why?

Q - using the above, is it going to create a new instance
of Excel each time?

....thanks
 
Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.Workbooks. Add
Set oWS = oWB.Activesheet
'or
Set oWS = oWB.Worksheets.Add

Patrick Molloy
Microsoft Excel MVP
 
I have no idea why it worked for Word.

But this is one way to check to see if excel is running:

Private oWS As Object
Sub testme()

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
Set oXL = CreateObject("Excel.Application")
End If

oXL.Visible = True
Set oWB = oXL.workbooks.Add(1)
Set oWS = oXL.activesheet

'do stuff

oWB.Close savechanges:=False
oXL.Quit

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub


Are you running this from inside Excel? If yes, then you could just use the
current instance to do what you want.

(And yeah, your code would've start a new instance each time.)
 
Oops, I dropped the declarations:

Option explicit
Private oXL as Object
Private oWB as Object
Private oWS as Object
Sub testme()

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
Set oXL = CreateObject("Excel.Application")
End If

oXL.Visible = True
Set oWB = oXL.workbooks.Add(1)
Set oWS = oXL.activesheet

'do stuff

oWB.Close savechanges:=False
oXL.Quit

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

<<snipped>>
 
Back
Top