Results 1 to 2 of 2
  1. #1
    GunVi is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    1

    Exporting and sorting data in Excel from Access - Problem

    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:D" & lastrow) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With appXL.Worksheets(Count).Sort
    .SetRange Range("A1:D" & 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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,017
    You need to debug. Review the link at bottom of my post for a good summary of debugging techniques.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting more than 65000 Access rows to Excel
    By TonyBender in forum Import/Export Data
    Replies: 8
    Last Post: 08-22-2013, 04:56 PM
  2. Replies: 1
    Last Post: 08-22-2011, 02:09 PM
  3. Exporting to Excel Problem
    By octu in forum Import/Export Data
    Replies: 1
    Last Post: 04-14-2010, 11:28 PM
  4. Exporting Access queries to Excel
    By dbDamo in forum Import/Export Data
    Replies: 2
    Last Post: 09-22-2009, 01:42 AM
  5. Replies: 0
    Last Post: 06-05-2009, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums