?
Solved

End Excel Process called from Access VB QUICK MAX POINTS

Posted on 2006-06-18
13
Medium Priority
?
326 Views
Last Modified: 2011-10-03
Please tell me how to get the EXCEL.EXE process to quit after my code runs.  Here's the code:

Private Sub Pre_Conversion_Validations_Click()
Const SW_SHOWNORMAL = 1
  Dim excelApp As Excel.Application
    Dim stDocName As String
    Dim sheet As Excel.Worksheet
    Dim excelWkb As Excel.Workbook
 
    Set excelApp = CreateObject("excel.application")
    excelApp.Workbooks.OpenText filename:="c" & ":" & "\center\" & center.Column(1) & "\OUTPUT" & "\" & center.Column(0) & ".dbf"
  Set sheet = excelApp.ActiveWorkbook.Sheets(1)
  sheet.Name = center.Column(0)


and here's how i end it:

excelApp.ActiveWorkbook.SaveAs filename:="i:\common\centers2006\" & center.Column(2) & "\" & center.Column(0) & " " & center.Column(2) & " " ,FileFormat _
        :=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
excelApp.ActiveWorkbook.Close
Set excelWkb = Nothing
Set excelWkb = Nothing
Set sheet = Nothing
excelApp.Quit
Set excelApp = Nothing
End Sub


0
Comment
Question by:PAMMIEO
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16931976
Hello again

Looks alright to me, though u got one line duplicated

Set excelWkb = Nothing

You could try excelApp.Application.Quit


Just something else, I assume u are using references
in that case, u do not need to do

Set excelApp = CreateObject("excel.application")

u should really do

Set excelApp = new excel.application



the use of CreateObject is better suited if u define excellApp as an object. Doing it this way also removes the need to add references



0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16932017
Usually excel isn't quitting because it's prompting for something.  Make it visible for the time being and see if you're getting a prompt when it tries to close.  If you are getting a prompt, it'll make it easier to figure out what it wants and what you'll need to do in your code to prevent (or take care of) the prompt.

 Set excelApp = CreateObject("excel.application")
 excelApp.Visible = true
0
 

Author Comment

by:PAMMIEO
ID: 16935780
Okay, I'm here today.  Hello Rockiroads and clockwatcher.  Rockiroads, I should start an escrow account with you.  Let me try both of yours suggestions.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PAMMIEO
ID: 16935870
okay, i tried both.  When i made it visible, it wasn't prompting me for anything, in fact, it closed out and quit as it should; however, the excel.exe process is still in the task manager.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16935876
Was it in the task manager before you started?
0
 

Author Comment

by:PAMMIEO
ID: 16935880
No, it appears in there after I execute the code.
0
 

Author Comment

by:PAMMIEO
ID: 16935908
Also, everytime at the same lines I get this error:

"The remote server machine does not exist or is unavailable"

Here's where I get it everytime:

  sheet.Range("A:M").Select
  Selection.EntireColumn.AutoFit

So, I don't know if that's related to the problem.
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 2000 total points
ID: 16935975
See this article:

  http://support.microsoft.com/kb/178510/en-us/

You need to qualify the selection.  Selection is an object of application.

   excelApp.Selection.EntireColumn.AutoFit

That may get rid of that error.
0
 

Author Comment

by:PAMMIEO
ID: 16936011
You're on too something...now it's not stopping at that section anymore, it's on another section:

    sheet.Rows("1:1").Select
    Selection.Font.Bold = True
    With excelApp.Selection

so, let me go through my entire code and qualify the selections.  I'll be right back.
0
 

Author Comment

by:PAMMIEO
ID: 16936220
Okay, I called myself adapting the qualifier; however I'm still getting the process running after the code has ran.  Will you take a look at part of my code and see if you can find where I might be not qualifying:

With sheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaper11x17
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 600
End With

 
 
    sheet.Rows("1:1").Select
    excelApp.Selection.Font.Bold = True
    With excelApp.Selection
   .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
  sheet.Range("A1").Select
   excelApp.ActiveCell.EntireRow.Select
   excelApp.Selection.Insert Shift:=xlDown
   sheet.Range("E:E").Select
   excelApp.ActiveCell.EntireColumn.Delete
   sheet.Range("E:E").Select
   excelApp.ActiveCell.EntireColumn.Delete
   sheet.Range("E:E").Select
   excelApp.ActiveCell.EntireColumn.Delete
   sheet.Range("F:F").Select
   excelApp.ActiveCell.EntireColumn.Delete
   sheet.Range("F:F").Select
   excelApp.ActiveCell.EntireColumn.Delete
   sheet.Range("H:H").Select
   excelApp.ActiveCell.EntireColumn.Cut
   sheet.Range("C:C").Select
   excelApp.Selection.Insert Shift:=xlRight
   sheet.Range("G:H").Select
   excelApp.ActiveCell.EntireColumn.Cut
   sheet.Range("D:D").Select
   excelApp.Selection.Insert Shift:=xlRight
   sheet.Range("H:H").Select
   excelApp.ActiveCell.EntireColumn.Cut
   sheet.Range("G:G").Select
   excelApp.Selection.Insert Shift:=xlRight
   sheet.Range("G:G").Select
   excelApp.ActiveCell.EntireColumn.Delete
   sheet.Range("G:G").Select
   excelApp.ActiveCell.EntireColumn.Cut
   sheet.Range("E:E").Select
   excelApp.Selection.Insert Shift:=xlRight
With sheet
sheet.Range("A1").FormulaR1C1 = Wirecenter.Column(0) & " " & Wirecenter.Column(2)
sheet.Range("D1").FormulaR1C1 = "TOTAL ERRORS:"
sheet.Range("F1").FormulaR1C1 = "=COUNTA(C[-1])-2"
sheet.Range("G1").FormulaR1C1 = "PRECONVERSION"

With sheet.Cells
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
    sheet.Range("A1:B1").Select
    excelApp.Selection.Merge
    sheet.Range("D1:E1").Select
    excelApp.Selection.Merge
sheet.Range("A:H").Select
 With Selection
   .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16936570
Just glancing but the with selection:

   With Selection
      .Borders(xlDiagonalDown).LineStyle = xlNone


 At the very end is still a problem, if it's still in your code.
0
 

Author Comment

by:PAMMIEO
ID: 16936790
HIP HIP HORAYYYYYYY, HIP HIP HORAYYYYYYY FOR CLOCKWATCHER.  Everythings working GREAT after replacing "anything" with the word "selection" with the qualifier to look like "excelApp.selection" --- No more errors period AND the EXCEL.EXE process ends properly.

Many thanks, and here are your points.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16937767
LOL... glad to have been able to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question