Problem with an Excel application object from Access

[EBGreen]

I am having two problems with a sub in Access. What it should do:
1) Open an existing Excel spreadsheet called export.
2) Delete the existing data from the last time it ran if there is any
3) Enter the new data from a query
4) Create a named range for the data it just entered.
5) Save the spreadsheet
6) Close the excel app

What really happens:
1) works
2) Doesn't work. See 4)
3) works
4) It is not creating the named range
5) works
6) The excel app is generating a stranded process that maintains a file lock on the spreadsheet instead of just dieing gracefully

Here is my code:

Private Sub btnExport_Click()
    On Error GoTo Err_Export_Data_to_Excel_Click
    
    Dim strQueryName As String
    Dim strWorkingDir As String
    Dim strFileName As String
    Dim db As DAO.Database
    Dim oExcel As New Excel.Application
    Dim oWB As Object
    Dim oWS As Object
    Dim rs As DAO.Recordset

    LogLine "Starting the export"
    
    Set db = CurrentDb
    
    strWorkingDir = "\\SomeServer\SomeShare\"
    strFileName = strWorkingDir & "Export.xls"
    LogLine "Creating Excel Object"
    LogLine "Opening workbook " & strFileName
    Set oWB = oExcel.Workbooks.Open(strFileName)
    LogLine "Setting the active sheet"
    Set oWS = oExcel.ActiveWorkbook.Worksheets(1)
    LogLine "Activating the sheet"
    oWS.Activate
    
    'Get rid of existing data
    On Error Resume Next
    oExcel.Goto ("Export")
    Selection.ClearContents
    oWB.Names("Export").Delete
    On Error GoTo Err_Export_Data_to_Excel_Click
    
    strQueryName = "MasterQuery"
    
    LogLine "Getting Query " & strQueryName
    Set rs = db.OpenRecordSet(strQueryName, dbOpenDynaSet)
    nRecordCount = 2
    LogLine "Beginning to output" & CStr(rs.RecordCount)
    
    With rs
        Do While Not .EOF
            nFieldCount = 1
            For Each myField In .Fields
                LogLine vbTab & "Outputting " & myField.Value & " to (" & nRecordCount & "," & nFieldCount & ")"
                oWS.cells(nRecordCount, nFieldCount).Value = Chr(39) & myField.Value
                nFieldCount = nFieldCount + 1
            Next
            .MoveNext
            
            nRecordCount = nRecordCount + 1
        Loop
    End With
            
    
    LogLine "Creating Range Name = Export"
    LogLine vbTab & "RefersTo:=" & "sheet1!$a$1:$" & Chr(nFieldCount + 97) & "$" & CStr(nRecordCount)
    oWB.Names.Add Name:="Export", _
                  RefersTo:="sheet1!$a$1:$" & Chr(nFieldCount + 97) & "$" & CStr(nRecordCount)
    LogLine Err.Description
    LogLine "Saving Excel"
    oExcel.ActiveWorkbook.Save
    LogLine "Quitting Excel"
    oExcel.Quit
    
Exit_Export_Data_to_Excel_Click:
        Exit Sub
    
Err_Export_Data_to_Excel_Click:
        MsgBox Err.Description
        Resume Exit_Export_Data_to_Excel_Click
End Sub

 

[Nikita6003]

Hiya,

There are several ways to assign names to a range. As you're using the Cells property to write your data to ythe worksheet, why not use this to set the range name as well?

oWS.Range(oWS.Cells(1, 1), oWS.Cells(nRecordCount, nFieldCount)).Name = "ExportToo"

Once the range has been set correctly, the ClearContents method (step 2) should work ok - but try using oWS.Range("Export").ClearContents instead of the 2 lines you're currently using

HTH

Cheers
Nikki
Look, mommy, I'm flying!