ExcelBanter - View Single Post

Jamie Collins

(LHC01) wrote ...

- Access database named Sales.mdb (at c:\data\) with a table tblSales
table tblSales has the following structure (about 70 fields):
RcdInd Customer Field1 Field2 Field3 Field4 Field5 Field6 ..

It contains data for 200 Customers and each Customer has 4 records
(RcdInd = 01, 02, 03 and 04)

- Excel workbook SalesD.xls (at c:\data\xlslib\)
This workbook is being used as a template i.e. it is formatted to
reflect data for each Customer.

I have started to write VBA in Excel using DAO to connect to Access

The following is an excerpt from my code:

' Open workbook SalesD.xls
ChDir "C:\DATA\XLSLIB\"
Workbooks.Open FileName:="SalesD.xls"

' Select the MS Access Database that houses the data
Dim DB As Database, RS As Recordset
' Open the Sales.mdb database
Set DB = OpenDatabase("c:\data\sales.mdb")

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field1, Field2, Field3 " & _
" FROM tblSales " & _
" WHERE RcdInd = '01' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell C2
[C2].CopyFromRecordset RS

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field6, Field7, Field8 " & _
" FROM tblSales " & _
" WHERE RcdInd = '02' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell F28
[F28].CopyFromRecordset RS
.................................................. ..........
.................................................. ..........
This code works fine; however, it is cumbersome as the Customer number
must be changed each time.

Is it possible to do the following? (if so, how?)

1. Perform the above for each Customer, save the output as
a new workbook.
2. Continue to process each Customer as above.

Something like:
For i = 1 to 200
code (SQL and CopyFromRecordset routines) .......
Next

As an alternative to using CopyFromRecordset, consider using either
the INSERT INTO..SELECT syntax (where the Excel table already exisits)
or the SELECT..INTO syntax (creates the Excel table and even creates
the workbook if it doesn't already exist). Such Jet syntax operates on
closed workbooks and consequently executes much faster.

I am working with Office XP

As an alternative to using DAO, consider using the more modern ADO.
You'll find more people in these ngs use ADO and find more support
generally.

Jamie.

--