excel vba with access won't close on this code

Question

Hi I justed posted a few minutes ago and somone asnwerd my question about excel not closing. I am using access to open a sheet and add a table. Excel won't close which causes issues down the road as when I get the excel object again in another function the sheet I am working with won't open and it won't format it. Here is my code. I thought I was explicit here but maybe I am not. Excel just won't closed.

Public Function BrooksFormatTableBrooks()
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

bfile = "S:\_Reports\Brooks\Tyco-Brooks Receiving Tracking MASTER - "

MyFileName = bfile & Format(Date, "mm-dd-yyyy") & ".xls"

On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
On Error GoTo 0

Set wb = xlApp.Workbooks.Open(MyFileName)
Set ws = wb.Sheets(1)
ws.Activate

wb.Sheets(1).Name = "RSSR_List"

Set ws = wb.Sheets(1)
ws.Activate

xlApp.ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$312"), , xlYes).Name = _
     "RSSR"

ws.Range("A1:F312").Select
DoEvents

ws.Cells.Rows("2:2").Select
xlApp.ActiveWindow.FreezePanes = False
xlApp.ActiveWindow.FreezePanes = True

ws.Columns("A:Z").HorizontalAlignment = xlCenter
ws.Rows("1:1").Font.Bold = True
ws.Rows("1:1").Font.ColorIndex = 1
ws.Rows("1:1").Interior.ColorIndex = 15
ws.Cells.Font.Name = "Calbri"
ws.Cells.Font.Size = 8
ws.Cells.EntireColumn.AutoFit
ws.Cells.EntireRow.AutoFit

xlApp.Cells.Borders.LineStyle = xlContinuous
xlApp.Cells.Borders.Weight = xlThin
xlApp.Cells.Borders.ColorIndex = 0

ws.Cells.Rows("1:1").Select

wb.CheckCompatibility = False
wb.Save
wb.CheckCompatibility = True
wb.Close SaveChanges:=True

xlApp.Quit

Set xlApp = Nothing
Set wb = Nothing
Set ws = Nothing
MsgBox "Table Add"
End Function

As I said in a comment on the other question, qualify your "Range". Range("$A$1:$F$312") should be ws.Range("$A$1:$F$312") rather than defaulting to Application.ActiveWorkbook.ActiveSheet.Range("$A$1:$F$312") — YowE3K
– YowE3K, Commented May 27, 2017 at 21:34
It is very likely that the xlApp is displaying some alert or prompt and gets blocked, (notice you are working with an old xls format, in compatibility mode) but since the app is not visible you cannot see what's happening with it. Try adding xlApp.Visible = True to observe what is happening. It is possible that xlApp.DisplayAlerts = False would solve the issue, with leaving wb.CheckCompatibility = False.But let us observe what is happening first. — A.S.H
– A.S.H, Commented May 27, 2017 at 22:02
@A.S.H - FWIW, I tested the code in this question in Access, and the unqualified Range does force Excel to remain active, and qualifying the Range allows it close correctly. — YowE3K
– YowE3K, Commented May 27, 2017 at 22:11
@A.S.H - If the project includes a Reference to Microsoft Excel Object Library, the Range will be recognised as an Excel Range method, but will create an extra reference to the Excel Application object - so it doesn't get destroyed at the end. (Excel will disappear from Task Manager only once all the references to it are destroyed - which will be after Set ws = Nothing if the Range is qualified as ws.Range.) — YowE3K
– YowE3K, Commented May 27, 2017 at 22:21

YowE3K · Accepted Answer · 2017-05-27 21:49:55Z

2

Replace Range("$A$1:$F$312") with ws.Range("$A$1:$F$312") or else you will still have a reference to an Excel Application object that won't be destroyed until you exit MSAccess.

answered May 27, 2017 at 21:49

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

A.S.H Over a year ago

That behavior of VBA is strange though, because the unnamed range variable evaporates within the sub, so why is a reference maintained because of it, is just ......... an unbelievable anomaly to say the least.

YowE3K Over a year ago

@A.S.H Possibly a bug? But also possibly because it is the Access application itself which is being forced to hold the reference to the Range. (I've given up trying to make sense of why VBA does what VBA does.)

Gustav Over a year ago

@A.S.H: It's not a bug. It's just that you have to be extremely specific using objects in Excel VBA when you use automation. You'll learn it the hard way, as the macro recorder of Excel won't educate you, and you can get away with code like this within Excel. But not using automation as here.

A.S.H Over a year ago

@Gustav the rules of COM is that an object is released when you dont maintain any reference on it. The fact that a temporary and unnamed variable causes a permanent reference on the xlApp, like here, is a bug.

Gustav Over a year ago

Yes, you and I can mean that; it just doesn't work that way in Excel VBA and never had - since Excel 95/97 - and probably never will. So adopt - resistance is futile.

Collectives™ on Stack Overflow

excel vba with access won't close on this code

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related