Can't Close Excel through Access VBA

SAGulick

Registered User.
Local time
Today, 15:01
Joined
Feb 8, 2006
Messages
18
I've been all over this site, as well as others, just trying to find a solution that works for my code, but have had no luck. The last attempt, I used the Total Access Analyzer source code. It worked fine...until I inserted my code in between the start and close of Excel. My code is shown, below:

Private Sub cmdManipulateXLFile_Click()
On Error GoTo Err_cmdManipulateXLFile_Click

DoCmd.SetWarnings False

''WILL UNCOMMENT WHEN FINISHED
'DoCmd.OpenQuery "qryQTRLYPRSNLEXP0010MaketblLastQtrPersonalExpenses", acNormal, acEdit
'DoCmd.OpenQuery "qryQTRLYPRSNLEXP0020AppendTotblLastQtrPersonalExpenses", acNormal, acEdit
'DoCmd.OpenQuery "qryQTRLYPRSNLEXP0030AppendTotblLastQtrPersonalExpenses", acNormal, acEdit

DoCmd.OutputTo acQuery, "qryQTRLYPRSNLEXP0040SortedReport", acFormatXLS, "\\Alb1pwafpr01\local\COMMON\TandE-PCard\TravelandExpense\New GELCO\Access Database for T&E\Reports\Quarterly\Personal Expenses\Files Sent\QtrlyPersExps" & Format(Date, "yyyy-mm-dd") & ".xls", False, ""

DoCmd.SetWarnings True

'Hides built-in Excel messages
Excel.Application.DisplayAlerts = False

Dim mobjExcel As CExcel2003Extended

Set mobjExcel = New CExcel2003Extended

'Start an instance of Excel
mobjExcel.StartExcel True


'**********THIS IS WHERE MY CODE STARTS**********
'Create default workbook
mobjExcel.OpenWorkbook ("\\Alb1pwafpr01\local\COMMON\TandE-PCard\TravelandExpense\New GELCO\Access Database for T&E\Reports\Quarterly\Personal Expenses\Files Sent\QtrlyPersExps" & Format(Date, "yyyy-mm-dd") & ".xls")

Sheets("qryQTRLYPRSNLEXP0040SortedRepor").Select
Sheets("qryQTRLYPRSNLEXP0040SortedRepor").Name = "Qtrly Pers Exps"

Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yyyy"
Cells.Select

With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

Rows("1:1").Select
Selection.Font.Bold = True
Rows("1:4").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Personal Expenses (Due to AMEX)"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Data From " & Format(Me.txtStartDt, "mm-dd-yyyy") & " Through " & Format(Me.txtEndDt, "mm-dd-yyyy")
Range("A1").Select

With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

Selection.Font.Bold = True
Range("A1:F1").Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Selection.Merge
Range("A2").Select

With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

Selection.Font.Bold = True
Range("A2:F2").Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Selection.Merge
Columns("A:F").EntireColumn.AutoFit
Range("A1:F1").Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&6Run Date: &D, &T"
.RightFooter = ""
.LeftMargin = Excel.Application.InchesToPoints(0.25)
.RightMargin = Excel.Application.InchesToPoints(0.25)
.TopMargin = Excel.Application.InchesToPoints(1)
.BottomMargin = Excel.Application.InchesToPoints(1)
.HeaderMargin = Excel.Application.InchesToPoints(0.5)
.FooterMargin = Excel.Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

'ActiveWorkbook.Save
Excel.ActiveWorkbook.SaveAs FileName:= _
"\\Alb1pwafpr01\local\COMMON\TandE-PCard\TravelandExpense\New GELCO\Access Database for T&E\Reports\Quarterly\Personal Expenses\Files Sent\QtrlyPersExps" & Format(Date, "yyyy-mm-dd") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'Unhides built-in Excel messages
Excel.Application.DisplayAlerts = True
'**********THIS IS WHERE MY CODE ENDS**********


''Close Workbook (leave commented out if you wish to see results above)
'mobjExcel.CloseWorkbook False

''Close Excel (leave commented out if you wish to see results above)
'mobjExcel.CloseExcel

'Set mobjExcel = Nothing

Exit_cmdManipulateXLFile_Click:
Exit Sub

Err_cmdManipulateXLFile_Click:
MsgBox Err.Description
Resume Exit_cmdManipulateXLFile_Click

End Sub
 
What is the error you are getting
Where is error occuring
Have you tried Excel.Quit

Set Excel = nothing
 
Yep, that was what I tried the first time, but still had the same problem. You may notuice that I commented out the closing of the excel file. I did that to ensure, when I manually closed the file that it would, in turn, disappear from the Task Mgr processes window...it didn't...lol.
 
...sorry, I don't get any error msg and it doesn't halt the code.
 
Try

mobjExcel.quit

or

mobjExcel.application.quit
 
What Excel objects version are referenced in your application references?
 
I get a method or data member not found. Should I just try Excel.Quit?
 
Refs: Excel 11.0 Obj Lib, Total Visual CodeTools, Total Visual SourceBook
 
Its just that I've never seen Excel referenced as CExcel2003Extended

If you go to Tools > References you should see which version of Excel is referenced
 
I'm think the CExcel2003Extended is an new excel class someone created for what is being done. Did you try

mobjExcel.quit

or

mobjExcel.application.quit

?
 
Snipped of code that I use

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim tDate As String

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(1)


Dim rs As DAO.Recordset

iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

With ActiveSheet.UsedRange
iCol = .Cells(1, 1).Column + .Columns.Count - 1
iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With


.......
.......


Set rs = Nothing
oBook.Close

oExcel.Quit
 
Yeah, but I got a 'Method or data member, not found" error. The class was created by the "Total Access Analyzer" suite.
 
This is just a footnote: Sometimes when you don't close an application object, like if you get an error in your code before you get to the =nothing code, you have to reboot your pc to close the orphaned object(s). I think it's because they are enumerated by the system, etc...
 
I read at the MSDN site that this was a known issue w/Access. Being that I'm pretty new to VBA, I'm not quite sure HOW to fix it with their workaround?
 
The only way it clears in the Task Mgr is if I close Access.
 
So if your code always makes it to the part where you kill the app object you should be ok. Right?
 
If you're asking if it's a runtime error, yes it is. To the user, the code seems to run fine, because the Excel window will close. The problem is, is that even tho Excel closed, there is an instance of Excel in the Task Manager processes. With that said, my code will crash, the next time the user runs the code.
 
One more thing. If I end the task, Excel.exe in the Task Mgr, and rerun the code, I get this err msg (The remote server machine does not exist or is not available). If I just rerun my code, I get this err msg (Method ‘Sheets’ of object ‘_Global’ failed).
 
I don't think I'm getting through. But it may be just as well because it's probably some other issue - :)
 

Users who are viewing this thread

Back
Top Bottom