+ Reply to Thread
Page 4 of 17 FirstFirst ... 2 3 4 5 6 14 ... LastLast
Results 46 to 60 of 243

How to post a range - headers and data?

  1. #46
    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?

    EDIT:Hadn't seen #45

    shg

    Columns and Rows and Booleans---> Center
    Numbers ---> Right
    Text ---> Left

    Is it?

    Requires some IFs to check cell contents...I'll try soon

    M.
    Marcelo Branco

  2. #47
    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?

    Requires some IFs to check cell contents
    Marcelo,

    VarType(cell.Value2) always returns vbEmpty, vbDouble, vbString, vbBoolean, or vbError
    Entia non sunt multiplicanda sine necessitate

  3. #48
    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?

    shg

    What do you suggest if the cell has a number formatted as text? Something like
    '1000

    I was thinking in IsNumeric, but I think it will result True for numbers as Numbers and also to numbers as Text

    Any suggestion?

    M.

  4. #49
    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 shg View Post
    Marcelo,

    VarType(cell.Value2) always returns vbEmpty, vbDouble, vbString, vbBoolean, or vbError
    What VarType returns for '1000? vbDouble or vbString?

    M.

  5. #50
    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?

    I checked: returns 8 ---> Text

    M.

  6. #51
    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?

    ....^....1
    Last edited by shg; 08-26-2013 at 01:56 PM.

  7. #52
    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 would do what Excel does by default -- left-align numeric strings, like all other text.

  8. #53
    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?

    Tested in Excel 2002...

    Data Range
    A B C
    1 Date Debit Credit
    2 8/1/2013 21.00
    3 8/2/2013 51.00
    4 8/5/2013 80.00 82.00
    5 8/8/2013 12.00
    6 8/8/2013 55.00 52.00
    7 8/15/2013 65.00 44.00
    8 8/16/2013 35.00 61.00
    9 8/16/2013 91.00
    10 8/26/2013 69.00 35.00
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #54
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,157

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

    I've not tried it but in Excel 2010> you can select a range>file>save as>file type mhtml dont click save but click publish, you could then use the source code from the resulting mhtml page.
    Not all forums are the same - seek and you shall find

  10. #55
    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 shg View Post
    I would do what Excel does by default -- left-align numeric strings, like all other text.
    Ok, i'll try tonight in my next version (i have to work a little bit on my job or ...... FIRED )



    New version dealing with 2/3 letters columns (added a new function)

    Data Range
    Z AA AB
    1 Name Score Rank
    2 Anthony 34 2
    3 John 30 5
    4 Mary 33 3
    5 Mike 38 1
    6 Robert 32 4

    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]" & ColLetters(rCell.Column) & "[/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
    
    Function ColLetters(lCol As Long) As String
        With ActiveSheet.Columns(lCol)
            ColLetters = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
        End With
    End Function

  11. #56
    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?

    Just did a copy/paste back to Excel test to make sure no html "junk" shows up in the empty cells.

    Looks good!

  12. #57
    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?

    oops i copied the old Sub

    This is the correct version

    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

  13. #58
    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 Simon Lloyd View Post
    I've not tried it but in Excel 2010> you can select a range>file>save as>file type mhtml dont click save but click publish, you could then use the source code from the resulting mhtml page.
    I tried in Excel 2007
    The resulting table code:

    <table border=0 cellpadding=0 cellspacing=0 width=192 style='border-collapse:
    collapse;table-layout:fixed;width:144pt'>
    <col width=64 span=3 style='width:48pt'>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 width=64 style='height:15.0pt;width:48pt'>Name</td>
    <td class=xl159881 width=64 style='width:48pt'>Score</td>
    <td class=xl159881 width=64 style='width:48pt'>Rank</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 style='height:15.0pt'>Anthony</td>
    <td class=xl159881 align=right>34</td>
    <td class=xl159881 align=right>2</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl649881 style='height:15.0pt'>John</td>
    <td class=xl649881 align=right>30</td>
    <td class=xl649881 align=right>5</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 style='height:15.0pt'>Mary</td>
    <td class=xl159881 align=right>33</td>
    <td class=xl159881 align=right>3</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl639881 style='height:15.0pt'>Mike</td>
    <td class=xl639881 align=right>38</td>
    <td class=xl639881 align=right>1</td>
    </tr>
    <tr height=20 style='height:15.0pt'>
    <td height=20 class=xl159881 style='height:15.0pt'>Robert</td>
    <td class=xl159881 align=right>32</td>
    <td class=xl159881 align=right>4</td>
    </tr>
    <![if supportMisalignedColumns]>
    <tr height=0 style='display:none'>
    <td width=64 style='width:48pt'></td>
    <td width=64 style='width:48pt'></td>
    <td width=64 style='width:48pt'></td>
    </tr>
    <![endif]>
    </table>

    Am i missing something?

  14. #59
    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?

    This is already a great tool and I will be using it extensively (have used it several times today already).

    Hats off to Marcelo for the work he has done on this!

  15. #60
    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
    This is already a great tool and I will be using it extensively (have used it several times today already).

    Hats off to Marcelo for the work he has done on this!
    Tony,

    Thank you very much for your kind words.

    I am very happy for creating a tool, i think, will be very useful for many members.

    But, as shg asked for a more sophisticated version, i.e., aligning according cells contents, here it goes:
    Numbers --> Right
    Text --> Left
    Boolean --> Center


    Data Range
    A
    B
    C
    D
    1
    Name
    Scores
    V/F
    Date
    2
    John
    30
    VERDADEIRO
    01/01/2013
    3
    Mary
    33
    FALSO
    02/01/2013
    4
    Mike
    34
    FALSO
    03/01/2013
    5
    William
    36
    VERDADEIRO
    04/01/2013

    Portuguese -- English
    VERDADEIRO ---> TRUE
    FALSO ---> FALSE

    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
    
    Private 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
        Dim strAlign 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][CENTER]" & ColLetters(rCell.Column) & "[/CENTER][/td]"
            Next rCell
            sReturn = sReturn & "[/tr]"
        End If
        
        For Each rRow In rInput.Rows
            sReturn = sReturn & vbNewLine & "[tr]"
            If bHeaders Then
                sReturn = sReturn & "[td][CENTER]" & rRow.Row & "[/CENTER][/td]"
            End If
            
            For Each rCell In rRow.Cells
                Select Case VarType(rCell.Value2)
                    Case 8 'Sring
                        strAlign = "LEFT"
                    Case 11 'Boolean
                        strAlign = "CENTER"
                    Case Else 'Others
                        strAlign = "RIGHT"
                End Select
            
                strAux = Right("000000" & Hex(rCell.Font.Color), 6)
                strColor = "#" & Right(strAux, 2) & Mid(strAux, 3, 2) & Left(strAux, 2)
                sReturn = sReturn & "[td][COLOR=""" & strColor & """][" & strAlign & "]" & _
                    rCell.Text & "[/" & strAlign & "][/COLOR][/td]"
            Next rCell
            
            sReturn = sReturn & "[/tr]" & vbNewLine
        Next rRow
        
        sReturn = sReturn & "[/table]"
        RngToHTML = "Data Range" & vbNewLine & sReturn
    End Function
    
    Private Function ColLetters(lCol As Long) As String
        With ActiveSheet.Columns(lCol)
            ColLetters = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
        End With
    End Function
    That's it

    M.

+ Reply to Thread
Page 4 of 17 FirstFirst ... 2 3 4 5 6 14 ... 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