Solved

End Excel Process called from Access VB QUICK MAX POINTS

Posted on 2006-06-18
13
323 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
Industry Leaders: 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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

726 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