L
LHC01
I am working with Office XP:
- 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
Best Regards
- 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
Best Regards