Using VBA to link Unbound Object Frame to Workbook (1 Viewer)

[Mr. B]

Shortly after submitting this thread, I discovered that a instance of Excel is opened when the action to perform the link is taken. Then once the link to the unbound object is activated the instance of Excel remains open until the link is removed or the form is closed.

If anyone has more info on this and any workarounds for dealing with the open instance of Excel, please let me know.

I have an unbound object frame control on an Access 2010 form that I am using to display a chart from a specific worksheet range in a workbook. It is defined using the following properties:
OLE Type: Linked
OLE Type Allowed: Either
Ole Class: Microsoft Excel 2003
Class: Excel.Chart.8
Update options: Automatic
Display Type: Content
Enabled: Yes
Locked: No

I have using the following code to assign the source parameters for the control.

Public Function GetOLEChart(sSourceDoc As String, sSourceItem As String, sFormName As String, sFileName As String)
Forms(sFormName).OLEMyExcelSheet.visible = True
DoEvents
With Forms(sFormName).[OLEMyExcelSheet]
.enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLELinked
' Class statement--optional for Excel worksheet.
.Class = "Excel.Sheet"
' Specify the file to be linked.
' Type the correct path name.
'.SourceDoc = "C:\DRRBSM\New Books\35000 SUBSIDIARIES.xlsm"
.SourceDoc = sSourceDoc
' Range statement--optional for Excel worksheet.
'.SourceItem = "35000!R1C1:R30C14"
.SourceItem = sSourceItem
' Create the linked object.
.Action = acOLECreateLink
' Optional size adjustment.
.SizeMode = acOLESizeStretch
End With

Everything works great to retrieve and display the chart. However, I just discovered that using this code causes an instance of Excel.exe *32 to be left open. The instance is evidently opened when the .Action = acOOECreateLnnk line is executed.

I must either be able to run this code without leaving the instance of Excel open or have some way to detect that it is open and close it.

Any assistance would be appreciated.

Byron

 

[BlueIshDan]

I'm sorry but I don't see the problem here. You're writing a program to link to an excel doc and you noticed an excel process starting at the time it is linked and opened and ending at the point the link is closed.

My gut tells me there has to be more. Is it actually opening the Excel Document separate from the Access Database Form's unbound object?

 

[vbaInet]

Is it actually opening the Excel Document separate from the Access Database Form's unbound object?

I think that's what Mr. B is on about otherwise there's no problem.

 

[Mr. B]

The only problem here is that I was not expecting that just creating a link to a chart in a workbook would cause an instance of Excel to be initiated and that instance would remain open until the link is broken or the Access form is closed.

I really think that I am going to be able to work around this and it should not be a problem.

I think I kinda pulled the trigger on posting this a littel too quick.

Byron

 

[vbaInet]

So Mr. B are you talking about an Excel process in Task Manager or an instance of the Excel application itself, i.e. the Excel window?

 

[Mr. B]

Yes, I am talking about an Excel process in the Task Manger.

 

[Mr. B]

If you recall, when using VBA code to open and manage Excel workbooks, if you do not reference every object by setting an object variable for each, (ie. App, Workbook, Worksheet, Range, etc) you can and will have one or more instances of Excel left open in the Task Manager when your code has been run. When I ran into this issues where creating the link to the unbound object frame open the instance of Excel in the Task Manager and when remaind there after the link was established, I became concerned and was thinking that I was experiencing the same thing as when running VBA code to manage the spreadsheet with out the correct referenced object variables.

As I said, I just pulled the trigger a little too quick on this one.

 

[vbaInet]

I see, got you!

 

[Mr. B]

Thanks for trying to help. I really do appreciate it.

Byron

 

[vbaInet]

It's more thanks to BlueIshDan for kicking things off.

 

[Mr. B]

Well, thanks to both of you.