Difference between CreateObject("Excel.Application") .Workbooks.Open and just Workbooks.Open

excel, vba

Solution

`Workbooks.Open` uses the current MS Excel instance and `CreateObject(“Excel.Application”)` creates a new MS Excel instance. You can read up on `CreateObject` here.

Simply issuing a `Workbooks.Open` after creating a new instance will not ensure that the workbooks open in the new instance. You will have to bind with it. For example

Dim oXLApp As Object, wb As Object

Set oXLApp = CreateObject("Excel.Application")

'~~> Hide Excel
oXLApp.Visible = False

'~~> Open files
Set wb = oXLApp.Workbooks.Open("C:\Sample.xls")

Regarding your other question

Also, should I just use one instance of Excel created using CreateObject to open all Workbooks or do I need to create one instance for each workbook I have to process

You don't need several instances. You can work with one instance. For example

Dim oXLApp As Object, wb As Object

Set oXLApp = CreateObject("Excel.Application")

'~~> Hide Excel
oXLApp.Visible = False

'~~> Open files
Set wb = oXLApp.Workbooks.Open("C:\Sample1.xls")

'
'~~> Do some Stuff
'

wb.Close (False)

'~~> Open files
Set wb = oXLApp.Workbooks.Open("C:\Sample2.xls")
'
'~~> Do some Stuff
'

wb.Close (False)

'
'~~> And So on
'

Problem

I am currently using `Workbooks.Open` to process a large number of files contained in a directory. But opening and closing these files make the files appear in the task bar and I wanted to avoid the constant flickering. I got a suggestion from that I can use `CreateObject` to create a new instance since that opens a new Excel instance which is hidden. Is there any other difference between the two ways of opening new workbooks in terms of performance? Also, should I just use one instance of Excel created using `CreateObject` to open all Workbooks or do I need to create one instance for each workbook I have to process (which seems like a waste of lot of memory and less speed)?

Original source