03/05/2011

PHPBB3 Tables and Excel

One of the forums I currently host and manage makes use of PHPBB3, the user’s post a lot of different tables (league standings e.t.c.) so being able to post tables is a must so I added some BB codes for [table], [tr] and [td] and they can now happily post all the tables they like.

The source data for these tables is held in excel, and having to upload and update 3 different tables each week is a little tiresome so I developed a little VBA Macro that will convert a selection to PHPBB3 Table code.

Throw a button on the toolbar and link it to the macro and job done!



Private Sub CommandButtonClose_Click()
    'closes the form
    End
End Sub

Private Sub CommandButtonCopy_Click()
    'Copies the content of the TextBoxTable
    Dim ansDataO As DataObject
    Set ansDataO = New DataObject
   
    ansDataO.SetText TextBoxTable.Text
    ansDataO.PutInClipboard
End Sub



Private Sub UserForm_Initialize()
    Dim i_ColumnCount As Integer
    Dim i_RowCount As Integer
    Dim i_ColumnLoop As Integer
    Dim i_RowLoop As Integer
   
    'Gets the number of columns / rows in our selection
    i_ColumnCount = Selection.Columns.Count
    i_RowCount = Selection.Rows.Count
 
    'stores the phpbb code
    TextBoxTable.Text = "[table]"
   
    'loops through each row in our selection
    For i_RowLoop = 1 To i_RowCount
        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[tr]"
           
        'for each row we loop, now loop through the column
        For i_ColumnLoop = 1 To i_ColumnCount

            TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[td]"
           
            TextBoxTable.Text = TextBoxTable.Text & Selection.CurrentRegion.Cells(i_RowLoop, i_ColumnLoop).Value
                   
            TextBoxTable.Text = TextBoxTable.Text & "[/td]"
        Next

        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/tr]"
    Next
       
    TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/table]"

End Sub


5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Can you tell me how you did this or can you provide me with the info how to be able to make a extra button?

    ReplyDelete
  3. What version of Microsoft Office do you have?

    ReplyDelete
    Replies
    1. At the moment the latest but other people should also be able to add a excel sheet that then should be able to be added by other moderators

      Delete
  4. This article explains how to add buttons to forms and also to the quick access ribbon http://www.extendoffice.com/documents/excel/878-excel-insert-macro-button.html

    ReplyDelete