By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

loops

P: n/a
I want to loop through the recordsets and send them to excel. For each
vendorgroup in rst (there are 1 to approx 15 facilities to each vendor
group, so there would be 1 to 15 excel rows for each vendorgroup), I
want to place the records from factotrst after each corresponding
vendorgroup from rst. I am a newbie

Example
vendorgroup facility total
CAM Reese 8
CAM Solong 13
CAM Bean 5
Total 26
LYLE Notel 1
Total 1

This is what I have so far and it is not working.

How do I tell it to put a total for the vendorgroup everytime the value
of vendorgroup changes?

Thank you,

Option Compare Database
Sub updateRTVol()
On Error Resume Next

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim factotRst As Recordset
Dim VGroupRst As Recordset
Dim varVG
Dim sSQL As String

Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Dim lFld As Integer
Const cStartRow As Byte = 6
Const cStartColumn As Byte = 1

' set to break on all errors
Application.SetOption "Error Trapping", 0
'--- open the workbook
Set objXL = New Excel.Application

objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("C:\RTS.xls")
Set objSht = objWkb.Worksheets("Volumes")
Set rst = CurrentDb.OpenRecordset("SELECT VendorGroup, Procedure,
Facility, [2004RefCount], [2005Jan], [2005Feb] from tblRtVolReport
Group by VendorGroup, Procedure, facility,[2004RefCount], [2005Jan],
[2005Feb] ")
Set factotRst = CurrentDb.OpenRecordset("SELECT * from
tblProcedureTotals")
Set VGroupRst = CurrentDb.OpenRecordset("SELECT * from tblVgroup")
Dim vgroup As Variant
vgroup = Array(VGroupRst.Fields("VendorGroup").Value)
For Each vgroup In rst.Fields

rst.MoveFirst
iCol = cStartColumn
iRow = cStartRow

Do

iFld = 0

' Me.lblMsg.Caption = "Exporting record #" & lRecords & " to
SalesOutput.xls"
' Me.Repaint
'
For iCol = cStartColumn To cStartColumn + (rst.Fields.count - 1)
objSht.Cells(iRow, iCol) = rst.Fields(iFld)
iFld = iFld + 1

Next

objSht.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1

objSht.Cells(iRow + 1, 1).Value = factotRst!refcount
rst.MoveNext

Loop While rst.Fields("VendorGroup").Value =
VGroupRst.Fields("VendorGroup").Value
' factotRst.MoveFirst
'' lFld = iFld + (factotRst.Fields.count - 1)
' iRow = iRow + (rst.RecordCount - 1)
' objSht.Rows(iRow, 2).Value = factotRst!PROCEDURE
' objSht.Rows(iRow, 3).Value = factotRst![2004RefCount]
' objSht.Rows(iRow, 4).Value = factotRst![2005Jan]
' objSht.Rows(iRow, 5).Value = factotRst![2005Feb]
'

' objSht.Cells(iRow, iCol) = factotRst.Fields(lFld)
objSht.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1

Next

On Error Resume Next
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rst.Close

End Sub

Sep 5 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Br

bu************@yahoo.com wrote:
I want to loop through the recordsets and send them to excel. For each
vendorgroup in rst (there are 1 to approx 15 facilities to each vendor
group, so there would be 1 to 15 excel rows for each vendorgroup), I
want to place the records from factotrst after each corresponding
vendorgroup from rst. I am a newbie
Sorry if I'm looking at this simplistically, but wouldn't it be far
simpler to do an Access report and then export it to Excel?

Br@dley

Sep 6 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.