Results 1 to 14 of 14
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    conditional formating - Excel

    Hi Guys



    i can export a query to excel and format the newly created worksheet as required whic is great

    the only thing i cant seem to get my head around now is to apply conditional formatting across multiple columns

    the code im using that works great is

    Code:
    Select Case MsgBox("This Will Take A Short While, As The Excel Sheet Is Formatted Nicely", vbOKCancel Or vbSystemModal, "Export To Exel")
            Case vbOK
            
                Dim ObjXlApp As Object
                Dim ObjXlBook As Object
                Dim ObjXlSheet As Object
                Dim StrSql As String
                Dim ObjRs1 As DAO.Recordset 'THIS IS AN Object
                Dim IRowStart As Integer
                Const XLCENTER = -4108
                Const XLLEFT = -4131 'xlRight is -4152
                Const XLCELLVALUE = 1
                Const XLLESSEQUAL = 8
                Const XLEQUAL = 3
                Const XLNOBLANKSCONDITION = 13
                'THIS IS THE SQL THAT CREATES THE EXCEL SHEET
                StrSql = "SELECT Asset.AssetNumber AS [Asset Number], Asset.AssetName AS [Asset Name], AssetType.Type AS [Type Of Asset], AssetMake.Asset AS [Asset Make], AssetDetails.AssetModel, AssetLocation.Location AS [Asset Location], OperatingSystem.OperatingSystem AS [Operating System], Users.UserName, Asset.IPAddress " & vbCrLf & _
                    "FROM OperatingSystem INNER JOIN (Users INNER JOIN (AssetType INNER JOIN (AssetMake RIGHT JOIN (AssetLocation INNER JOIN (Asset INNER JOIN AssetDetails ON Asset.assetID = AssetDetails.AssetID) ON AssetLocation.assetLocationID = Asset.AssetLocation) ON AssetMake.AssetMakeID = AssetDetails.AssetMakeID) ON AssetType.AssetTypeID = Asset.AssetType) ON Users.UserID = Asset.UserID) ON OperatingSystem.OperatingSystemID = AssetDetails.OS " & vbCrLf & _
                    "GROUP BY Asset.AssetNumber, Asset.AssetName, AssetType.Type, AssetMake.Asset, AssetDetails.AssetModel, AssetLocation.Location, OperatingSystem.OperatingSystem, Users.UserName, Asset.IPAddress, Asset.AssetDisposedOf, AssetLocation.Location, Asset.assetID, AssetDetails.OS " & vbCrLf & _
                    "HAVING (((Asset.AssetDisposedOf)=False)) " & vbCrLf & _
                    "ORDER BY AssetType.Type, OperatingSystem.OperatingSystem DESC;"
       
                'SHOW THE OUTPUT IN THE IMMEDIATE WINDOW
                'Debug.Print StrSql
                'EXECUTE QUERY AND POPULATE RECORDSET
                Set ObjRs1 = CurrentDb.OpenRecordset(StrSql, dbOpenSnapshot)
                'IF NO DATA, DON'T BOTHER OPENING EXCEL, JUST QUIT
                If ObjRs1.RecordCount = 0 Then
                    MsgBox "NO DATA SELECTED FOR EXPORT", vbInformation + vbOKOnly, "NO DATA TO EXPORT"
                Else
                    'WE SHALL TURN ON THE HOUR GLASS, SO THAT USERS KNOW THAT SOMETHING IS HAPPENING
                    DoCmd.Hourglass (True)
                    'CREATE AN INSTANCE OF EXCEL AND START BUILDING A SPREADSHEET LATE BINDING USED SO NO REFRENCES REQUIRED
                    Set ObjXlApp = CreateObject("Excel.Application")
                    
                    'HIDE EXCEL FOR NOW
                    ObjXlApp.Visible = False
               
                    Set ObjXlBook = ObjXlApp.Workbooks.Add()   'START A NEW WORKBOOK
                    Set ObjXlSheet = ObjXlBook.Worksheets(1)
                    With ObjXlSheet
                        .Name = "IT Assets"
                        .Cells.Font.Name = "Arial"
                        .Cells.Font.Size = 10
                        
                        'FORMAT RANGE AS REQUIRED, TO BOLD A CELL USE THIS FOR EXAMPLE .RANGE("A1").CELLS.FONT.BOLD = TRUE
                        .Range("A1", "H1").Merge
                        .Range("A1").Columns.AutoFit
                        .Range("A2", "H2").Merge
                        .Range("A2").Columns.AutoFit
                        .Range("A1").HorizontalAlignment = XLLEFT
                        .Range("A2").HorizontalAlignment = XLLEFT
                        .Range("A1").Cells.Font.Name = "Franklin Gothic Book"
                        .Range("A2").Cells.Font.Name = "Franklin Gothic Book"
                        .Range("A1").Cells.Font.Size = 12
                        .Range("A2").Cells.Font.Size = 10
                        .Range("A1").Value = "IT Assets Details"
                        .Range("A1").Font.Color = vbBlue
                        .Range("A2").Value = "Exported ON" & " - " & Date
                        .Range("A2").Font.Color = vbBlue
                        'ADD HEADER IFORMING OF POSSIBLE EXTENDED RESOLUTION TIMES
                        .Range("A4", "H4").Merge
                        .Range("A4").Columns.AutoFit
                        .Range("A4").Cells.Font.Name = "Franklin Gothic Book"
                        .Range("A4").Cells.Font.Size = 10
                        .Range("A4").Value = "All Asset Details On Record"
                        .Range("A4").Font.Color = vbRed
                        'NOW WE SHALL BUILD THE COLUM HEADINGS.VALUE IS THE TEXT REQUIRED
                        .Range("A6").Value = "Asset Number"
                        .Range("B6").Value = "User Name"
                        .Range("C6").Value = "Asset Name"
                        .Range("D6").Value = "Type Of Asset"
                        .Range("E6").Value = "Asset Make"
                        .Range("F6").Value = "Asset Model"
                        .Range("G6").Value = "Asset Location"
                        .Range("H6").Value = "Operating System"
                        .Range("I6").Value = "IP Address"
                        
                        'NOW WE APPLY COLOUR TO THE CELLS TO MAKE THEM LOOK NICE
                        .Range("A6:I6").Interior.ColorIndex = 37 '(37 IS THE BLUE COLOUR)
                        .Range("A6:I6").Borders.Weight = 3
                   
                        'NOW WE FORMAT THE CELLS AS REQUIRED
                        .Range("A6:i6").Cells.Font.Bold = True
                        .Range("A6:i6").HorizontalAlignment = XLCENTER
                        .Range("A6:i6").Columns.AutoFit
                            
                        'IROWSTART IS THE ROW THAT THE RECORDSET WILL ENTER DATA INTO
                        IRowStart = 7
                        'THEN WE LOOP THROUGH RECORDSET ABOVE AND COPY DATA FROM THE RECORDSET UNTIL WE GET TO THE END OF FILE
                        Do While Not ObjRs1.EOF
                
                            'START IMPORTING THE DATA FROM THE RECORD SET ABOVE INTO THE REQUIRED COLUMNS A,B,C,D IN THIS EXAMPLE
                            .Range("A" & IRowStart).Value = Nz(ObjRs1![Asset Number], "")
                           
                            .Range("A" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("A" & IRowStart).ColumnWidth = 20
                            .Range("B" & IRowStart).Value = Nz(ObjRs1![UserName], "")
                            .Range("B" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("B" & IRowStart).ColumnWidth = 20
                            .Range("C" & IRowStart).Value = Nz(ObjRs1![Asset Name], 0)
                            .Range("C" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("C" & IRowStart).ColumnWidth = 25
                            .Range("D" & IRowStart).Value = Nz(ObjRs1![Type Of Asset], 0)
                            .Range("D" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("D" & IRowStart).ColumnWidth = 20
                            
                            'NOW APPLY CONDITIONAL FORMATTING AS REQUIRED
                            With .Range("D" & IRowStart).FormatConditions.Add(XLCELLVALUE, XLEQUAL, "Laptop")
                                .Font.Color = vbBlue
                            End With
                                           
                            .Range("E" & IRowStart).Value = Nz(ObjRs1![Asset Make], 0)
                            .Range("E" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("E" & IRowStart).ColumnWidth = 25
                    
                            .Range("F" & IRowStart).Value = Nz(ObjRs1![AssetModel], 0)
                            .Range("F" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("F" & IRowStart).ColumnWidth = 30
                    
                            .Range("G" & IRowStart).Value = Nz(ObjRs1![Asset Location], 0)
                            .Range("G" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("G" & IRowStart).ColumnWidth = 35
                    
                            .Range("H" & IRowStart).Value = Nz(ObjRs1![Operating System], 0)
                            .Range("H" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("H" & IRowStart).ColumnWidth = 30
                            
                            'NOW APPLY CONDITIONAL FORMATTING AS REQUIRED
                            With .Range("H" & IRowStart).FormatConditions.Add(XLCELLVALUE, XLEQUAL, "Windows 7 Professional 32 Bit")
                                .Font.Color = vbRed
                            End With
                    
                            .Range("I" & IRowStart).Value = Nz(ObjRs1![IPAddress], "") '"" WITH LEAVE THE CELL BLANK
                            .Range("I" & IRowStart).HorizontalAlignment = XLCENTER
                            .Range("I" & IRowStart).ColumnWidth = 15
                            
                           'NOW APPLY CONDITIONAL FORMATTING AS REQUIRED
                            With .Range("I" & IRowStart).FormatConditions.Add(XLNOBLANKSCONDITION) 'FORMATS ALL CELLS THAT ARN'T BLANK
                                .Font.Color = vbRed
                            End With
                         
                            IRowStart = IRowStart + 1
                            ObjRs1.MoveNext
                        Loop
                
                        'THEN WE SET A FOOTER, WE LEAVE A GAP OF 3 ROWS AND FORMAT THE FOOTER NICELY
                        IRowStart = IRowStart + 2
                        .Range("A" & IRowStart).Value = "These Are All The Asset Details That We Have On Record, These Do Not Include Assets That Have Been Disposed Of"
                        .Range("A" & IRowStart).Font.Color = vbRed
                        .Range("A" & IRowStart).HorizontalAlignment = XLLEFT
                        
                      
                        'PLACE THE CURSOR AT THE REQUIRED CELL
                        .Range("A7").Select
                        ObjXlApp.ActiveWindow.FreezePanes = True
                        .Range("A6:I6").Autofilter
                         
                        'NOW WE SAVE THE NEWLY CREATED EXCELFILE TO THE NETWORK
                        ObjXlSheet.Application.DisplayAlerts = False
                        ObjXlSheet.SaveAs DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\IT Assets" & "\IT Asset Report.xls"
                        ObjXlSheet.Application.DisplayAlerts = True
                       
                    End With
                    'NOW WE CLOSE THE RECORD SETS AND RECLAIM ANY MEMORY HELD
                    DoCmd.Hourglass False
                    ObjXlApp.Visible = True
                    ObjRs1.Close
                    Set ObjRs1 = Nothing
                    
                    Set ObjXlApp = Nothing
                    Set ObjXlBook = Nothing
                    Set ObjXlSheet = Nothing
                    
                  
                    'NOW WE OPEN THE FILE WE HAVE JUST SAVED
                    ObjXlApp.Visible = True
                    ObjXlApp.WindowState = 3
                    ObjXlApp.Workbooks.Open DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\IT Assets" & "\IT Asset Report.xls"
                                        
                End If
        
            Case vbCancel

    The code in Red is the part i would like to change so that the text in columns A:I is red, not just the text in column H
    im struggling to get my head around chenging the text colour of coloumn a:i based on text in column H

    any helkp would be fantastic

    Steve

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Untested in your situation, but I refer to a range like this:

    .Range("G" & R & ":P" & R).Select

    where R is a row variable. The following lines apply borders and such to columns G to P.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Paul

    thats great will give that a go, many thanks

    Steve

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Steve, post back if you get stuck. I've never applied conditional formatting to Excel, so it could be that doesn't work for you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Quite frankly, when tasked with something like this, I prefer to create an Excel macro to do the Excel formatting.
    Then I just have my Access database call and run that macro.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Looks like this code creates a new Excel file, but I suppose a template could be used that had such a macro (or already defined CF).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Guys

    im starting to think that the template option is best, i will go that way
    and leave the rest of my hair where it should be lol on my head ive been taring it out for ages

    thanks guys

    Steve

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Looks like this code creates a new Excel file, but I suppose a template could be used that had such a macro (or already defined CF).
    If you don't actually want VBA code in the file that is produced, I often create an Excel file that simply holds the VBA code and runs upon opening.
    So, I will have my Access application drop an Excel file with a specified name in a specified folder.
    Then, I will have it call and open my Excel file, which opens, and automatically runs the macros on the new Excel file.

  9. #9
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi JoeM

    What a great Idea, no idea why i didnt think of that

    tried on a test file, works fab
    will go that route

    thanks again to all that have helped

    Steve

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome. Glad it helped!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JoeM View Post
    If you don't actually want VBA code in the file that is produced
    I wasn't suggesting that, just saying that if a new file is being created by the code, it wouldn't have any existing code in it. I often use template files that already have headers, etc. My automation code copies the template file with a new name, then opens and populates the copy. I haven't tried code in the Excel file itself, other than recording macros to see what code is produced so I can adapt to my automation code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I wasn't suggesting that, just saying that if a new file is being created by the code, it wouldn't have any existing code in it. I often use template files that already have headers, etc. My automation code copies the template file with a new name, then opens and populates the copy. I haven't tried code in the Excel file itself, other than recording macros to see what code is produced so I can adapt to my automation code.
    Yes, that would probably work if you pre-formatted the entire columns with the needed Conditional Formatting rules.
    Sometimes I need dynamic VBA to do certain things, where a basic Template just won't do enough. You could have a "Template-like" file with VBA code in it that runs and populates itself. However, you could also have a Macro file that runs against some other file so that the final file does not contain the VBA code in there. That is all I was trying to say.

    As is evident, there are many ways to attack this!

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds like you're way ahead of me in the Excel VBA department.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Sounds like you're way ahead of me in the Excel VBA department.
    That is actually my real specialty! I learned that well before Access and Access VBA.
    So I am an Excel guy by trade first, and Access second.

    But I do love Access and what it can do. It drives me crazy when people try to create relational databases in Excel. They jump through a million hoops to do it (and it it usually is rather clunky), when it would be so much easier to use Access!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional Formating
    By Derrick T. Davidson in forum Reports
    Replies: 3
    Last Post: 07-18-2014, 09:25 AM
  2. Conditional Formating
    By Derrick T. Davidson in forum Forms
    Replies: 5
    Last Post: 04-13-2013, 07:18 AM
  3. Conditional Formating
    By redbull in forum Reports
    Replies: 2
    Last Post: 03-26-2012, 12:08 PM
  4. Conditional Formating help
    By Mounds in forum Forms
    Replies: 2
    Last Post: 02-10-2012, 11:07 AM
  5. Conditional formating!
    By karanvemuri in forum Access
    Replies: 3
    Last Post: 10-29-2011, 03:34 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums