MS Access VBA Export Query results

Question

Hi all,

I need help coming up with a method to allow a user to export a query's results to an xls file on a button click event. I've tried using an Output To macro, but it doesn't work for a query containing 30,000+ records.

Thanks in advance

Answer 1

Can you use VBA?

Intellisense will help you, but get started with:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "my_query_name", "C:\myfilename.xls"

Note: you may have a different Excel version "my_query_name" is the name of your query or table you'll need to set the file location to the appropriate location\name .extension

More Info: http://msdn.microsoft.com/en-us/library/bb214134.aspx

Answer 2

You might want to consider using Automation to create an Excel spreadsheet and populate it on your own rather than using a macro.

Here's a function I have used in the past to do just that.

Public Function ExportToExcel(FileToCreate As String, ByRef rst As ADODB.Recordset)
'Parms:     FileToCreate - Full path and file name to Excel spreadsheet to create
'           rst - Populated ADO recordset to export

On Error GoTo Err_Handler

Dim objExcel As Object
Dim objBook As Object
Dim objSheet As Object

'Create a new excel workbook; use late binding to prevent issues with different versions of Excel being
'installed on dev machine vs user machine
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Add

'Hide the workbook temporarily from the user
objExcel.Visible = False

objBook.SaveAs (FileToCreate)

'Remove Worksheets so we're left with just one in the Workbook for starters
Do Until objBook.Worksheets.Count = 1
   Set objSheet = objBook.Worksheets(objBook.Worksheets.Count - 1)
   objSheet.Delete
Loop

Set objSheet = objBook.Worksheets(1)

rst.MoveFirst

'Use CopyFromRecordset method as this is faster than writing data one row at a time
objSheet.Range("A1").CopyFromRecordset rst

'The UsedRange.Rows.Count property can be used to identify the last row of actual data in the spreadsheet
'This is sometimes useful if you need to add a summary row or otherwise manipulate the data
'Dim lngUsedRange As Long
'lngUsedRange = objSheet.UsedRange.Rows.Count

'Save the spreadsheet
objBook.Save

objExcel.Visible = True

ExportToExcel = True

Err_Handler:
   Set objSheet = Nothing
   Set objBook = Nothing
   Set objExcel = Nothing
   DoCmd.Hourglass False

   If Err.Number <> 0 Then
      Err.Raise Err.Number, Err.Source, Err.Description
   End If
End Function