将ADODB二进制流转换为字符串vba [英] convert ADODB binary stream to string vba

问题描述

我遇到以下问题：
我有一个CSV文件，该文件存储在服务器上，但有3个字符作为定界符： [|]。我想使用[|]作为分隔符从URL加载数据并将数据填充到Excel页面的列中。到目前为止，我已经找到了使用ADODB记录集从网站加载文件的代码，但是我无法获得进一步的信息：

I have the following problem: I have a CSV file which is stored on a server but it has 3 characters as delimiters: "[|]". I would like to load the data from the URL and fill the data in the columns of my Excel page using the [|] as delimiter. Until now I found code to load the file from a website using an ADODB recordset but I cannot get any further:

 myURL = "http://www.example.com/file.csv"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1 'binary type
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "E:\file.csv", 2 ' 1 = no overwrite, 2 = overwrite   
oStream.Close
End If

保存效果很好直接文件。但是我不想将其保存到文件中，我想在适当的单元格中输入数据。有什么办法吗？我宁愿不使用Internet Explorer对象

This works fine to save a file directly. But I do not want to save it to a file, I want to enter the data in the proper cells. Is there any way to do this? I would prefer not tu use Internet Explorer objects

推荐答案

使用常规的csv文件测试正常：

Tested OK with a regular csv file:

Sub Tester()
    Dim myURL As String, txt As String, arrLines, arrVals
    Dim l As Long, v As Long, WinHttpReq As Object
    Dim rngStart As Range

    myURL = "http://www.mywebsite.com/file.csv"

    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False, "username", "password"
    WinHttpReq.send

    txt = WinHttpReq.responseText

    'might need to adjust vbLf >> vbCrLf or vbCr
    '  depending on the file origin (Win/Unix/Mac)
    arrLines = Split(txt, vbLf)

    Set rngStart = ActiveSheet.Range("A1")

    For l = 0 To UBound(arrLines)
        arrVals = Split(arrLines(l), "[|]")
        For v = 0 To UBound(arrVals)
            rngStart.Offset(l, v).Value = arrVals(v)
        Next v
    Next l

End Sub

这篇关于将ADODB二进制流转换为字符串vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！