Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

End Excel Process called from Access VB QUICK MAX POINTS

Posted on 2006-06-18
13
Medium Priority
?
328 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 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