Remove records with 0 value

[ria.arora]

Hi All,
I am writing data from Access to excel. There are few records with 0 values. How to remove those records?
Below the code used to export data from Access to Excel.

Dim TempString As String
    Set objXLApp = CreateObject("Excel.Application")
    wb = "F:\MyDoc\Weekly MI Automation\" & sTeamRegionName & Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4) & "_" & Left(Time, 2) & Mid(Time, 4, 2) & ".xlsx"
    
    Set objXLWorkbook = objXLApp.Workbooks.Add 'Will Create a new workbook
    Set objXLSheet = objXLWorkbook.Worksheets(1) 'Will create a new worksheet
    'objXLSheet.Name = sTeamRegionName 'Rename the worksheet
    'Set Wkb = AppExcel.Workbooks.Open(excelname)
    
    objXLApp.Visible = True 'Makes the spreasheet visible. False will let you open it behind the scenes
    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]
        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count
    rsTmp.Close
    Set rsTmp = Nothing
    
    'For double top line and single bottom line for Subtotal
    TempString = "A" & RowStart & ":" & "T" & RowStart
    boldFontSize (TempString)
    applyDoubleTotalLine (TempString)
objXLSheet.range("RowEnd").Select
    objXLSheet.ActiveCell.Formula = "= SUBTOTAL(101, B" & RowEnd & ":B" & recount + 6 & ")"

 

[pbaldy]

Simplest would probably be to exclude them from rsTmp.

 

[ria.arora]

Hi Paul,

How to remove from the rsTmp? rsTmp contains full records with 20 columns. Do I need each columns one by one or there is some syntex to check sum of all the columns?

Can you please send me the syntex?

Thanks

 

[pbaldy]

You didn't show how the recordset was populated. Are you trying to exclude records with a zero value in a specific field? Any field?

 

[G37Sam]

Before going into the details of your code, I spotted the following cause for errors:

wb = "F:\MyDoc\Weekly MI Automation\" & sTeamRegionName & Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4) & "_" & Left(Time, 2) & Mid(Time, 4, 2) & ".xlsx"

Left, Mid & Right are used for string operations, if you want to extract the day, month & year of a certain use the day(), month() & year() functions instead.

 

[Pat Hartman]

Is there some reason you didn't want to use the TransferSpreadsheet Method? It lets you export an entire table or query with a single statement.

 

[ria.arora]

I'll prefer to remove from the result set only but now sure how to do that? I please send me the sample for the same.


I don't want use TransferSpreadsheet Method because there are number of quierie's results need to be populated into Excel Dashboard into specific cells and need to do the formating / SUM etc. I was not sure how to generate the dashboard using TransferSpreadsheet that why I prefered to write data into cell by cell.

Please fine below code used for result set

Code:
    strSelectSQL = "SELECT " & _
        "[Banker], " & _
        "[Income YTD], [AnnRevenue],  " & _
        "[VRevenueTargetCY], [RevenuePriorFY],  " & _
        "[VAnn vs Budget], [VAnnu vs Prior FY Revenue],  " & _
        "[Income YTD], [Ann ARevenue],  " & _
        "[ARevenueTargetCY], [ARevenuePriorFY],  " & _
 ...
 ...
 ...
 ...
 "[AnnuVRevenue] / [TotalAnnuRevenue] AS [Annu Actual V Ratio] " & _
        "FROM tbl_WConsol  " & _
        "INNER JOIN tbl_Bankers ON tbl_WConsol.[Banker] = tbl_Bankers.[BankerNm] " & _
        "WHERE [YearMonthWeek] = '" & txtReportingMonth & CStr(gsReportingWeekNo) & "'" & _
        "AND Team = '" & sTeamRegionName & "'"
         Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)
    recount = rsTmp.RecordCount
    rsTmp.MoveFirst
    RowEnd = RowStart + recount

    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]
        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count
    rsTmp.Close
    Set rsTmp = Nothing

 

[pbaldy]

You would add to your criteria:

"AND Team = '" & sTeamRegionName & "' AND FieldName <> 0"