Results 1 to 2 of 2
2003-03-24, 11:04 #1
- Join Date
- Mar 2002
- Kent, Kent, United Kingdom
- Thanked 0 Times in 0 Posts
Automate Copy and Paste of Table to Excel (Access 97)
Has anybody got any code kicking about that will (Effectively) copy and paste a table (or query) from and Access database to an Excel workbook?
I have some code that does this for small recordsets quite well :
(Should be easy to figure out what the passed variables are)
Public Function ExportQuerytoExcel(ByRef objexcel, ByRef db, ByRef rstform, placeY As Integer, placeX As Integer)
Dim Records, RowCol As Long
Records = rstform.RecordCount
RowCol = .Fields.Count
.Visible = True
For Records = 1 To rstform.RecordCount
For RowCol = 1 To rstform.Fields.Count
.Cells(placeX + (Records - 1), placeY + (RowCol - 1)).value = rstform.Fields(RowCol - 1)
'.value could be fields.name?!?!
If Records = 1 Then .Cells(placeX - 1, placeY + (RowCol - 1)).value = rstform.Fields(RowCol - 1).Name
But this doesn't handle recordsets that are 100 records plus very well.... Too slow.
Any thoughts? - Mods, would you prefer I post in another forum? (I thought Access was my best bet rather than VB)
The solution needs to be available as automation - it's to automatically generate lists in Excel from query results.
2003-03-24, 12:26 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Automate Copy and Paste of Table to Excel (Access 97)
1. From Access
The DoCmd object in Access has a TransferSpreadsheet method that can be used to export a table or a saved query to an Excel workbook. Take a look at the Access online help to learn about the details.
The data will be exported to a worksheet in the specified workbook with the name of the table/query, and start in cell A1. You could create a relatively simple Excel macro to move the exported data to another location.
2. From Excel
The Range object in Excel has a CopyFromRecordset methid that can be used to import a DAO recordset. Look up CopyFromRecordset in the Excel VBA online help
for the details. The recordset can be created from a table, a saved query or an SQL string.
In your case, you would use something like
Dim iCols As Integer
For iCols = 0 to rstForm.Fields.Count - 1
.Cells(placeX, placeY + iCols).Value = rstForm.Fields(iCols).Name
.Cells(placeX + 1, placeY).CopyFromRecordset(rstForm)
Note that the Fields collection is zero-based: the first field has index 0, the last has index Fields.Count - 1