Extract the value only for few columns dynamically from result set

[riaarora]

Hi All,

I have table with following Columns:

Banker
Status
Week1
Week2
Week3
Week4
......
Week48
Week49
Week50
Budget
YearMonth

I need to extract the value only for few columns dynamically from the result set. I have written the following code:

    i = 0
    strColumnsSQL = "Week" & CStr(gsReportingWeekNo)
    
    Do While i < 4
        i = i + 1
        strColumnsSQL = strColumnsSQL & ",Week" & CStr(gsReportingWeekNo) - i
    Loop

    strSelectSQL = "SELECT tbl_Consol_Weekly_NNA.[Banker], tbl_Consol_Weekly_NNA.Exclude, " & _
                    strColumnsSQL & _
                    " FROM tbl_Consol_Weekly_NNA " & _
                    " INNER JOIN tbl_Mapping ON tbl_Consol_Weekly_NNA.[Banker] = tbl_Mapping.[Revenue Producer]" & _
                    " WHERE [YearMonthWeek] = '" & txtReportingMonth & CStr(gsReportingWeekNo) & "'" & _
                    " AND " & strWhereCondition & "= '" & sTeamRegionName & "' " & _
                    " AND tbl_Mapping.[Exclude] = False " & _
                    " ORDER BY " & strWhereCondition & ", tbl_Consol_Weekly_NNA.[Private Banker]"
    Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)
    
    i = 0
    ColumnStart = ColumnStart + 1
    Do While i < 4
        i = i + 1
        objXLSheet.Cells(RowStart, ColumnStart) = "Week " & CStr(gsReportingWeekNo) - i
        ColumnStart = ColumnStart + 1
    Loop

    rsTmp.MoveLast
    recount = rsTmp.RecordCount
        
    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp.Fields(0)
        ColumnStart = ColumnStart + 1
        i = 0
        Do While i < 4
            i = i + 1
            objXLSheet.Cells(RowStart, ColumnStart) = " & rsTmp.Fields(" & i & ") &" '--> Not Returning the value            
ColumnStart = ColumnStart + 1
        Loop
        ColumnStart = ColumnStart + 1
        RowStart = RowStart + 1
        If rsTmp.EOF Then Exit For
        rsTmp.MoveNext
    Next count

Below line is not returing the value

objXLSheet.Cells(RowStart, ColumnStart) = " & rsTmp.Fields(" & i & ") &"

Please help me to correct this code...

[westconn1]

Below line is not returing the value

what is it returning?

[riaarora]

Hi Pete,

It's writing " & rsTmp.Fields(1) &" in the cell instead of value e.g. 2309 etc.

[westconn1]

try like

objXLSheet.Cells(RowStart, ColumnStart) =  rsTmp.Fields( i)