Can't get Excel to quit

T

Todd Waldron

Hi all,

I am using automation (From Access) to format a saved .xls
file. Even after all the research I have done on this
newsgroup, I still can't get Excel to quit through code.
I'm sure I must be missing something.

Below is what I'm doing in Code from Access:

*****Start*****
Dim appExcel As Excel.Application
Dim wkbWorkBook As Excel.Workbook
Dim wksWorkSheet As Excel.Worksheet

'Opens a saved report
Set wkbWorkBook = Excel.Application.Workbooks.Open
(strImpPath & txtClientName.Value & "_report.xls")

'Defines the worksheet
Set wksWorkSheet = Excel.Application.Worksheets("report")

'Run formatting code here

'Save, Close, Quit
wkbWorkBook.Save
wkbWorkBook.Close
*ATTEMPTING TO QUIT HERE*
Set appExcel = Nothing
Set wkbWorkBook = Nothing
Set wksWorkSheet = Nothing
*****End*****

If I use the variable - "appExcel.Quit", I get "RT-Err 91
ObjVar or With Block not set".

If I specify "Excel.Application.Quit", the procedure
completes successfully. However, Excel is still running
in the tsk mgr and if I try to view the report.xls I get
some kind of ghost window where I can see the Excel tool
bar (like the app is running) but no workbook.

If I comment out the ".Quit" line altogether: the
procedure completes successfully, Excel is still running
in tsk mgr, but this time I can open the report.xls and
see everything. However if I then close report.xls and
then try to open again immediately, I get the ghost Excel
window again. The only way to stop this is to exit my
Access app or quit Excel from the tsk mgr.

Any help with this would be greatly appreciated.

Thank you,

Todd Waldron
Austin, Tx.
 
D

Don Guillett

try
application.quit
'Save, Close, Quit
wkbWorkBook.Save

application.quit

'> wkbWorkBook.Close
'> *ATTEMPTING TO QUIT HERE*
'> Set appExcel = Nothing
'> Set wkbWorkBook = Nothing
'> Set wksWorkSheet = Nothing

*****End*****
 
J

John Green

Tod,

You are not assigning Excel to appExcel. It remains undefined. Use something like:

Dim appExcel As Excel.Application
Dim wkbWorkBook As Excel.Workbook
Dim wksWorkSheet As Excel.Worksheet

Set appExcel = New Excel.Application

'Opens a saved report
Set wkbWorkBook = appExcel.Workbooks.Open(strImpPath & txtClientName.Value & "_report.xls")

'Defines the worksheet
Set wksWorkSheet = wkbWorkBook.Worksheets("report")

You should then be able to appExcel.Quit after closing the workbook.
 
T

Todd Waldron

Hi John,

Thank you for your reply. I did what you suggested but I
am sitll having the same problem. The procedure ran all
the way through and finished, but Excel is still running
in tsk mgr and I get the ghost window when I try to view
the report.xls. ??? :blush:(

Any other suggestions?
-----Original Message-----
Tod,

You are not assigning Excel to appExcel. It remains undefined. Use something like:

Dim appExcel As Excel.Application
Dim wkbWorkBook As Excel.Workbook
Dim wksWorkSheet As Excel.Worksheet

Set appExcel = New Excel.Application

'Opens a saved report
Set wkbWorkBook = appExcel.Workbooks.Open(strImpPath &
Click to expand...
txtClientName.Value & "_report.xls")
'Defines the worksheet
Set wksWorkSheet = wkbWorkBook.Worksheets("report")

You should then be able to appExcel.Quit after closing the workbook.
--

John Green - Excel MVP
Sydney
Australia


"Todd Waldron" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
J

John Green

Did you do "exactly" what I suggested? Did you remove all references to Excel.Application apart from the one used to define
appExcel? Are there any other code lines that refer to Excel objects that are not fully qualified by object variables?

There are a number of traps in this area so, if you can't get your code to work, please post a complete code example that exhibits
the problem.
 
T

Todd Waldron

Hi Don, thank you for your response. I tried what you
suggested but now I get a prompt to save because the app
is trying to quit before the save command and I would like
this to save automatically. Then I get a memory reference
error, and the err msg indicates that Excel.exe will be
terminated. After all this Excel is still running is tsk
mgr.

Also, I have to use "Excel.Application.Quit" or else it
will kill my Access app where this automation code is
running from.

Any other suggestions?
 
T

Todd Waldron

Hi John,

Yes, as far as I can tell. I have gone through every line
to be sure that it is qualified by the defined variables.

Below is the code. I must warn you that it's long and I'm
not sure how it is going to look once I post it. Thank
you again for your help, I really appreciate you looking
into this.

'***COM-Automation Procedure Begin***
'Open Excel file, run macro formatting actions, save,
close, quit Excel

Dim appExcel As Excel.Application
Dim wkbWorkBook As Excel.Workbook
Dim wksWorkSheet As Excel.Worksheet

Set appExcel = New Excel.Application

'Opens the report
Set wkbWorkBook = appExcel.Workbooks.Open(strImpPath &
txtClientName.Value & "_report.xls")

'Defines the worksheet
Set wksWorkSheet = wkbWorkBook.Worksheets("report")

'Set WorkSheet formatting: Page, Margins,
Header/Footer, Sheet
With wksWorkSheet.PageSetup
'Page
.Orientation = xlLandscape

'Margins
.LeftMargin = appExcel.InchesToPoints(0.5)
.RightMargin = appExcel.InchesToPoints(0.5)
.TopMargin = appExcel.InchesToPoints(0.75)
.BottomMargin = appExcel.InchesToPoints(0.75)
.HeaderMargin = appExcel.InchesToPoints(0.5)
.FooterMargin = appExcel.InchesToPoints(0.5)

'Header/Footer
.LeftHeader = txtClientName.Value & " Report"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P of &N"

'Sheet
.PrintTitleRows = "$1:$1"
.PrintGridlines = True
End With

'Set Font & Size
wksWorkSheet.Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 7
End With

'Freeze first row
wksWorkSheet.Range("B2").Select
ActiveWindow.FreezePanes = True

'Rename header rows
wksWorkSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "CRC"
wksWorkSheet.Range("B1").Select
ActiveCell.FormulaR1C1 = "RCN1"
wksWorkSheet.Range("C1").Select
ActiveCell.FormulaR1C1 = "RCN2"
wksWorkSheet.Range("D1").Select
ActiveCell.FormulaR1C1 = "OLD-RP1"
wksWorkSheet.Range("E1").Select
ActiveCell.FormulaR1C1 = "NEW-RP1"
wksWorkSheet.Range("F1").Select
ActiveCell.FormulaR1C1 = "COMP"
wksWorkSheet.Range("G1").Select
ActiveCell.FormulaR1C1 = "OLD-RP2"
wksWorkSheet.Range("H1").Select
ActiveCell.FormulaR1C1 = "NEW-RP2"
wksWorkSheet.Range("I1").Select
ActiveCell.FormulaR1C1 = "COMP"
wksWorkSheet.Range("J1").Select
ActiveCell.FormulaR1C1 = "OLD-RP3"
wksWorkSheet.Range("K1").Select
ActiveCell.FormulaR1C1 = "NEW-RP3"
wksWorkSheet.Range("L1").Select
ActiveCell.FormulaR1C1 = "COMP"
wksWorkSheet.Range("M1").Select
ActiveCell.FormulaR1C1 = "OLD-RP4"
wksWorkSheet.Range("N1").Select
ActiveCell.FormulaR1C1 = "NEW-RP4"
wksWorkSheet.Range("O1").Select
ActiveCell.FormulaR1C1 = "COMP"
wksWorkSheet.Range("P1").Select
ActiveCell.FormulaR1C1 = "OLD-RP5"
wksWorkSheet.Range("Q1").Select
ActiveCell.FormulaR1C1 = "NEW-RP5"
wksWorkSheet.Range("R1").Select
ActiveCell.FormulaR1C1 = "COMP"

'Set header row to Bold
wksWorkSheet.Rows("1:1").Select
Selection.Font.Bold = True

'Resize & autofit columns
wksWorkSheet.Columns("A:A").EntireColumn.AutoFit
wksWorkSheet.Columns("B:B").ColumnWidth = 7.45
wksWorkSheet.Columns("C:C").ColumnWidth = 8.7
wksWorkSheet.Columns("D:D").EntireColumn.AutoFit
wksWorkSheet.Columns("E:E").EntireColumn.AutoFit
wksWorkSheet.Columns("F:F").EntireColumn.AutoFit
wksWorkSheet.Columns("G:G").EntireColumn.AutoFit
wksWorkSheet.Columns("H:H").EntireColumn.AutoFit
wksWorkSheet.Columns("I:I").EntireColumn.AutoFit
wksWorkSheet.Columns("J:J").EntireColumn.AutoFit
wksWorkSheet.Columns("K:K").EntireColumn.AutoFit
wksWorkSheet.Columns("L:L").EntireColumn.AutoFit
wksWorkSheet.Columns("M:M").EntireColumn.AutoFit
wksWorkSheet.Columns("N:N").EntireColumn.AutoFit
wksWorkSheet.Columns("O:O").EntireColumn.AutoFit
wksWorkSheet.Columns("P:p").EntireColumn.AutoFit
wksWorkSheet.Columns("Q:Q").EntireColumn.AutoFit
wksWorkSheet.Columns("R:R").EntireColumn.AutoFit

'Resize & autofit rows
wksWorkSheet.Cells.Select
Selection.RowHeight = 11.25

'Apply border from row 2 to end of populated rows
wksWorkSheet.Range("D2:F" &
wksWorkSheet.UsedRange.Rows.Count).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone

'Apply border from row 2 to end of populated rows
wksWorkSheet.Range("G2:I" &
wksWorkSheet.UsedRange.Rows.Count).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone

'Apply border from row 2 to end of populated rows
wksWorkSheet.Range("J2:L" &
wksWorkSheet.UsedRange.Rows.Count).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone

'Apply border from row 2 to end of populated rows
wksWorkSheet.Range("M2:O" &
wksWorkSheet.UsedRange.Rows.Count).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone

'Apply border from row 2 to end of populated rows
wksWorkSheet.Range("P2:R" &
wksWorkSheet.UsedRange.Rows.Count).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone

'Loop through all five "COMP" columns & set all "diff"
values to Bold & Blue
Dim Cell As Range
Set Cell = wksWorkSheet.Cells

For Each Cell In wksWorkSheet.Range("F2:F" &
wksWorkSheet.UsedRange.Count)
If Cell.Value = "diff" Then
Cell.Font.Bold = True
Cell.Font.ColorIndex = 5
End If
Next

For Each Cell In wksWorkSheet.Range("I2:I" &
wksWorkSheet.UsedRange.Count)
If Cell.Value = "diff" Then
Cell.Font.Bold = True
Cell.Font.ColorIndex = 5
End If
Next

For Each Cell In wksWorkSheet.Range("L2:L" &
wksWorkSheet.UsedRange.Count)
If Cell.Value = "diff" Then
Cell.Font.Bold = True
Cell.Font.ColorIndex = 5
End If
Next

For Each Cell In wksWorkSheet.Range("O2:O" &
wksWorkSheet.UsedRange.Count)
If Cell.Value = "diff" Then
Cell.Font.Bold = True
Cell.Font.ColorIndex = 5
End If
Next

For Each Cell In wksWorkSheet.Range("R2:R" &
wksWorkSheet.UsedRange.Count)
If Cell.Value = "diff" Then
Cell.Font.Bold = True
Cell.Font.ColorIndex = 5
End If
Next

'Set the focus to "A1"
wksWorkSheet.Range("A1").Select

'Save, close, Quit
wkbWorkBook.Save
wkbWorkBook.Close
appExcel.Quit
Set appExcel = Nothing
Set wkbWorkBook = Nothing
Set wksWorkSheet = Nothing
'***COM-Automation Procedure End***

-----Original Message-----
Did you do "exactly" what I suggested? Did you remove all
Click to expand...
references to Excel.Application apart from the one used to
define
appExcel? Are there any other code lines that refer to
Click to expand...
Excel objects that are not fully qualified by object
variables?
There are a number of traps in this area so, if you can't
Click to expand...
get your code to work, please post a complete code example
that exhibits
the problem.

--

John Green - Excel MVP
Sydney
Australia


"Todd Waldron" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
J

John Green

Hi Todd,

Their might be other problems, but one problem that is immediately obvious is your use of Selection with no qualification. In
general, it is better to avoid selecting anything and it is seldom necessary to do so in Excel. However, selecting is still a valid
process and should not cause problems - but you must specify that Selection is a property of the Excel object. Instead of Selection.
use:

appExcel.Selection.

I didn't have time to read all your code so you should check for any other unqualified references as well.
 
T

Todd Waldron

Hi John,

Thank you! Everything is appropriately qualified now and
it works perfectly.

I was having problems because some of the code was
generated by recording a macro in Excel and simply
copy/pasting the results to Access. I see now were
additional qualifications were needed. I had also
forgotten to release the "Cell" variable from the looping
section.

Thanks again for all your help!

Todd Waldron
Austin, Tx.
-----Original Message-----
Hi Todd,

Their might be other problems, but one problem that is
Click to expand...
immediately obvious is your use of Selection with no
qualification. In
general, it is better to avoid selecting anything and it
Click to expand...
is seldom necessary to do so in Excel. However, selecting
is still a valid
process and should not cause problems - but you must
Click to expand...
specify that Selection is a property of the Excel object.
Instead of Selection.
use:

appExcel.Selection.

I didn't have time to read all your code so you should
Click to expand...
check for any other unqualified references as well.
--

John Green - Excel MVP
Sydney
Australia


"Todd Waldron" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access vba to Excel 2
Excel process doesn't close? 3
Save Excell file as 8
Excel process still shows in TaskManager 7
Terminating Excel 1
runtime error 2
can't get Excel to Quit 3
One Access record updated to subsequent Excel row 1

Top