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

---