Solved

End Excel Process called from Access VB QUICK MAX POINTS

Posted on 2006-06-18
13
317 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

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