Using a Redmine export as a spreadsheet data source?
I've played plenty with connecting Excel directly to a Redmine database for reporting, but that's not a good solution for regular users... so what I'm curious about is whether anyone has had luck loading a CSV export into Excel as a refreshable data source.
This would be a nice way for any user to take their saved queries and work with them entirely from a spreadsheet, rather than diving into Redmine's UI.
RE: Using a Redmine export as a spreadsheet data source? - Added by Joshua DeClercq almost 5 years ago
For anyone who is similarly interested in making an Excel workbook that can sync with a saved Redmine query, I've gotten far enough along in putting something together that I think it's best to share it. Consider the following VBA:
Sub GetCSV() ' Adapted from http://stackoverflow.com/questions/1272389/mantis-bt-export-request-via-winhttp-winhttprequest-5-1 Dim webClient As Object Dim i As Long, vFF As Long, oResp() As Byte Dim vLocalFile As String Dim username As String, password As String Dim myURL As String Dim oRespStr As String username = InputBox(Prompt:="Redmine username:", Title:="Log in") ' Password is not masked. Wasn't sure how to do this and didn't look into it. password = InputBox(Prompt:="Redmine password:", Title:="Log in") ' Fill out the following as appropriate to your server's configuration. ' This assumes you have two cells in your workbook somewhere named RMpid, for the project identifier, ' and RMqid, for the numerical query ID. You're welcome to just insert the values here instead. myURL = "http://yourredmine/projects/" & Range("RMpid") & "/issues.csv?query_id=" & Range("RMqid") Set webClient = CreateObject("WinHttp.WinHttpRequest.5.1") ' Update this URL, too. webClient.Open "POST", "http://yourredmine/login", False webClient.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" webClient.send ("username=" & username & "&password=" & password & "&perm_login=on") webClient.Open "GET", myURL, False webClient.send oResp = webClient.responseBody ' You'll need to have a reference enabled for MS Forms to do this part. With New MSForms.DataObject .SetText StrConv(oResp, vbUnicode) .PutInClipboard End With ' Assumes a sheet exists in your workbook named QueryData where the export will paste to. Application.DisplayAlerts = False Sheets("QueryData").Delete Application.DisplayAlerts = True Sheets.Add(After:=Sheets(Sheets.Count)).Name = "QueryData" Sheets("QueryData").Select Range("A1").Select ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False, NoHTMLFormatting:=True Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True Range("A1").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection, ActiveCell.SpecialCells(xlLastCell).Offset(-1,0)), , xlYes).Name = _ "RedmineQuery" Range("RedmineQuery").Columns.AutoFit ' Assumes the first sheet in the workbook is what you want to look at after you're done. Worksheets(1).Activate Set webClient = Nothing ActiveWorkbook.RefreshAll End Sub
It's a big ol' mess of different code and manual macros hacked together, but it works. Note the comments to get it going. When you run it, you'll be asked for your credentials, and it will proceed to populate and name a CSV export as a range of values.
There may be a much better way to parse the string into a table, but this worked, and I was in a rush.
If you set up a pivot table mapped to the data range's name (RedmineQuery), it should work reliably refresh after refresh. The best part is, it can be safely distributed to any Redmine user, since access will always be limited to what Redmine is already restricting them to.