Export from access to excel

[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!

[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 "TransferSpreadsheet". Both the append query and TransferSpreadsheet can be add and ran from the same macro.

Hope this points you in the right direction,
Bender

[aaronward]

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!

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 "TransferSpreadsheet". Both the append query and TransferSpreadsheet can be add and ran from the same macro.

Hope this points you in the right direction,
Bender

[Stewart Ross]

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 - CopyFromRecordSet. 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 CopyFromRecordSet 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.

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