Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

End Excel Process called from Access VB QUICK MAX POINTS

Posted on 2006-06-18
13
Medium Priority
?
330 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

581 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