none
Formatting a report and export the formatted report RRS feed

  • Question

  • Hi all,

    I want to format an Access report as follows:

    I have a field called "StockOut". If this field is "Y", then I need the entire row on the report to be highlighted in Red. The report is linked to a query. I have tried several event procedures, but cannot find a solution.

    After the report is generated, I need to export the report to MS Excel, but I want to export it with the formats. When the Excel sheet opens, the row should also be highlighted in red. Currently, the report exports 100%, but the rows are not formatted.

    Any suggestions?

    Thanks

    Deon

    Monday, July 11, 2016 8:34 AM

Answers

  • Try this modification to the Do Loop portion of the code instead...

            Do While Not rsGeneric.EOF
                row = row + 1
                col = 0
                Do While (col < ColCount)
                    .Cells(row, col + 1).Value = rsGeneric.Fields(col)
                    ' This checks if stockOut is "Y" for the given row, and formats accordingly (hopefully)
                    if rsGeneric.Fields(col).Name = "StockOut" AND rsGeneric.Fields(col) = "Y" then
                         .Cells(row, col + 1).EntireRow.Interior.ColorIndex = vbRed
                     End If
                    col = col + 1
                Loop
                            
                rsGeneric.MoveNext
            Loop


    Miriam Bizup Access MVP

    • Marked as answer by Deon SA Monday, July 11, 2016 12:33 PM
    Monday, July 11, 2016 12:28 PM

All replies

  • You need to use Excel Automation if you want custom formatting in your Excel output.  This basically opens Excel in your code, and gives you the full control you would have writing macros in Excel. 

    The following function is one I use to simply export data to Excel.   You can modify the code as needed with If-Then blocks to provide the conditional formatting you're looking for.

    You'll find plenty of other examples of this on the Web, if you use search terms such as "Access VBA Export Excel Automation", "Excel VBA Row Background Color", etc...

    Function GenericExcelReport(sSelect As String, sTitle As String) As Boolean
    'On Error GoTo ErrGenericExcelReport
    
        GenericExcelReport = False
    
        Dim db As Database
        Dim rsGeneric As DAO.Recordset
        
        Set db = CurrentDb
        Set rsGeneric = db.OpenRecordset(sSelect, dbOpenDynaset, dbSeeChanges)
        
        Dim ColCount As Integer
        Dim col As Integer
        Dim row As Integer
            
        Dim oExcel As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oWS As Excel.Worksheet
        
        'open the spreadsheet for editing
    'On Error GoTo Excel_EH
        If oExcel Is Nothing Then Set oExcel = New Excel.Application
        oExcel.Visible = True
        Set oWB = oExcel.Workbooks.Add
        Set oWS = oExcel.ActiveSheet
    
        
    'On Error GoTo ErrGenericExcelReport
    
        DoEvents
        
        ColCount = rsGeneric.Fields.Count
        row = 1
        col = 0
        With oWS
                         
            If (sTitle & "" <> "") Then row = row + 2       'set up for the title if there is one
            
            .Rows(row).Font.Bold = True
            
            'set up the Column Headings and
            Do While (col < ColCount)
                .Cells(row, col + 1).Value = rsGeneric.Fields(col).Name
                
                'check if this field type is Date/Time
                If rsGeneric.Fields(col).Type = 8 Then
                    'next line requires more checking, the property may not exist for each date field
                    'If (rsGeneric.Fields(col).Properties("Format") = "Short Date") then .Columns(col + 1).NumberFormat = "m/d/yyyy;@"
                    .Columns(col + 1).NumberFormat = "[$-409]yyyy-mm-dd"
                End If
                
                'check if this field type is Currency
                If rsGeneric.Fields(col).Type = 5 Then
                   .Columns(col + 1).NumberFormat = "$#,##0.00"
                End If
                    
                col = col + 1
            Loop
            
            'output the data
            If rsGeneric.EOF Then
                row = row + 1
                col = 0
                .Cells(row, col + 1).Value = "There are no records to display."
                .Range(.Cells(row, col + 1), .Cells(row, ColCount)).Merge
            End If
            
            Do While Not rsGeneric.EOF
                row = row + 1
                col = 0
                Do While (col < ColCount)
                    .Cells(row, col + 1).Value = rsGeneric.Fields(col)
                    col = col + 1
                Loop
                            
                rsGeneric.MoveNext
            Loop
            
            .Cells.EntireColumn.AutoFit
            
            If (sTitle & "" <> "") Then
                row = 1
                col = 0
                .Rows(row).Font.Bold = True
                .Cells(row, col + 1).Value = sTitle
                .Cells(row, col + 1).WrapText = False
                .Cells(row, col + 1).Font.Size = 14
            End If
            
        End With
    
    
        GenericExcelReport = True
    
    Exit Function
    
    Excel_EH:
        DoEvents
        DoEvents
        MsgBox "An error occurred. Please close excel and try running the process again.", vbExclamation, "No Page Break Inserted"
    Exit Function
    
    ErrGenericExcelReport:
        MsgBox "An error occured while attempting to generate the report." & vbCrLf & Err.number & ": " & Err.Description
    Exit Function
        
    End Function

    The code to highlight a cell might look something like this:

    If rsGenericFields(Col).Name = "StockOut" and rsGenericFields(Col) = True then
       .Cells(Row,Col+1).EntireRow.Interior.ColorIndex = vbRed
    End If 

    EDIT: Again there are plenty of (bigger and better) examples of this on the web.  The above If- then shows you how to simply change a row's background color.  However, automation gives you full control, and if needed you can actually set up robust conditional formatting in your excel report by building on this method...


    Miriam Bizup Access MVP




    • Edited by mbizup MVP Monday, July 11, 2016 9:14 AM
    Monday, July 11, 2016 9:03 AM
  • Hi Miriam,

    I have amended your code to suit my needs. One part as follows:

                    .Application.cells(RowSelection(t), ColumnSelection).Select
                    If .Application.cells(RowSelection(t), ColumnSelection) = "Y" Then
                        .Application.cells(RowSelection(t), ColumnSelection).EntireRow.Interior.ColorIndex = vbRed
                    End If

    Where RowSelection(t) = number of rows in the sheet and ColumnSelection = "O". Column will never change, it will always be column "O".

    The error message generated here is error 9 - Subscript out of Range as soon as the IF part evaluates to True.

    Monday, July 11, 2016 11:27 AM
  • Try this modification to the Do Loop portion of the code instead...

            Do While Not rsGeneric.EOF
                row = row + 1
                col = 0
                Do While (col < ColCount)
                    .Cells(row, col + 1).Value = rsGeneric.Fields(col)
                    ' This checks if stockOut is "Y" for the given row, and formats accordingly (hopefully)
                    if rsGeneric.Fields(col).Name = "StockOut" AND rsGeneric.Fields(col) = "Y" then
                         .Cells(row, col + 1).EntireRow.Interior.ColorIndex = vbRed
                     End If
                    col = col + 1
                Loop
                            
                rsGeneric.MoveNext
            Loop


    Miriam Bizup Access MVP

    • Marked as answer by Deon SA Monday, July 11, 2016 12:33 PM
    Monday, July 11, 2016 12:28 PM
  • Thanks Miriam.It still gave me the error, but I resolved it by replacing

    .Cells(Row,Col+1).EntireRow.Interior.ColorIndex = vbRed

    With this code

    .Cells(Row,Col+1).EntireRow.Font.Color = vbRed

    Thanks a mil for your assistance!!

    Monday, July 11, 2016 12:33 PM
  • Glad you got it worked out! Good luck with your project.

    Miriam Bizup Access MVP

    Monday, July 11, 2016 5:02 PM