Export Trello to Excel to Access to Excel

 in Excel  tagged excel / export / project / management / import / access / Trello by

Trello is an open source web based project management application which organizes your project information into boards. A board could represent a project. Within each board you create cards that represent comments, requirements or bugs that were found. Each card is placed in a column of cards which might represent the stages that a project goes through and perhaps the name of the person who added the card as an input. You also have the ability to attach documents (PDF, Word, Excel and so on) to a card and add comments to a card. You can move cards to different boards. For example you have an input card reporting a software bug that was fixed by the development and you want to move that to the column called Staging Release. In other words, you can set this up any way you want.

You may have the need to work with, analyze and report that data to other users in your project. Trello has several boards where you categorize the project based on criteria you set up. Each category (column) could be inputs from a team of people or persons. It could be based on time. For example a comment may be reporting a bug in an imput column and then moved to a “fixed on ” column. The Excel that Trello produces has in the first column, the name of the Trello column.

Here are the major steps involved.

  1. Export from Trello to Excel
  2. Import Excel into Access
  3. Create Queries you want
  4. Export all Queries to an Excel file where each query is in its own worksheet
  5. Open Excel and Save As an Excel Macro-Enabled Workbook (*.xlsm)
  6. Format data in all Excel worksheets at once with the code below

Export from Trello to Excel

There is an article at Oodles Technologies that explains how you can export “all” of your Trello data to Excel.

This solution relies on a Chrome Web store extension named Export for Trello. You will need to install this first.

Import Excel into Access

First, create an Access file. In the menu, click on External Data, New Data Source, From File, Excel. Work through the short Wizard. Browse out to find the location of the Excel file you exported from Trello. Select the radio button: Import the source data into a new table in the current database. Select the worksheet you want to import and click Next. Since the first row does contain column headings, click the check box at the top of the next page in the wizard and click Next. Just click Next in the next page of the wizard as you don’t need to define the data in the columns. Go ahead and let Access define a primary key by adding a column called ID and populating it with a sequence of numbers. Click Next. Change the name of the table being created, if you want. Click Finish with analyzing the data. Click Save import steps and provide a description in the next screen. Click the Save Import button. You are done.

Create Queries

Create the queries you need. Once you create them you won’t need to create them again. You many want a separate query for each Trello column. You just need to create a filer in the Criteria row of the graphical interface. Just surround the first column name with single quotes. You may want to create a query that runs other filters by combining similar columns. You may want to create queries that filter out certain label values.

Export all Queries to Several Excel Files

Now you can create a form in Access that allows you to export all of the queries to a series of Excel sheets, one for each query. Once that is done, the next step will combine all of the Excel files into one Excel file with separate worksheets for each query.

Here is a website at UtterAccess the explains how to do this step. I needed to change the code from Tables to Queries and the resulting code is below. I needed to change code in the following line numbers: 22 to 26, 29 and 31. For example, instead of TableDef, we need QueryDef and instead of strTables we should be using strQueries. 

Option Compare Database
Option Explicit
Private Sub Command4_Click()
  Dim strFile As String
  Dim varItem As Variant
  strFile = InputBox("Path and file name to export to...", "Export")
    
  If (strFile = vbNullString) Then Exit Sub
    
  For Each varItem In Me.List0.ItemsSelected
        DoCmd.TransferSpreadsheet transferType:=acExport, _
                spreadsheetType:=acSpreadsheetTypeExcel9, _
                tableName:=Me.List0.ItemData(varItem), _
                FileName:=strFile
  Next
  MsgBox "Process complete.", vbOKOnly, "Export"
End Sub

Private Sub Form_Open(Cancel As Integer)
  ' put a list of all the queries into the list box
  Dim strQueries As String
  Dim qdf As QueryDef
  For Each qdf In CurrentDb.QueryDefs
        If (Left(qdf.Name, 4) <> "MSys") Then
            strQueries = strQueries & qdf.Name & ","
        End If
   Next
   strQueries = Left(strQueries, Len(strQueries) - 1)
    
   Me.List0.RowSource = strQueries
End Sub

Open Excel and Save As an Excel Macro-Enabled Workbook

Open your Excel file and save it as a new file that is macro-enabled and give it a new name.

Format data in all Excel worksheets at once

There are a few macros you can run to do things in all of your worksheets inside an Excel file. To open up the VBA code window, press Alt+F11. In the window at the upper left called Project – VBA Project, scroll down the list and double-click on ThisWorkbook to open a window to paste the code below. Save it. Go back to Excel and click Macros under the Developer tab. 

' Coded by Mike Porter and Dioni Paulo
' Date: 2018-09-13
'
Sub S00_RUN_ALL()
    S01_Set_Calibri_11
    S02_BoldRow1AllWorksheets
    S03_SelectA2CellinAllWorksheets
    S04_FreezeAllSheets
    S05_WidenColumnABHIJ_CD
    S06_SelectJ2CellinAllWorksheets
    S09_CreateHyperlinks
    S01_Set_Calibri_11   ' S09 for some reason changes the font
    S03_SelectA2CellinAllWorksheets
    S10_MakeFirstWorksheetActive
    MsgBox ("Done Formatting Excel Worksheets.")
End Sub
Sub S01_Set_Calibri_11()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
         With ws
            .Cells.Font.Name = "Calibri"
            .Cells.Font.Size = 11
         End With
    Next ws
End Sub
Sub S02_BoldRow1AllWorksheets()
    'make the first row bold in all worksheets
    Dim ws As Worksheet
    For Each ws In Worksheets
         With ws.Range("1:1")
            .Font.Bold = True
         End With
    Next ws
End Sub
Sub S03_SelectA2CellinAllWorksheets()
    ' do this before freezing
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
       ws.Select
       Range("A2").Select
    Next ws
End Sub
Sub S04_FreezeAllSheets()
   'Code source: www.contextures.com
   Dim wsA As Worksheet
   Dim ws As Worksheet
   Dim wbA As Workbook
   Dim strSel As String
   Dim lRsp As Long
   On Error GoTo errHandler
   Set wbA = ActiveWorkbook
   Set wsA = ActiveSheet
   strSel = Selection.Address
     Application.ScreenUpdating = False
     For Each ws In wbA.Worksheets
        ws.Activate
        ActiveWindow.FreezePanes = False
        Range(strSel).Select
        ActiveWindow.FreezePanes = True
     Next ws
     wsA.Activate
exitHandler:
     Application.ScreenUpdating = True
     Exit Sub
errHandler:
     MsgBox "Could not freeze all sheets"
     Resume exitHandler
End Sub
Sub S05_WidenColumnABHIJ_CD()
   ' WidenColumns Macro
   ' Double-click to widen columns to fit contents
    Dim ws As Worksheet
    For Each ws In Worksheets
         With ws
            .Columns("A:A").EntireColumn.AutoFit
            .Columns("B:B").EntireColumn.AutoFit
            .Columns("H:H").EntireColumn.AutoFit
            .Columns("I:I").EntireColumn.AutoFit
            .Columns("J:J").EntireColumn.AutoFit
            .Columns("C:C").ColumnWidth = 60.67
            .Columns("D:C").ColumnWidth = 40
         End With
    Next ws
End Sub
Sub S06_SelectJ2CellinAllWorksheets()
    ' do this before freezing
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
       ws.Select
       Range("J2").Select
    Next ws
End Sub
Sub S08_Optional_WrapTextAllCellsInAllWS()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Select
        Cells.WrapText = True
    Next ws
End Sub
Sub S09_CreateHyperlinks()
    Dim nRow As Integer, nCol As Integer
    nRow = 2
    nCol = GetColumnHeadingNum("Card URL")
    ' For all of the worksheets, convert text to hyperlinks in the
    ' "Card URL" column from row 2 down to, but not including the end, where the end is
    ' defined as an empty cell.
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        nCol = GetColumnHeadingNum("Card URL")
        nRow = 2
        ws.Select
        ws.Cells(nRow, nCol).Select
        Do While ws.Cells(nRow, nCol) <> ""
            ws.Hyperlinks.Add Cells(nRow, nCol), Cells(nRow, nCol)
            nRow = nRow + 1
        Loop
    Next
End Sub
Function GetColumnHeadingNum(ColumnHeading)
    ' S09_CreateHyperlinks() calls this function.
    Dim nRow As Integer, nCol As Integer
    Dim cString As String
    nRow = 1
    nCol = 1
    cString = UCase(Trim(Cells(nRow, nCol))) 
    Do While cString <> ""
        If cString = UCase(ColumnHeading) Then
            Exit Do
        End If
        nCol = nCol + 1
        cString = UCase(Trim(Cells(nRow, nCol)))
    Loop
    GetColumnHeadingNum = nCol
End Function
Sub S10_MakeFirstWorksheetActive()
    ' return the user to the first worksheet
    ActiveWorkbook.Worksheets(1).Activate
End Sub