| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get Excel to quit
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
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 ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
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 ... 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. . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
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 ... 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. . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
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? -----Original Message----- 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***** -- Don Guillett SalesAid Software "Todd Waldron" wrote in message ... 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. . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
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 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 ... 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. . . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
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" 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 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 ... 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. . . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get Excel to quit
Also change ActiveCell:
appExcel.ActiveCell -- 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" 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 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 ... 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. . . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel quit opening | Excel Discussion (Misc queries) | |||
| quit ie from excel macro | Excel Discussion (Misc queries) | |||
| can't quit excel | Excel Discussion (Misc queries) | |||
| Excel won't quit | Excel Discussion (Misc queries) | |||
| Excel won't quit | Excel Programming | |||
Linear Mode
