+ Reply to Thread
Page 3 of 17 FirstFirst 1 2 3 4 5 13 ... LastLast
Results 31 to 45 of 243

How to post a range - headers and data?

  1. #31
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    a b c
    1 1 9 59
    2 2 10 37
    3 3 8 76
    4 4 83 95
    5 5 49 6

    I'll check the code
    Marcelo Branco

  2. #32
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Blah Blah

    A B C
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6

  3. #33
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    a b c
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6

    The code generates letters in uppercase, but If the table is first thing in the post, for some reason beyond my imagination, the column letters appear in lowercase.


  4. #34
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Data Range
    A B C
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6


    I just changed the last code line to
    RangeToHTML = "Data Range" & vbNewLine & sReturn

  5. #35
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    CORRECTION

    The previous codes are not setting correctly the Font colors

    New code

    HTML Code: 
    Public Function RangeToHTML(rInput As Range, Optional bHeaders As Boolean = True) As String
        Dim rRow As Range, rCell As Range, sReturn As String, strAux As String, strColor As String
        sReturn = "[Table=""width: 500, class: grid""]"
        If bHeaders Then
            sReturn = sReturn & "[tr][td] [/td]"
            For Each rCell In rInput.Rows(1).Cells
                sReturn = sReturn & "[td]" & Chr$(rCell.Column + 64) & "[/td]"
            Next rCell
            sReturn = sReturn & "[/tr]"
        End If
        
        For Each rRow In rInput.Rows
            sReturn = sReturn & vbNewLine & "[tr]"
            If bHeaders Then
                sReturn = sReturn & "[td]" & rRow.Row & "[/td]"
            End If
            
            For Each rCell In rRow.Cells
                strAux = Right("000000" & Hex(rCell.Font.Color), 6)
                strColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                sReturn = sReturn & "[td][COLOR=""" & strColor & """]" & rCell.Text & "[/COLOR][/td]"
            Next rCell
            
            sReturn = sReturn & "[/tr]" & vbNewLine
        Next rRow
        
        sReturn = sReturn & "[/table]"
        RangeToHTML = "Data Range" & vbNewLine & sReturn
    End Function

  6. #36
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Tony,

    Do you mind, if you have time, to do a test?

    1. Create a Module in your Personal Workbook and put the Sub and the Function below in the new module
    (You must add a Reference to Microsoft Forms 2.0 Object Library)

    HTML Code: 
    Sub CopyRngToHTML()
        'Add Reference to  Microsoft Forms 2.0 Object Library
        Dim DataObj As New MSForms.DataObject
        Dim strTable As String
        
        Application.CutCopyMode = False
        strTable = RngToHTML(Selection)
        DataObj.SetText strTable
        DataObj.PutInClipboard
    End Sub
    
    Public Function RngToHTML(rInput As Range, Optional bHeaders As Boolean = True) As String
        Dim rRow As Range, rCell As Range, sReturn As String, strAux As String, strColor As String
        sReturn = "[Table=""width: 500, class: grid""]"
        If bHeaders Then
            sReturn = sReturn & "[tr][td] [/td]"
            For Each rCell In rInput.Rows(1).Cells
                sReturn = sReturn & "[td]" & Chr$(rCell.Column + 64) & "[/td]"
            Next rCell
            sReturn = sReturn & "[/tr]"
        End If
        
        For Each rRow In rInput.Rows
            sReturn = sReturn & vbNewLine & "[tr]"
            If bHeaders Then
                sReturn = sReturn & "[td]" & rRow.Row & "[/td]"
            End If
            
            For Each rCell In rRow.Cells
                strAux = Right("000000" & Hex(rCell.Font.Color), 6)
                strColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                sReturn = sReturn & "[td][COLOR=""" & strColor & """]" & rCell.Text & "[/COLOR][/td]"
            Next rCell
            
            sReturn = sReturn & "[/tr]" & vbNewLine
        Next rRow
        
        sReturn = sReturn & "[/table]"
        RngToHTML = "Data Range" & vbNewLine & sReturn
    End Function
    2. Add a new command (Macros) in the QAT, linking to the Sub CopyRngToHTML in your Personal Workbook.

    Then to post a table all you have to do is:
    Select the Range
    Click in the new icon (QAT)
    Paste in the Forum Reply page.

    It worked for me. Please, check if it works to you too.

    M.

  7. #37
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    More testing (user defines table width via Input Box)

    Data Range
    A B
    1 Name Scores
    2 John 30
    3 Mary 32
    4 Mike 34
    5 William 36

  8. #38
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    Tony,

    Do you mind, if you have time, to do a test?
    Here goes...

    Data Range
    A B
    1 Name Status
    2 Pit Pass
    3 Pat Fail
    4 Pot Fail
    5 Put Pass

    EDIT: Tested in Excel 2010
    Last edited by Tony Valko; 08-26-2013 at 09:37 AM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #39
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Quote Originally Posted by mlcb View Post
    More testing (user defines table width via Input Box)
    Yeah, I like having that option.

    That way you can show more columns in the post without having to scroll. In some of my other test posts I was just manually editing this line:

    [Table="width: 500, class: grid"]

  10. #40
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Quote Originally Posted by Tony Valko View Post
    Yeah, I like having that option.

    That way you can show more columns in the post without having to scroll. In some of my other test posts I was just manually editing this line:

    [Table="width: 500, class: grid"]

    Testing new version - user defines width=300

    Data Range
    A B C
    1 3 2 4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6


    New code to Personal Workbook

    HTML Code: 
    Sub CopyRngToHTML()
         'Add Reference to  Microsoft Forms 2.0 Object Library
        Dim DataObj As New MSForms.DataObject
        Dim strTable As String, lWidth As Variant
        
        Application.CutCopyMode = False
        
        lWidth = Application.InputBox("Enter Table width - an integer between 100 and 1000", Type:=1)
        If lWidth = False Or lWidth < 100 Or lWidth > 1000 Then Exit Sub
        
        strTable = RngToHTML(Selection, CInt(lWidth))
        
        DataObj.SetText strTable
        DataObj.PutInClipboard
    End Sub
    
    Public Function RngToHTML(rInput As Range, lWidth As Integer, Optional bHeaders As Boolean = True) As String
        Dim rRow As Range, rCell As Range, sReturn As String, strAux As String, strColor As String
        sReturn = "[Table=""width:" & lWidth & ", class: grid""]"
        If bHeaders Then
            sReturn = sReturn & "[tr][td] [/td]"
            For Each rCell In rInput.Rows(1).Cells
                sReturn = sReturn & "[td]" & Chr$(rCell.Column + 64) & "[/td]"
            Next rCell
            sReturn = sReturn & "[/tr]"
        End If
        
        For Each rRow In rInput.Rows
            sReturn = sReturn & vbNewLine & "[tr]"
            If bHeaders Then
                sReturn = sReturn & "[td]" & rRow.Row & "[/td]"
            End If
            
            For Each rCell In rRow.Cells
                strAux = Right("000000" & Hex(rCell.Font.Color), 6)
                strColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                sReturn = sReturn & "[td][COLOR=""" & strColor & """]" & rCell.Text & "[/COLOR][/td]"
            Next rCell
            
            sReturn = sReturn & "[/tr]" & vbNewLine
        Next rRow
        
        sReturn = sReturn & "[/table]"
        RngToHTML = "Data Range" & vbNewLine & sReturn
    End Function

    Next improvement: I think (not tested yet) the code doesn't work with double or triple letters columns (AA or AAA), but I can envisage an easy solution

  11. #41
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Test with input box...

    Data Range
    A B
    1 Name Score
    2 Biff 71
    3 Tom 77
    4 Paul 82
    5 Hoppy 88

  12. #42
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: How to post a range - headers and data?

    Data Range
    A B
    1 Name Score
    2 Biff 71
    3 Tom 77
    4 Paul 82
    5 Hoppy 88

  13. #43
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    38,470

    Re: How to post a range - headers and data?

    Can you set the justification according to cell contents?

    HTML5 apparently doesn't accept align=right|left|center
    Entia non sunt multiplicanda sine necessitate

  14. #44
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to post a range - headers and data?

    Data Range
    A B C
    1
    3
    2
    4
    2 1 9 59
    3 2 10 37
    4 3 8 76
    5 4 83 95
    6 5 49 6

    I aligned only the first row ---> center.
    I did it manually but I think is possible to include in the code (to all rows)
    Next improvement...

  15. #45
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    38,470

    Re: How to post a range - headers and data?

    I meant alignment by cell, Marcelo -- text left, numbers right, errors and Booleans center.

+ Reply to Thread
Page 3 of 17 FirstFirst 1 2 3 4 5 13 ... LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1