Microsoft Access VBA Macro Programming

Transferring Individual Numbers to Excel

The preceding examples are very good for transferring large chunks of data into Excel, but

you may encounter a situation where you only want to transfer specific numbers to Excel.

You can do this by iterating through theRecordsetobject instead of copying and pasting it.

The reason for this may be that you do not want the data put into the spreadsheet in one

contiguous chunk, and you want more control over where the data will appear.

Insert the following VBA code into the module you created earlier in this chapter. Do not

forget to make sure you have added a reference to the Excel Object Model, as shown on the

first page of this chapter.

Sub CreateSpreadsheet 2 ()

Dim RecSet As Recordset

Dim MyExcel As Excel.Application

Dim MyBook As Workbook

Dim MySheet As Worksheet

Dim strTemp As String

Dim Coun As Long

Dim Coun 1 As Long

Set MyExcel = CreateObject("Excel.Application")

Set MyBook = MyExcel.Workbooks.Add

Set MySheet = MyBook.Worksheets( 1 )

strTemp = "SELECT Products.[Product Name], Sum([Unit

Cost]*[quantity]) AS Cost "

strTemp = strTemp & "FROM Products INNER JOIN [Purchase Order

Details] "

strTemp = strTemp & "ON Products.ID = [Purchase Order

Details].[Product ID] "

strTemp = strTemp & "GROUP BY Products.[Product Name];"

Set RecSet = CurrentDb.OpenRecordset(strTemp)

Do Until RecSet.EOF

Coun = Coun + 1

MySheet.Range("a" & Coun).Value = RecSet![Product Name]

MySheet.Range("b" & Coun).Value = RecSet!Cost

Coun 1 = Coun 1 + 1

If Coun 1 = 5 Then

Coun = Coun + 2

Coun 1 = 0

End If

RecSet.MoveNext

Loop

332 Microsoft Access 2010 VBA Macro Programming