• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Excel proccess running after export from Access

Just curious as to what I am doing wrong here. I have some code that exports a table to Excel and then manipulates it with some code. After everything is done I go in and look at the task manager and there is always an Excel process running. Here is my code:

 
DoCmd.OutputTo acOutputForm, "TESTTABLEFORMEXPORT", acFormatXLS, "c:\TestForm2.xls", False
    Dim xlObj As Object, xlFile As String
    xlFile = "c:\TestForm2.xls"
    Set xlObj = CreateObject("excel.application")
    xlObj.Workbooks.Open xlFile
    With xlObj
   
    
        .Columns("C:C").Select
    .Selection.FormatConditions.AddColorScale ColorScaleType:=3
    .Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    .Selection.FormatConditions(1).ColorScaleCriteria(1).type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    .Selection.FormatConditions(1).ColorScaleCriteria(2).type = _
        xlConditionValuePercentile
    .Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    .Selection.FormatConditions(1).ColorScaleCriteria(3).type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    .Cells.Select
    .Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True

    End With
    
     ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    xlObj.Quit

    Set xlObj = Nothing

Open in new window


I tried adding this:

xlObj.Workbooks.Close xlFile

But it said 'Too few arguments'

What am I missing here? Thanks for any help you can give me.
0
G Scott
Asked:
G Scott
  • 4
  • 3
1 Solution
 
YohanFCommented:
try following:

xlObj.ActiveWorkbook.Save
xlObj.Quit
0
 
G ScottAuthor Commented:
Hey YohanF, tried your suggestion and the Excel process is still running:

 Still running
0
 
YohanFCommented:
Hi, I found the problem. Try this and see. This will assign the work book you open to a variable and save and close that instead of saving and closing the active sheet. This seems to have done the trick.

Before executing make sure you kill off any existing EXCEL processes.

Dim xlObj As Object
    Dim xlFile As String
    Dim xlwrkbk As Excel.Workbook
    
    xlFile = "c:\TestForm2.xls"
    Set xlObj = CreateObject("excel.application")
    
    xlObj.Workbooks.Open xlFile
    Set xlwrkbk = xlObj.Workbooks.Open(xlFile)
    
    With xlObj
        .Columns("C:C").Select
    .Selection.FormatConditions.AddColorScale ColorScaleType:=3
    .Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    .Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    .Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    .Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    .Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    .Cells.Select
    .Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True

    End With
    
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    
    xlwrkbk.Save
    xlwrkbk.Close
    
    xlObj.Quit

    Set xlObj = Nothing

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
G ScottAuthor Commented:
Hmmm..line 14:

.Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority

is giving me a 462 error. 'Remote server machine does not exist or is unavailable"

Any idea??
0
 
G ScottAuthor Commented:
Ok..now the same line is giving me a '91' error:

Object variable or With block variable not set.

What the heck? That is strange...to me anyway.
0
 
YohanFCommented:
I saw you have opened a different thread for this. I am not very familiar with conditional formatting with VBA..
0
 
G ScottAuthor Commented:
Oh, yeah, that was sort of a separate issue. Yours is working by the way. For some reason, there were two Excel processes running. I killed the one and missed the other.

Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now