DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: VB6 format excel sheet with canned Table Style

  1. #1
    Join Date
    Apr 2008
    Posts
    10

    VB6 format excel sheet with canned Table Style

    I've exported a MSFlexgrid to an Excel worksheet and want to format it as a table using Excel's canned "Table Style Light 1". Cannot find the code to do this exactly.

    I cobbled together some code in VB6, based on an Excel Macro, that produces the table -- but in the wrong color. I've changed the color number in the below code but it's still blue. I want alternating gray bars, not blue. Ideally I just want to use the canned Quick Style.

    The code is:
    --------------------------
    With wsXL
    .Range(.Cells(1, 1), .Cells(TheRows, TheCols)).Name = "Table1"
    End With
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("Table1"), , xlYes).AutoFormat = 40
    --------------------------------

    Thanks,

    6rtury

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    If you know how to do all of this manually, then start a macro, do it, stop the macro, and there is your code to do it.

  3. #3
    Join Date
    Apr 2008
    Posts
    10
    Have already done that. The problem is that it formats with a fixed color that I cannot change, e.g., the macro code implemented in VB6 is:

    With wsXL
    .Range(.Cells(1, 1), .Cells(TheRows, TheCols)).Name = "Table1"
    End With

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("Table1"), , _lYes).AutoFormat = 40


    The problem is that it produces a table only with the alternating rows in an intense blue. The choice that produced the above code, when done manually, colors alternate rows light gray. I've changed the AutoFormat number 40 to a dozen other numbers, but the colors will not change. It always produces a bright blue colored table.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    I don't see anything referencing any type of color in that code.

  5. #5
    Join Date
    Apr 2008
    Posts
    10
    I didn't either, but the autoformat = 40 gave me a table with alternating blue bars.

    I went back and redid the Excel macro and this time got a reference to a table style. Combined elements of the macro with some of my vb code and finally got the output I want. The final pieces of code that format the sheet contents as a shaded table:
    -----------------------------------
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(TheRows, TheCols)), , xlYes).Name = _
    "Table2"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight1"
    ----------------------------------
    I have since removed the next-to-last line (Range("Table2[.... ) because it works without it.

    6rtury

  6. #6
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Try This:
    ActiveSheet.UsedRange.AutoFormat xlRangeAutoFormatTable1

    Your Code:
    wsXL.UsedRange.AutoFormat xlRangeAutoFormatTable1
    Last edited by Ron Weller; 04-28-2008 at 10:24 PM.

  7. #7
    Join Date
    Apr 2008
    Posts
    10
    Ron:

    Your code was something I had previously tried. It formats the data as a table but does not color alternate rows as the canned style does. I did, after much trial and error, come up with a combination of Excel macro and V6 code that works. The two lines that do the trick are:

    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(TheRows, TheCols)), , xlYes).Name = _
    "Table2"
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight1"

    Thanks.

    6rtury

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by 6rtury
    The two lines that do the trick are
    If you don't mind, post your entire routine for this as it might help someone with facing the same or a similiar issue.

    Thanks.

  9. #9
    Join Date
    Apr 2008
    Posts
    10
    Be glad to. Only some of the code is "original". The part that deals with Excel was cobbled together from several other sources, but am unable to name them exactly.
    The code........
    ------------------------------------------------
    ' Write FlextGrid-data to an Excel table.
    Private Sub FlexGrid_To_Excel()
    Dim TheRows As Long
    Dim TheCols As Integer
    Dim WorkSheetName As String

    GridStyle = 1
    WorkSheetName = "ScheduleD"

    Dim objXL As New Excel.Application
    Dim wbXL As New Excel.Workbook
    Dim wsXL As New Excel.Worksheet
    Dim intRow As Integer ' counter
    Dim intCol As Integer ' counter

    If Not IsObject(objXL) Then
    MsgBox "You need Microsoft Excel to use this function", _
    vbExclamation, "Print to Excel"
    Exit Sub
    End If

    TheRows = MSFlexGrid2.Rows
    TheCols = MSFlexGrid2.Cols

    On Error Resume Next

    Set wbXL = objXL.Workbooks.Add
    Set wsXL = objXL.ActiveSheet

    ' name the worksheet --- add something later to put output multiple grids to a workbook
    With wsXL
    If Not WorkSheetName = "" Then
    .Name = WorkSheetName
    End If
    End With

    ' fill worksheet
    For intRow = 1 To TheRows
    For intCol = 1 To TheCols
    With MSFlexGrid2
    wsXL.Cells(intRow, intCol).Value = _
    .TextMatrix(intRow - 1, intCol - 1) & " "
    End With
    Next
    Next

    ' format and make it a sortable table
    For intCol = 1 To TheCols
    wsXL.Columns(intCol).AutoFit
    Next intCol

    'put it into lite gray alternating bars table form with sort by column head
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(TheRows, TheCols)), , xlYes).Name = _
    "Table2"
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight1"

    'Deselect the whole table and put cursor in A1
    ActiveSheet.Range("A1").Select

    'show the workbook
    objXL.Visible = True
    'Let user do the saving.
    '---------------------------------------------------------------
    'clean everything out of memory
    Set objXL = Nothing
    Set wbXL = Nothing
    Set wsXL = Nothing

    End Sub
    -----------------------
    6rtury

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Thank you Mr. 6rtury sir!

  11. #11
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Quote Originally Posted by 6rtury
    Ron:

    Your code was something I had previously tried. It formats the data as a table but does not color alternate rows as the canned style does.
    Actually xlRangeAutoFormatTable1 format does not have alternating colored lines. If you want alternating colored lines, you must choose a format that uses it.
    Try the xlRangeAutoFormatList1 format:
    ActiveSheet.UsedRange.AutoFormat xlRangeAutoFormatList1

    Also xlRangeAutoFormatList2 has every two lines colored:
    ActiveSheet.UsedRange.AutoFormat xlRangeAutoFormatList2

    Other Formats:
    xlRangeAutoFormat3DEffects1, or 2
    xlRangeAutoFormatAccounting1, 2, 3, or 4
    xlRangeAutoFormatClassic1, 2 , 3, or PivotTable
    xlRangeAutoFormatColor1, 2, or 3
    xlRangeAutoFormatList1, 2, or 3
    xlRangeAutoFormatNone, or Simple
    xlRangeAutoFormatReport1 , to 10
    xlRangeAutoFormatTable1, to 10

Similar Threads

  1. Replies: 2
    Last Post: 03-29-2010, 04:56 AM
  2. Passing Format() a User Defined Style
    By joewmaki in forum .NET
    Replies: 1
    Last Post: 06-14-2006, 12:45 PM
  3. oracle data into excel sheet
    By deepali in forum Java
    Replies: 0
    Last Post: 09-27-2005, 05:23 AM
  4. vb6 and Excel
    By Sherman in forum VB Classic
    Replies: 4
    Last Post: 06-07-2005, 10:50 AM
  5. Exporting datagrid to excel: Format cells?
    By wakeup in forum ASP.NET
    Replies: 2
    Last Post: 03-01-2005, 04:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center