OLEObjects.Add Results In Multiple Instances of Excel

[B99]

I posted this on another forum but haven't gotten any results so I'm hoping I'll have better luck here. I have an Access DB that exports data to Excel, including images and documents that I insert using OLEObjects.Add. This is my first time using this function and it has been a learning process but after countless iterations, I have it 'mostly' working. The end result spreadsheet is what I want, however when there is an Excel spreadsheet that is being inserted into the target spreadsheet, the process creates (and leaves open) multiple instances of Excel - two additional instances for each inserted Excel sheet. The additional instances do not have any worksheets open but they are active, meaning I can navigate menus, etc. This does not happen for other Office documents or PDFs, only Excel files. And what's even more strange is that I am unable to close several of the instances after the process completes.

Here is the code I'm using to insert the files:

  Dim xlApp As Excel.Application    'Open the Excel application
  Dim xlBook As Excel.Workbook      'Create a new Excel workbook
  Dim xlImg As Excel.Worksheet      'Create a tab with Attachment details

  Set xlApp = Excel.Application
  Set xlBook = xlApp.Workbooks.Add

'First I add a 'data only' worksheet and do some processing on it, then add a new sheet below for the attachments

  xlBook.Worksheets.Add
  Set xlImg = xlBook.Worksheets(1)

'I get the filename from a recordset
'strIcon is the default executable for the file type
'strAtchName is the file name without the path

xlBook.ActiveSheet.OLEObjects.Add(FileName:=<recordset filename>, _
          Link:=False, DisplayAsIcon:=True, IconFileName:=strIcon, _
          Left:=ActiveSheet.Range("D" & x).Left, Width:=13, _
          Top:=ActiveSheet.Range("D" & x).Top, Height:=56, _
          iconlabel:=strAtchName).Select

'More formatting, then cleanup

Here is a screenshot where there were 6 files that were inserted into the export spreadsheet; 3 Excel files, one Word doc, one PDF and one PPT. Excel was closed before the export, and the screenshot shows 7 instances were running after the process completed.

If I try to close any of the 7, it asks me if I want to save the changes to the export spreadsheet (Book5 in this case). After saving (or discarding), 4 of the instances close and I am left with "ExcelTest2.xlsx", "Excel" and "Book1.xlsx" in this example. I am unable to close any of those without forcing it in Task Manager. However, if I open a new (unrelated) Excel file and close it, two of the instances disappear and if I repeat that process, the third one disappears.

Any ideas on what I can do to either prevent the extra instances from opening or somehow close them? I want to leave the main spreadsheet open so that the user can review it. Thanks in advance for any help!

 

[Micron]

I don't think there is enough info. First, I do believe that while it's good that you declared this was posted elsewhere, it's best to include a link to that site. It may fill in some blanks for us, plus it helps to see what was already suggested so that we don't repeat those suggestions. Second, I think there might be too much of your code missing to provide much of a clue. Before posting the rest of the relevant code you could try 3 things:
- make sure there are no running instances of Excel before you run this code. Something else might be responsible for at least one of those.
- Second, you could step through your code and see if anything is looping and causing the extra instances.
- Last, make sure that none of those instances are adding additional ones with code of their own.

 

[B99]

Hi Micron, thanks for responding. I wasn't sure if posting to another forum was against the rules but since you asked, here is the link to UA. If interested, in that thread there is a link to another thread that has an example of my DB. The example was early on in this process and there are several issues that no longer apply related to formatting, icons, etc. but it does create multiple instances of Excel during an export. If needed to help avoid confusion, I can update the example where the only issue is the current one.

As for the missing code, I can post more of it but really the issue is just in the .Add method. The rest of the code opens a recordset to extract the data (the attachment files are not stored in the DB, only the path to the files) loops through and then handles formatting the spreadsheet.

I have tried all of your suggestions (and many more) before posting. I start the process with no running instances of Excel and I have stepped through the code to see where it trips up. None of the attached files have any code of their own. They are test files that basically have "Test" as the content.

Any other thoughts?

 

[Micron]

I don't use your syntax for creating Excel type objects so I don't know what your method might do in terms of adding members to a collection. My preferred syntax can be found at this excellent site, which might solve your issue. See at least the topic
Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

at EXCEL Export or this page higher in the hierarchy

EXCEL Import And Export

EDIT - I didn't mean to imply that the topic would be applicable - just the method of creating application, workbook, etc. objects; especially the application one.

 

[Legacy 456155]

I no longer ended up with a hidden instance after making these changes.

Change:

Set xlApp = Excel.Application

To

Set xlApp = New Excel.Application

Change unqualified or ambiguous refs:

        Set Atch = .OLEObjects.Add(FileName:=rsAtch!attachmentpath, _
          iconindex:=0, _
          Link:=False, DisplayAsIcon:=True, IconFileName:=rsAtch!iconpath, _
          Left:=ActiveSheet.Range("B" & x).Left, Width:=.Range("B" & x).Width, _
          Top:=ActiveSheet.Range("B" & x).Top, Height:=.Range("B" & x).Height)

To

        Set Atch = .OLEObjects.Add(FileName:=rsAtch!attachmentpath, _
          iconindex:=0, _
          Link:=False, DisplayAsIcon:=True, IconFileName:=rsAtch!iconpath, _
          Left:=.Range("B" & x).Left, Width:=.Range("B" & x).Width, _
          Top:=.Range("B" & x).Top, Height:=.Range("B" & x).Height)

Change unqualified or ambiguous ref:

.ListObjects.Add(xlSrcRange, Range("$A$1:$C$" & x - 1), , xlYes).Name = "Attachments"

To

.ListObjects.Add(xlSrcRange, .Range("$A$1:$C$" & x - 1), , xlYes).Name = "Attachments"

 

[Micron]

Just based on what you posted for the last 2 I can see an improvement (some references looked ambiguous) but I have little idea why creating the application object or its members in that manner would make a difference to how many workbooks you ended up with. Maybe that is caused by ambiguity when referencing to members (e.g. worksheets) of the application object and perhaps it causes new application objects to be created for those child objects.
Glad you solved it and hopefully KS's examples will prove useful in the future. I know that they have for me.

 

[B99]

I'm one step closer. At the beginning of the export, I set Excel to be hidden and then make it visible after everything is exported and formatted correctly. I just noticed that the extra instances of Excel show up after the line to make it visible again. (I'm not sure why that didn't click before!) However, if I leave those lines out (which leaves Excel visible during the export and formatting process) then it completes exactly as expected with no extra instances of Excel.

The end result is perfect but I *really* don't want Excel to be visible while things are flying around and being formatted. Any thoughts on what I might try so that I can make it hidden and then only make the intended instance visible? Could this be related to the ambiguous references you guys mentioned?

Micron - I looked briefly at the reference you provided and I'll have to look at it more in depth. Is that a preferred method for creating objects?

Dataluver - I must be missing it, but when I look your suggestions for changing unqualified or ambiguous refs, the 'from' and 'to' code looks identical. Can you point out the exact part I should change? I'm not sure what you mean by unqualified or ambiguous references.

I tried this:

Set xlApp = New Excel.Application

But that caused it to error on the ".ListObjects" line of code you referenced. Is that also because of the ambiguous references?

 

[Legacy 456155]

...but I have little idea why creating the application object or its members in that manner would make a difference to how many workbooks you ended up with.

nor do I. It was giving me an automation error. I have never seen a reference created like that outside of Excel and was surprised that it worked. (well, it worked some of the time).

perhaps it causes new application objects to be created for those child objects

Range is a global if not qualified, I think. Perhaps it was creating new instances? I have no idea. I was surprised that the code compiled before the changes, but it did.

KS's

Who is that or what does that mean?

Have a nice weekend!

 

[Legacy 456155]

I am going by your example download. When I ran the code, as is, it produced multiple instances and an automation error at times. After the changes, it seems to work fine.

FROM
Left:=ActiveSheet.Range("B" & x).Left, Width:=.Range("B" & x).Width, _
Top:=ActiveSheet.Range("B" & x).Top, Height:=.Range("B" & x).Height)
TO
Left:=.Range("B" & x).Left, Width:=.Range("B" & x).Width, _
Top:=.Range("B" & x).Top, Height:=.Range("B" & x).Height)

FROM
.ListObjects.Add(xlSrcRange, Range("$A$1:$C$" & x - 1), , xlYes).Name = "Attachments"
TO
.ListObjects.Add(xlSrcRange, .Range("$A$1:$C$" & x - 1), , xlYes).Name = "Attachments"
Note the period before Range, qualifying "who" range's parent is. In the case of your example, the worksheet pointed to by xlAtch.

I don't understand how the code compiled without the qualifiers.

See "B99.zip" in this folder.
Extract to C:\Temp

The end result is perfect but I *really* don't want Excel to be visible while things are flying around and being formatted.

If all else fails, you could just minimize the window while it's doing its thing.

xlApp.WindowState = xlMinimized
at the beginning, and
xlApp.WindowState = xlMaximized
at the end somewhere.

 

[Legacy 456155]

Nevermind, that broke it good. I have no idea why, but minimizing and maximizing blew it up.