Excel Not Unloading

[sgfromny]

Im running this series of code, excel starts and quits as expected, athough its process remains in memory.

Am I not closing it out properly? Please help!

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp As Object
Dim path As String
Dim objXl As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim RCount As Integer


DoCmd.SetWarnings False
DoCmd.OpenQuery "qryschedulegrabber"
DoCmd.SetWarnings True

path = "f:\forecast\stndrds\Schedule.xls"
Set db = CurrentDb
Set rs = db.OpenRecordset("schedule")

rs.MoveLast
rs.MoveFirst
RCount = rs.RecordCount
Set objXl = New Excel.Application
RCount = RCount + 7
With rs
.Edit
With objXl
objXl.Visible = True
Set objWkb = .Workbooks.Open(path)
Worksheets("Rawdata").Activate
For Counter = 6 To RCount Step 1
Index = Counter
.Range("A" & Index).Value = rs![Job Number]
.Range("B" & Index).Value = rs![Type]
.Range("C" & Index).Value = rs![RemainingPDR]
.Range("f" & Index).Value = rs![Shop Progress]
If rs![Type] = "A" Then .Range("D" & Index).Value = rs![Actual Production]
If rs![Type] = "P" Then .Range("D" & Index).Value = rs![Projected Construction]

.Range("E" & Index).Value = rs![EndDate]
rs.MoveNext
If rs.EOF Then
objWkb.Save
.Application.Quit
Set objWkb = Nothing
Set objXl = Nothing
Set objSht = Nothing
MsgBox "Data Transfer Complete!" & vbCrLf & "Click OK to Open Spreadsheet", vbInformation, "Complete!"
Shell "C:\program files\Microsoft office\office\excel.exe f:\forecast\stndrds\schedule.xls", vbMaximizedFocus

Exit Sub
End If
Next Counter

rs.Close
End With
End With
End Sub

 

[cLFlaVA]

Try moving this line:
[tt]Set objXl = Nothing[/tt]
to outside the inner with. So, something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp As Object
Dim path As String
Dim objXl As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim RCount As Integer


DoCmd.SetWarnings False
DoCmd.OpenQuery "qryschedulegrabber"
DoCmd.SetWarnings True

path = "f:\forecast\stndrds\Schedule.xls"
Set db = CurrentDb
Set rs = db.OpenRecordset("schedule")

rs.MoveLast
rs.MoveFirst
RCount = rs.RecordCount
Set objXl = New Excel.Application
RCount = RCount + 7
With rs
    .Edit
    With objXl
        objXl.Visible = True
        Set objWkb = .Workbooks.Open(path)
        Worksheets("Rawdata").Activate
        For Counter = 6 To RCount Step 1
                        Index = Counter
            .Range("A" & Index).Value = rs![Job Number]
            .Range("B" & Index).Value = rs![Type]
            .Range("C" & Index).Value = rs![RemainingPDR]
            .Range("f" & Index).Value = rs![Shop Progress]
            If rs![Type] = "A" Then .Range("D" & Index).Value = rs![Actual Production]
            If rs![Type] = "P" Then .Range("D" & Index).Value = rs![Projected Construction]
            
            .Range("E" & Index).Value = rs![EndDate]
    rs.MoveNext
    If rs.EOF Then
    objWkb.Save
    .Application.Quit
    Set objWkb = Nothing
    Set objSht = Nothing
    MsgBox "Data Transfer Complete!" & vbCrLf & "Click OK to Open Spreadsheet", vbInformation, "Complete!"
    Shell "C:\program files\Microsoft office\office\excel.exe f:\forecast\stndrds\schedule.xls", vbMaximizedFocus
    
    Exit Sub
    End If
    Next Counter
    
    rs.Close
    End With
    [red]Set objXl = Nothing[/red]
End With
End Sub

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[sgfromny]

That didnt work. Any Other Ideas?

 

[RoyVidar]

I'd consider closing the workbook prior to the quit. I browsed thru the code previoulsy, but couldn't find any implicit/unqualified referencing, but there's one:

[tt]Worksheets("Rawdata").Activate[/tt]

Need to be qualified thru the workbook object.

Sometimes using with blocks when automating Excel can cause such (the with rs block, is really only used for the .edit, too, so can be omitted).

Roy-Vidar

 

[sgfromny]

Can you help me understand what to do?, How do I close the workbook? How Do I qualify the worksheet line?

 

[RoyVidar]

I believe your workbook object, is the one called objWkb, issue the .Close method on it, could probably look something like this.

[tt]objWkb.close[/tt]

The workbook object, still being objWkb, used to qualify the line

[tt]Worksheets("Rawdata").Activate[/tt]

Would then probably look like this:

[tt]objWkb.Worksheets("Rawdata").Activate[/tt]

Roy-Vidar

 

[sgfromny]

Thanks,

Its still not working,..