Exporting to Excel

Resolved · Low Priority · Version 2003

Thu 10 Jul 2008, 15:55 Delegate David said...

David has attended:
Access VBA course

Exporting to Excel

I would like to know how to code in Access the exporting of more than one table or query into a single excel spreadhseet as multiple worksheets i.e one tab for each query/table.

Thanks,
David

Fri 11 Jul 2008, 11:48 Trainer Roy said...

RE: Exporting to Excel

One way to get data across is the Excel.Range.CopyFromRecordset method:

'Set up a Recordset based on either an SQL string or directly on a table (option adCmdTableDirect to the Open method)

Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlbook = GetObject(bookPath)
Set xlsheet = xlbook.Worksheets("WS Name")
xlsheet.Range("Data").CopyFromRecordset recset

Then just do this as many times as necessary, for different Recordsets and Ranges.

GetObject is a function in the VBA.Interaction module, which retrieves an (ActiveX) object from a specified file. In this case, the object is an Excel Workbook. The Excel application is started automatically

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds