Geek asks and answers

How to parse the answer for WinHTTP line by line: encoded CSV-encoded UTF-8?

As the next step for my successfully resolved problem ( I don’t understand why WinHTTP does NOT authenticate a particular HTTPS resource ) I need to parse the resulting CSV nicely. I am currently using the following solution:

If HTTPReq.Status = 200 Then
    If FSO.FileExists(CSV_Path) = True Then FSO.DeleteFile (CSV_Path)
    Set FileStream = CreateObject("ADODB.Stream")
    FileStream.Open
    FileStream.Type = 1
    FileStream.Write HTTPReq.responseBody
    FileStream.SaveToFile (CSV_Path)
    FileStream.Close
    ActiveWorkbook.Connections("Redmine Timelog").Refresh
    ActiveSheet.PivotTables("PivotTable_RM").PivotCache.Refresh
End If

That is, I save the CSV to disk, and then associate it as a data source with Excel. However, I would like my Excel workbook to be self-sufficient, without the need to create additional files (for some very obvious reasons).

: WinHTTP.responseText Excel ( CSV), Text to Data Excel. :

  • CSV UTF-8, WinHTTP - . ?
  • CSV ? Split - ? , CSV NewLine, 99% .

, VBA, . !

+2
2

, :

:

'CSV to UTF-8
Set FileStream = CreateObject("ADODB.Stream")
FileStream.Open
FileStream.Type = 1 'Binary
FileStream.Write HTTPReq.responseBody
FileStream.Position = 0
FileStream.Type = 2 'Text
FileStream.Charset = "UTF-8"
CSV_Text = FileStream.ReadText
FileStream.Close
'CSV Splitting
CSV_Strings = Split(Trim(CSV_Text), vbLf)
ThisWorkbook.Worksheets("RM_Log").Cells.ClearContents
Set OutputRange = ThisWorkbook.Sheets("RM_Log").Range("A1:A" & UBound(CSV_Strings) + 1)
OutputRange = WorksheetFunction.Transpose(CSV_Strings)
OutputRange.TextToColumns Destination:=ThisWorkbook.Sheets("RM_Log").Range("A1"), _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 3), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1)), DecimalSeparator:=".", _
    TrailingMinusNumbers:=True

Excel . , - . , - .

+3

OutputRange = WorksheetFunction.Transpose(CSV_Strings)


OutputRange.Formula = WorksheetFunction.Transpose(CSV_Strings)
0

More articles:


All Articles