MIssing References

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
 
P

Patrick Molloy

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
 
D

Dave Peterson

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.)
 
D

Dave Peterson

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>>
 
Top