Sorting data in Excel from Access

[GunVi]

Hi,

I am exporting data (5 queries) from Access into an existing Excel spreadsheet (5 worksheets) using CopyFromRecordset. This, however, does not preserve the sorting order of the data. Thus, I have to sort the data in the spreadsheet using code. This code works fine for the first worksheet but fails on the subsequent worksheet with "Method Range of object _Global failed" error. Can somebody please let me know why this error occurs and how I can rectify it? The code that I am using is below.

Private Sub Command37_Click()

a = ExportResultstoExcel("Qry1", 1)
b = ExportResultstoExcel("Qry2", 2)
c = ExportResultstoExcel("Qry3", 3)
d = ExportResultstoExcel("Qry4", 4)
e = ExportResultstoExcel("Qry5", 5)

End Sub

Function ExportResultstoExcel(qryName As String, Count As Integer)


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim lastrow As Integer
Dim lastcolumn As Integer
Dim intStart As Integer
Dim appXL As Excel.Application


Set dbs = CurrentDb
Set appXL = New Excel.Application

'Select the data you want to output
Set qdf = dbs.QueryDefs(qryName)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset


'Open the receiving book and activate the required sheet
appXL.Workbooks.Open "C:\Documents and Settings\DataSpreadsheet.xlsx"

appXL.Worksheets(Count).Select

'clear contents in all cells except the header row
lastrow = appXL.ActiveSheet.UsedRange.Rows.Count
lastcolumn = appXL.ActiveSheet.UsedRange.Columns.Count
With appXL.ActiveSheet
.Range(.Cells(2, 1), .Cells(lastrow, lastcolumn)).ClearContents
End With

'copy records from Access to Worksheet
appXL.ActiveSheet.Range("A2").CopyFromRecordset rst

'sort data in excel

appXL.Worksheets(Count).Sort.SortFields.Clear
appXL.Worksheets(Count).Sort.SortFields.Add Key:=appXL.Worksheets(Count).Range("D2" & lastrow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With appXL.Worksheets(Count).Sort
.SetRange Range("A1" & lastrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

lastrow = 0
lastcolumn = 0

appXL.ActiveWorkbook.Save
appXL.Workbooks.Close
appXL.Quit

Set appXL = Nothing
rst.Close
Set rst = Nothing

End Function

 

[SkipVought]

hi,

    dim ws as excel.worksheet

    set ws = appXL.Worksheets(Count) 

    With ws.Sort
       .SortFields.Clear

       .SortFields.Add _
            Key:=ws.Range("D2"), _
             SortOn:=xlSortOnValues, _
             Order:=xlAscending, _
             DataOption:=xlSortNormal

        .SetRange ws.Range("A2").currentregion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

does not preserve the sorting order of the data
Really ?
the ORDER BY clause of the query isn't preserved ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886