Can't get Excel to quit
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
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.
--
John Green - Excel MVP
Sydney
Australia
"Todd Waldron" wrote in
message ...
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"\0
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 = xlCotinuous
.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
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.
--
John Green - Excel MVP
Sydney
Australia
"Todd Waldron" wrote in
message ...
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. ??? :o(
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 &
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"
wrote in
message news:01a201c3b06f$bb159c80
...
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.
.
.
.
|