obj var vs automation

[Clayton]

What is the difference between using an object
variable, as in this example:



Sub Macro1()

Dim k As Object

Set k = Workbooks.Add

With k
.Sheets(1).Range("E5").Value = "Hello"
.SaveAs ("C:\filename.xls")
.Close
End With

Set k = Nothing

End Sub



and using "Automation," as in this example:



Sub Macro2()

Dim xlApp As Object
Dim xlWorkbook As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Add

With xlWorkbook
.Sheets(1).Range("E5").Value = "World"
.SaveAs ("C:\filename.xls")
.Close
End With

xlApp.Quit
Set xlApp = Nothing
Set xlWorkbook = Nothing

End Sub



If the goal is to create a new workbook,
write in it, and save it (as in the examples
above), they both seem to produce the same
result. So the question is: what's the difference?
Why one over the other?

(Late vs early binding is not an issue.)

Thanks for any help.

Clayton

 

[Harald Staff]

In code 2 you create and run a new instance of Excel. That make no sense if
you already have one Excel running, unless you have far too much RAM on your
computer. Code 1 uses the existing Excel.

HTH. Best wishes Harald

 

[Clayton]

Thanks for clearing that up. Version 1 was
my first choice, anyway. Now it's confirmed.

Clayton