Redmine

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.