CODEWITHSUNDEEP
excelms-accessvba
user594082
user594082

Reputation: 1

Create Excel spreadsheet using the criteria from another table in MS Access VBA

I need some help with Ms Access VBA and Excel. I have two tables:

t_Customer

- CustomerID
- CustomerName
- CustomerAccnt

t_Orders

- OrderID
- Desc
- PartNo
- Amount
- Disc
- Date
- CustomerID

I need to create Excel file for each customer, including the orders details and customer details as well. Each file should be called "Transactions for .xls" My thought is to take each customer from the customer table in a loop, match the information from the orders table, create a temporary table, and export to Excel.

I don’t know how to do this, any help or advice will be appreciated.

Upvotes: 0

Views: 932

Answers (1)

Kevin Ross
Kevin Ross

Reputation: 7215

You are on the right lines. I would do something like this (presto code but should work)

Dim Db as DAO.Database
Dim Rst as DAO.Recordset

Set Rst = Db.OpenRecordset ("SELECT Customer_ID, Customer_name FROM t_Customer")
With Rst
    Do Until .Eof
        Db.CreateQueryDef "qryTemp_query", _
            "SELECT blah FROM t_Order WHERE Customer_ID=" & !Customer_ID.Value
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
            "qryTemp_query", "C:\Transactions for " & !Customer_name.Value
        Db.QueryDefs.Delete "qryTemp_query"
        .MoveNext
    Loop
End With
Rst.Close
Db.Close

Upvotes: 3

Related Questions