Results 1 to 8 of 8
  1. #1
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936

    Export to Excel and format

    I have exported to Excel and formatted successfully. However, I am trying to find certain rows (with a criteria) and higlight just those rows in the spreadsheet. When I attempted it my computer got itself all out of whack and it took me ages to sort it all out, so I am afraid to try anything else! Can anyone help with the code to filter on just the applicable rows in order to change their color?



    Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    I don't follow your question. Are you exporting your data to excel, then while you're in excel, trying to find a specific record(s) but you want those rows that meet your criteria to be highlighted somehow?

    Or are you exporting data to excel and you want the format of the entire file to be altered?

  3. #3
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    I concur with rpeare on not quite following what you are trying to describe. Some specifics would likely be helpful. However, I did just implement something similar to what I think you are getting at. I will show you my code below

    I have a report that gets exported to Excel for distribution. One of the columns in the report is High Priority which has either TRUE or FALSE values. The following code shows how I find the right column and highlight those rows
    Code:
    Dim lRow As Integer, lCol As Integer, i As Integer, HPriCol As Integer
    
        lRow = xlWs.Range("A3").End(xlDown).Row
        lCol = xlWs.Range("A3").End(xlToRight).Column 
        HPriCol = xlWs.Range("A3:" & A(lCol) & "3").Find("High Priority", , xlValues).Column
    
    For i = 4 To lRow
            If xlWs.Range(A(HPriCol) & i).value = "TRUE" Then
                With xlWs.Range("A" & i & ":" & A(lCol) & i).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                End With
            End If
    Next i
    I hope that helps get you started.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry for not being very clear in my original post. Monterey_Manza seems to have given me some code which sounds like it is doing what I want. Yes, to find all the rows which have a certain value, such as True or False, and highlight only those rows. I will give it a try tomorrow. Thanks for responding.

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Fantastic!! This worked perfectly. I was fortunate enough to know which column held the value so didn't have to go looking for it, however.

    I am truly grateful for your help.

  6. #6
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    Glad it worked out for you. I don't know how often your reports change but I use the .Find method of the Excel range because the users of my dBase regularly request tweaks to either the formatting or data presented in some of the reports it generates. By using the Find method, I don't have to re-write my formatting code every time I add or subtract a column. Maybe you are lucky enough to never have this problem but it might prove helpful. Also, the more you rely on the code to find the right column, the more widely the code can be applied to other reports. Just a thought for the future.

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That's a good warning - although I am an experienced Access programmer, this is the first time that I have had to do any of this Excel formatting stuff. That is definitely something to program into the code, the users adding just one column would require rewrite.

    Hmmmm....

  8. #8
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    I'm still a relative newbie to Access but exporting to Excel and formatting has be a requirement for everything I build since I got started. I realize I forgot one thing to make the whole "Find" feature work. I had problems using the .Cells property of a Excel.Range object in conjunction with the column number so I decided to simply write a function that would give me a matrix of the alphabet. I know there should be another way but this just made my life much more simple. It goes out to "AZ" for those really long spreadsheets.

    Code:
    Function Alpha()
    Dim i As Integer, A(52) As String
    
        For i = 1 To 26
            A(i) = Chr(i + 64)
        Next i
        For i = 27 To 52
            A(i) = Chr(65) & Chr(i + 38)
        Next i
        Alpha = A
    End Function
    I usually just add a variant (usually A) in my code and use A = Alpha. Then, whenever the find method for a column returns an integer, I just plug the value it to the resulting array. That's where the A(HiPriCol) and A(lCol) come in for the code I originally posted.

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

Similar Threads

  1. Export to Excel in Transpose Format
    By abdulnaseer in forum Import/Export Data
    Replies: 1
    Last Post: 01-30-2013, 02:18 PM
  2. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  3. Export to Excel, format and e-mail
    By NISMOJim in forum Programming
    Replies: 16
    Last Post: 08-18-2012, 12:13 AM
  4. Report export to Excel with format
    By mrswarrick in forum Access
    Replies: 3
    Last Post: 02-29-2012, 03:10 PM
  5. Replies: 3
    Last Post: 08-18-2011, 05:04 PM

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