• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

In Excel, is there a way to automatically (programatically) format tables?

I have crosstab queries that I export from access into excel. I would love to figure out a way to automatically format this data into nice looking tables (ultimately published in a report in Word).

I have a before and after photo example you can refer to (this is my 1st attempt at utilizing free image hosting, so let me know if it is not working.)
Before:
http://img384.imageshack.us/img384/4339/sourcedata4iy.jpg

After:
http://img137.imageshack.us/img137/3656/formatteddata2cp.jpg

Here are some criteria:

1) Have to be able to figure out what constitutes a distinct section of the table, so the borders can be applied properly. (I.e. thick borders around Grade 6, Grade 8 etc.)
2) Be able to color over a column (i.e., in this case, the State 2005 data)
3) Grey separators for table titles
4) appropriate manipulation of font size, bold, ital, justification

A pretty tall order. If you look in the sourcedata picture at column A, I have tentatively begun titling rows with labels that might be parsed to help interpret the nature of the row, i.e. "lblSpacer_TblTitle," "lblSpacer_Grade," "lblSpacer_SourceYear" in addtition to the labels I am using for the individual variables.

In the past, some have suggested pivottables, but when I tried, it would not let me put text data in the 'body' of the table, so I was at a loss of how to make it work (for example, in addition to the grade and year labels, some cells might need to read "n/a," if data were unavailable for past years). Another suggestion  was that Crystal Reports should be able to make tables similar to this; I could not find references to this ability in the many Crystal tutorials I found online, I could not actually see a potent difference between Crystal's report engine and Access's.

What I would like is something that can automatically deal with different variations that might arise and format appropriately; A year might be missing, or an entire grade:
http://img384.imageshack.us/img384/1153/variationsource0fe.jpg
http://img384.imageshack.us/img384/5994/variationformatted8in.jpg

While I'm shooting for the moon, how about automatically adjusting column widths so that it makes the table have the same dimensions.

Am I going about this in the hardest possible way? Let me know that, too.
0
Dsastray
Asked:
Dsastray
  • 8
  • 4
  • 2
1 Solution
 
mcsweenSr. Network AdministratorCommented:
Have you tried to create this as an Access report.  All of the things you are asking for are available in an Access report.  That could then be saved as a PDF or something to distribute.
0
 
DsastrayAuthor Commented:
Can you point me to a tutorial or example of an access report that is formatted as intricately? I have had the idea that it was somewhat capable of doing this, but not quite up to the formatting intricacies of excel. Any links would be appreciated.
0
 
mcsweenSr. Network AdministratorCommented:
I'm sorry, I was mistaken...Access reports probably can't handle that.  I was thinking you could add more sections to the report but you apparently can't (it's been a long time since I've written any Access reports).  Probably the easiest way to do it would be with Crystal Reports, but that's going to cost ya :(
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DsastrayAuthor Commented:
I would even go for looking at this capability in Crystal; could you point me to any Crystal tutorials showing something similar to what I need? I just keep finding super-drab looking reports, nothing of the complexity I had wanted.
0
 
jimbobmcgeeCommented:
If you're even going to attempt this, can you be sure that the format/layout will not change?  Because, basically, you're going to be hard-coding some pretty definite formatting routines for this.

To get started, first thing's first.  You need to copy the data that you require from the source sheet to an output sheet (or delete the unwanted data from the source).  That looks like the used range from D:D to Y:Y, so you can do the following (I wouldn't use the 'gutter' column/row at A:A and 1:1; you can adjust margins to do this, after):

    Sub FormatSourceData(ByRef oSourceSheet As Excel.Worksheet)
        Dim oWorkBook As Excel.Workbook
        Dim oTargetSheet As Excel.Worksheet
        Dim oWorkRange As Excel.Range

        Set oWorkbook = oSourceSheet.Parent
        Set oTargetSheet = oWorkbook.Sheets.Add
        oTargetSheet.Name = "Formatted Output Sheet (" & oWorkbook.Sheets.Count & ")"
        Application.Intersect(oSht.Range("D:D", "Y:Y"), oSht.UsedRange).Copy(oTargetSheet.Range("A1"))        

        '...MORE CODE GOES HERE
    End Sub

Since this is a fixed procedure, we will add formatting to this function, before the End Sub statement.  The next thing we can try is to split the new sheet into separate tables, using named ranges (assuming you have no named ranges already)

    Dim oRow As Excel.Range
    Dim nCount As Integer
    For Each oRow In oTargetSheet.UsedRange.EntireRow
        If Left(oRow.Cells(,1).Value, 6) = "Table " Then
            Application.Intersect(oWorkRange, oTargetSheet.UsedRange).Name = "TableRange" & nCount
            Set oWorkRange = oRow
            nCount = nCount + 1
            Application.Intersect(oRow, oTargetSheet.UsedRange).Name = "TableRange" & nCount & "Header"
        Else
            Set oWorkRange = oWorkRange.Resize(oWorkRange.Rows.Count + 1)
        End If
    Next oRow

Now we can at least try getting the heading shaded:

    Dim oName As Excel.Name
    For Each oName In oWorkBook.Names
        If Left(oName.Name, 10) = "TableRange" And Right(oName.Name, 6) = "Header" Then
            Set oWorkRange = oName.RefersToRange
            oWorkRange.Merge
            oWorkRange.Interior.Color = RGB(192, 192, 192)
        End If
    Next oName

That should get you started.  The next step is to split each named range table into its required columns, for borders and shading.  I'm not going to go into that until:
    (1.)  I'm giving you what you want
    (2.)  You upload the sheet somewhere, so I can test

HTH

J.
0
 
jimbobmcgeeCommented:
Rather, (1.) should read:

    (1.)  You're sure that I'm giving you what you want

J.
0
 
DsastrayAuthor Commented:
Wow, this looks very promising. I am off to find a file hosting service.
0
 
DsastrayAuthor Commented:
Okay, I have uploaded a test file to

http://www.savefile.com/files/5252586

Click on the orange 'download' button in the lower right corner (I was having trouble seeing it because it is cleverly disguised to look just like an ad...),

...which then takes you to a second page, where you click on the hyperlink 'Download the file now' at the bottom.

Thanks, I am very excited. As you help me understand some of the principles involved in making this happen, I hope to mitigate the hard-coded inflexibility aspect by building a form with formatting choices/rules to select.
0
 
jimbobmcgeeCommented:
OK, I've got the sheet -- is this the initial form of the sheet, or did you add the labels (A:C) yourself?  If you are adding the labels, it may not be necessary, dependant on the original data (seems overkill to create a formatting 'language' and then parse it through VBA).  That said, where does the original data come from -- a database?  If so, it may be more efficient to generate the formatting as the data is extracted.

I doubt we're going to get a one-size-fits-all routine for all your tabling needs but we may be able to create a formatter for this style of table.  Besides, it's often more appropriate to treat radically differing data as a new 'project' and create a specific macro for each desired format.

J.
0
 
DsastrayAuthor Commented:
It is from a MSAccess DB; A:C is what helps me keep the query output in the correct order and selecting the correct variables, they are probably not neccessary to be in the output transfered to the spreadsheet, but I thought they could be handy.

Plus, as the process stands right now, it is rather circular; I will often "design" a table in excel, and then import A:C as a list of variable codes for the DB to organize output with.
0
 
DsastrayAuthor Commented:
Re: the second point -

I am just interested in getting this test running; once I (we) determine how feasible it is, that will help me decide whether it makes sense to go further with this whole kooky idea.;)
0
 
jimbobmcgeeCommented:
Fair enough.  Seeing as you're willing to do some work manually, for a test implementation, try this.  Select the actual data table and run this macro against it.  It's a literal formatter, though -- it expects certain rows and columns to be as they are in the sheet you supplied, but it worked for all tables on the sheet:

    Sub FormatType1()
        Const NoFill As Integer = -4142
   
        Dim oSel As Excel.Range
        Dim oRng As Excel.Range
        Dim oMrg As Excel.Range
       
        Set oSel = Selection
       
        For Each oRng In oSel.Columns
            oRng.BorderAround 1, xlThin, xlColorIndexAutomatic
        Next oRng
                   
        For Each oRng In oSel.Rows(2).Cells
            If oRng.Value = Empty Then
                Set oMrg = oMrg.Resize(1, oMrg.Columns.Count + 1)
                Application.Intersect(oMrg.Columns(oMrg.Columns.Count).EntireColumn, oSel).BorderAround 1, xlThin, xlColorIndexAutomatic
            Else
                If Not (oMrg Is Nothing) Then
                    oMrg.Merge False
                    oMrg.BorderAround 1, xlThin, xlColorIndexAutomatic
                    Application.Intersect(oMrg.Columns(oMrg.Columns.Count).EntireColumn, oSel).Interior.Color = RGB(255, 255, 192)
                    Application.Intersect(oMrg, oSel).BorderAround 1, xlThin, xlColorIndexAutomatic
                    oMrg.HorizontalAlignment = xlCenter
                End If
                Set oMrg = oRng
            End If
        Next oRng
        oMrg.Merge False
        oMrg.BorderAround 1, xlThin, xlColorIndexAutomatic
        Application.Intersect(oMrg.Columns(oMrg.Columns.Count).EntireColumn, oSel).Interior.Color = RGB(255, 255, 192)
        Application.Intersect(oMrg, oSel).BorderAround 1, xlThin, xlColorIndexAutomatic
        oMrg.HorizontalAlignment = xlCenter
       
        oSel.Columns(1).Interior.ColorIndex = NoFill
        oSel.Rows(1).Merge False
        oSel.Rows(1).Interior.Color = RGB(192, 192, 192)
        oSel.Rows(1).Font.Bold = True
        oSel.Rows(2).BorderAround 1, xlThin, xlColorIndexAutomatic
        oSel.Rows(3).BorderAround 1, xlThin, xlColorIndexAutomatic
       
        oSel.Range("A2", "A3").Merge
        oSel.Range("A2", "A3").Font.Bold = True
        oSel.Range("A2", "A3").HorizontalAlignment = xlCenter
        oSel.Range("A2", "A3").VerticalAlignment = xlCenter
        oSel.Range("A2", "A3").BorderAround 1, xlThin, xlColorIndexAutomatic
       
        oSel.BorderAround 1, xlMedium, xlColorIndexAutomatic
        oSel.Rows(1).Borders.Item(xlEdgeTop).ColorIndex = NoFill
        oSel.Rows(1).Borders.Item(xlEdgeLeft).ColorIndex = NoFill
        oSel.Rows(1).Borders.Item(xlEdgeRight).ColorIndex = NoFill
        oSel.Rows(1).Borders.Item(xlEdgeBottom).Weight = xlMedium
        oSel.Columns.EntireColumn.AutoFit
    End Sub

It works best if:
    (1.)  You remove the VarFullDefinition column (it's empty, isn't it?).  You can also remove the other label columns
    (2.)  You select only the table region (e.g. D6:Y21)
    (3.)  You format the tables from bottom to top (e.g. do D22:Y37 before D6:Y21 before D2:Y5)

HTH

J.
0
 
DsastrayAuthor Commented:
Hey, I am going to find some time to try this out soon; you are not abandoned. Talk to you soon.
0
 
DsastrayAuthor Commented:
Looks good! I am going to award you the points. I am also interested in range detection, that is, how to know when to begin and end an outlined section etc. Keep an eye out for that question soon.

Thanks for your help!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now