CODEWITHSUNDEEP
excelms-accessprocessvba
Shatterday
Shatterday

Reputation: 35

How do I get rid of my orphan MS Excel process from MS Access VBA?

I'm exporting an MS Access query to a template, doing some formatting, and then saving the template as a new name. When this is all done, I have an orphan MS Excel process that is interfering when the function is called again. I'm thinking this is either a problem with how I'm using ranges or a problem with my cleanup at the end.

Also I'm a novice coder so if anyone has any tips and tricks that I can take advantage of to make this better I'm always receptive.

Updated code after Andre's comments Updated code after Rory's comments

Public Function OpenOrders(strSupplier As String)

'Excel file variables
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim xlsLRow As Long
Dim xlsLCol As Long

'Access variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

'Set up access objects
strSQL = "SELECT * FROM qryOpenOrderReport WHERE [Supplier Cd] = '" & strSupplier & "';"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'Set up excel connection
Set xlapp = CreateObject("Excel.Application")
Set wb = xlapp.Workbooks.Open(Application.CurrentProject.Path & "\Open Order Template.xlsx")
Set ws = wb.Worksheets(1)
xlapp.Visible = True

'Make sure the form is clear
xlsLRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).row
xlsLCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
ws.Range("A2", ws.Cells(xlsLRow, xlsLCol)).ClearContents

'Copy recordset to worksheet
ws.Cells(2, 1).CopyFromRecordset rs
rs.Close

'Copy formats down and autofit
xlsLRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).row
xlsLCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
xlapp.CutCopyMode = False
ws.Range(ws.Cells(2, 1), ws.Cells(2, xlsLCol)).Copy
ws.Range(ws.Cells(3, 1), ws.Cells(xlsLRow, xlsLCol)).PasteSpecial (xlPasteFormats)
ws.UsedRange.Columns.AutoFit

'Clean up
xlapp.DisplayAlerts = False
Set ws = Nothing
wb.SaveAs Application.CurrentProject.Path & "\Open Orders\" & strSupplier & ".xlsx"
wb.Close True
Set wb = Nothing
xlapp.Quit
Set xlapp = Nothing

End Function

Upvotes: 0

Views: 674

Answers (3)

Gustav
Gustav

Reputation: 55816

You have to be extremely specific with objects of Excel, opening them and closing in reverse order. Here's a skeleton that works:

 Dim xls     As Excel.Application
 Dim wkb     As Excel.Workbook
 Dim wks     As Excel.Worksheet
 Dim rng     As Excel.Range

 Set xls = New Excel.Application
 Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
 Set wks = wkb.Worksheets(1)
 Set rng = wks.<somerange>  ' Cells or whatever.

 ' Do stuff.

 ' Clean up.
 Set rng = Nothing

 wks.Name = "My New Name"
 wkb.Close True

 Set wks = Nothing
 Set wkb = Nothing

 xls.Quit

 Set xls = Nothing

Don't ever use wkb.Sheets for a WorkSheet.

Upvotes: 1

Rory
Rory

Reputation: 34045

All of your Cells calls need to be changed to ws.Cells. That is what is causing your orphaned process.

Upvotes: 3

Andre
Andre

Reputation: 27634

My guess is that you still have an active reference to ws when doing the clean up, which prevents Excel from quitting.

I suggest doing it in this order:

'Clean up
xlapp.DisplayAlerts = False
Set ws = Nothing
wb.Close True, strSupplier
Set wb = Nothing
xlapp.Quit
Set xlapp = Nothing

ws.Range("A2", "XFD1048576").ClearContents seems a little radical :) - you can use .UsedRange for that.

An additional note:

After opening a recordset, you can never be in a situation where rs.EOF is False, but rs.BOF is True. So it is not necessary to test for rs.BOF.

With changing the loop to Do While, the If Not (rs.EOF And rs.BOF) Then becomes superfluous:

Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF 
    ' ...
    rs.MoveNext
Loop

Upvotes: 1

Related Questions