Application.Quit Help

[AndFel]

Hi
I’m writing an Outlook macro that looks in the incoming mails and, selects the specific mails, opens the attachments (only excel files), and copy the contents into another workbook, the process is really simple. Anyway after I close the workbooks, and the application, the Applications keeps rolling in the system!
I have already read other post in the forum, suggesting to use System.Runtime.InteropServices.Marshal.ReleaseComObject...., but it doesn’t work in the Microsoft Visual Basic Editor of Outlook, or I don’t know how make it work.
I don’t know if there is another way to end this procces? Could you help me pls??

[TABLE="class: outer_border, width: 800"]
<tbody>[TR]
[TD]Public Function StockReportXlabel(ByVal newStockReport As Attachment)
Dim xlsApp as Excel.Application
Dim DataModel As Excel.Workbook
Dim newFile As Excel.Workbook
Dim filePath As String, srLines As Double, fileName As String

filepath = i save the attachement in a local folder and gets the path


xlsApp.Visible = False
xlsApp.DisplayAlerts = False
xlsApp.ScreenUpdating = False

Set DataModel = xlsApp.Workbooks.Open(fileName:=DATA_PREPARATION_PATH, ReadOnly:=False)
Set newFile = xlsApp.Workbooks.Open(fileName:=filePath, ReadOnly:=True)
newFile.Activate
Range("A6", Cells(Range("A65536").End(xlUp).Row, 9)).Copy
DataModel.Activate
Range("A2").Select
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
xlsApp.CutCopyMode = False
newFile.Close savechanges:=False
Set new File = Nothing

srLines = Range("A65536").End(xlUp).Row
ActiveWorkbook.Names("SR_New").RefersToR1C1 = "=StockReport!R2C1:R" & srLines & "C9"
Range("A1").Select

DataModel.Close savechanges:=True
Set DataModel = Nothing
xlsApp.Quit
Set xlsApp = Nothing
End Function
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!!!

 

[Andrew Poulsom]

Welcome to MrExcel.

Why are you using unqualified Excel objects in Outlook code, eg Range, Cells, ActiveCell and ActiveWorkbook?

 

[AndFel]

I use the ddl for Microsoft Excel 12.0 Object Library... (Tools-->References-->and i search it)... It makes it simple for me

 

[Andrew Poulsom]

That doesn't answer my question. You should fully qualify all Excel properties:

INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic

 

[RoryA]

You still have to properly qualify your objects or you will see the behaviour you describe. Try something like:

Public Function StockReportXlabel(ByVal newStockReport As Attachment)
Dim xlsApp As Excel.Application
Dim DataModel As Excel.Workbook
Dim newFile As Excel.Workbook

Dim filePath As String, srLines As Double, fileName As String

filepath = i save the attachement in a local folder and gets the path


xlsApp.Visible = False
xlsApp.DisplayAlerts = False
xlsApp.ScreenUpdating = False

Set DataModel = xlsApp.Workbooks.Open(fileName:=DATA_PREPARATION_PATH, ReadOnly:=False)
Set newFile = xlsApp.Workbooks.Open(fileName:=filePath, ReadOnly:=True)
With newFile.ActiveSheet
    .Range("A6:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy
End With

DataModel.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats

xlsApp.CutCopyMode = False
newFile.Close savechanges:=False

Set newFile = Nothing

With DataModel
    With .ActiveSheet
        srLines = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    .Names("SR_New").RefersToR1C1 = "=StockReport!R2C1:R" & srLines & "C9"
    .Close savechanges:=True
End With

Set DataModel = Nothing
xlsApp.Quit
Set xlsApp = Nothing
End Function

 

[AndFel]

It Works!!! I can’t believe it was that easy !!
Thanks a lot!

 

[RoryA]

Glad to help. Welcome to the forum, by the way!

 

[AndFel]

Thanks!