?
Solved

Closing Excel through VB (including instance in Task manager

Posted on 2003-02-18
2
Medium Priority
?
290 Views
Last Modified: 2013-12-26
Hi all,

I have opened excel through code, pasted some values into the cells from a recordset, saved and closed the application, yet in Task Manager the instance of excel is still there.  I have looked everywhere but cannot find a solution, although i have found people with similar problems.  Attached is my code:

Sub Export_Excel()
    Dim recArray            As Variant
    Dim fldCount            As Integer
    Dim recCount            As Long
    Dim iCol                As Integer
    Dim iRow                As Integer
    Dim xlApp               As Excel.Application
    Dim xlWb                As Excel.Workbook
    Dim xlWs                As Excel.Worksheet
   
    With rstPhoenix
        .Open strSQL, sADOConnect, adOpenDynamic, adLockOptimistic, adCmdText
       
        Set xlApp = New Excel.Application
        Set xlWb = Excel.Workbooks.Add
        Set xlWs = Excel.Worksheets("Sheet1")
       
     xlApp.Visible = False
     xlApp.UserControl = False
       
        fldCount = rstPhoenix.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rstPhoenix.Fields(iCol - 1).Name
        Next
       
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            xlWs.Cells(2, 1).CopyFromRecordset rstPhoenix
           
        Else
            'EXCEL 97 or earlier: Use GetRows then copy array to Excel
           
            recArray = rstPhoenix.GetRows
           
            recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
           
            For iCol = 0 To fldCount - 1
                For iRow = 0 To recCount - 1
                    If IsDate(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'next record
            Next iCol 'next field
               
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
       
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
       
        xlWb.SaveAs (strFileName)
    End With
   
    'Cleans up the sub
    recArray = Null
    fldCount = 0
    recCount = 0
    iCol = 0
    iRow = 0
    blnDBConnect = False
    strSQL = ""
    sADOConnect = ""
    Set rstPhoenix = Nothing
    Set xlWs = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Sub
0
Comment
Question by:sjeffress
2 Comments
 
LVL 12

Accepted Solution

by:
jgv earned 300 total points
ID: 7979300
Try changing this...
Set xlWb = Excel.Workbooks.Add
Set xlWs = Excel.Worksheets("Sheet1")

to this...
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
0
 
LVL 1

Author Comment

by:sjeffress
ID: 7990277
Thanks for that.  It was some bad coding.  I had got some of the code from the internet and developed some myself, which would have caused the error.

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

621 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