loop

[tuktuk]

can anyone help with why i am not looping through the qry results? i have a a qry that pull a list of data......startign with the first result i want to open an excel file and then paste that data into a cell adn then save the file specific to that data with the date.

as is step through it does everything i want for the first record but is not Moving to the second entry, third entry etc.

Thanks
tuk

here is the code:

Public Function CodePassThru()
DoCmd.SetWarnings False

Dim rsCC As Recordset
'Dim X As Integer
'Dim SQLT As String
Dim strCC As String

Set rsCC = CurrentDb.OpenRecordset("qry_CC_List")

With rsCC
    .MoveFirst
    strCC = rsCC("CostCenters")
    Do While Not .EOF

Const FILE_PATH As String = "G:\tuktuk\"

Dim FullPath As String
    strFullPath = FILE_PATH
    
Set objXL = Excel.Application
        With objXL.Application
        .Visible = True
        .Workbooks.Open "G:\tuktuk\Master_Template.xls"
        .Worksheets("Options").Activate
    
    objXL.Cells(3, 2).Value = strCC & ""
        
Dim month
        month = Format(Now(), "MMM-YY")
        
        .ActiveWorkbook.SaveAs (strFullPath & "Marketing Budget " & month & " - " & strCC & ".xls")
        .ActiveWorkbook.Close
        End With
    rsCC.MoveNext

    Loop
  End With

Set rsCC = Nothing

DoCmd.SetWarnings True
End Function

 

[tuktuk]

ahhh ifigured it out:

Public Function CodePassThru()
Dim rsCC As Recordset
Dim strCC As String
Dim flds As DAO.Fields
Dim fld As DAO.Field

DoCmd.SetWarnings False
Set rsCC = CurrentDb.OpenRecordset("qry_CC_List")
Set flds = rsCC.Fields
Set fld = flds("CostCenters")

With rsCC
    .MoveFirst
    Do While Not .EOF
    strCC = fld
 
Const FILE_PATH As String = "G:\tuktuk\"

Dim FullPath As String
    strFullPath = FILE_PATH

    Set objXL = Excel.Application
        With objXL.Application
        .Visible = True
        .Workbooks.Open "G:\tuktuk\Master_Template.xls"
        .Worksheets("Options").Activate
    
    objXL.Cells(3, 2).Value = strCC & ""

Dim strMonth
        strMonth = Format(Now(), "MMM-YY")
        .ActiveWorkbook.SaveAs (strFullPath & "Marketing Budget " & strMonth & " - " & strCC & ".xls")
        .ActiveWorkbook.Close
        End With
    rsCC.MoveNext
Loop
  End With

Set rsCC = Nothing


DoCmd.SetWarnings True
End Function