Export from access to excel

**MindBender77** · Feb 14 '08, 09:01 PM

You could make an append query that writes the user selected records to a temp table. To export those records to Excel, you would use "TransferSpread sheet". Both the append query and TransferSpreads heet can be add and ran from the same macro.

Hope this points you in the right direction,
Bender

**aaronward** · Feb 14 '08, 10:01 PM

I was actually looking for some sort of source code. I am rather confused on the export process. if anyone can provide any assistance, i would greatly appreciate it, thanks!

Originally posted by MindBender77

You could make an append query that writes the user selected records to a temp table. To export those records to Excel, you would use "TransferSpread sheet". Both the append query and TransferSpreads heet can be add and ran from the same macro.

Hope this points you in the right direction,
Bender

**Stewart Ross** · Feb 14 '08, 10:02 PM

Originally posted by aaronward

i have a couple of books coming on programming access - but they have not arrived as of yet. i am a fluent excel vba programmer, but i am extending out into access. I am starting with a simple export to a code generated excel file.
My database consists of 1 table (tblErrLog) with 8 fields (numEntry, txtVZID, txtAcctNum, txtRegion, txtComments, EZInfo, txtDate, txtTime)

I have the users enter data into an unbound form and submit it thru a simple sql string. At a random time, i will need to export data into a new excel file. the user will specify a data range, datStart and datEnd. then i will need to know how to use vba to have access export all records where txtDate is within the specified date range.

If anyone could shed some code onto the subject, i would greatly appreciate it, thanks!

VBA provides a specific Excel routine for copying a recordset direct to Excel from Access - CopyFromRecordS et. It does not itself copy the field names, but these are easily transferred from the query itself as shown below.

In the Access VBA code you need to create and initialise an Excel application object. Once that is done you can open a named workbook and transfer the data using the CopyFromRecordS et method.

The advantage of using this approach is that you have full programmed control of what you do with the data thereafter - saving the worksheet, formatting cells, inserting worksheets, copying existing sheets, and so on.

The following skeleton extracts show the general principles. I have not shown all Dims or definitions for the variables.

Code:

Dim ObjExcel as Excel.Application
Dim TheQuery as DAO.Recordset

Set objExcel as New Excel.Application

objExcel.Workbooks.Add ' if new workbook wanted, or
objExcel.Workbooks.Open (WorkBookPath) ' if existing workbook - WorkBookPath is name and path of workbook to open

Set TheQuery = CurrentDb.OpenRecordset(QueryName)
'QueryName is a string holding the name of the table or query to be copied.

TheQuery.MoveLast
R = TheQuery.RecordCount
' recordcount is not used in this extract, but is useful in other contexts. 
' the recordcount is not accurate unless you have moved to the end of the 
' recordset first - to allow Access to count the records properly.
' If you forget to move back to the first record the RecordSet will be at end of file
' which will terminate any loop relying on a While Not TheQuery.EOF condition

TheQuery.MoveFirst
N = TheQuery.Fields.Count

'Copy the field names to Excel
With objExcel.ActiveSheet
        For I = 0 To N - 1
            .Cells(StartRow, I + StartColumn) = TheQuery.Fields(I).Name
        Next I
End With

'Copy the querydata in the row below the field names

objExcel.Cells(StartRowNo+1,StartColumnNo)._
CopyFromRecordset TheQuery 

' StartRowNo and StartColumnNo are numbers - 1 for row 1, 2 for row 2 etc, 1 for column A, 2 for column B etc. Cells (1,1) refers to cell A1.

TheQuery.Close

objExcel.DisplayAlerts = False ' turn off user dialogue for file saving
objExcel.ActiveWorkbook.SaveAs FileName:=TheFileName ' specify the name of the file
objExcel.DisplayAlerts = True

The skeleton is taken from several different procedures and functions I use within an Excel automation class developed to handle copying of queries from Access to Excel. Normally, these are copied then autofilter applied, column widths fitted, and other processing done from within Access directly on the Excel object.

Hope this helps with ideas for what can be done.

Cheers

Stewart