Read string from text file in url using VBA

Question

I have followed multiple links here but I am unable to get results I desire. I have a csv file at a url which has only 1 string in cell A1 "Test". I need to read the value "Test" from that url.

The url is:

url = https://website.com/xlsmUploaderUpdate.csv"

I used the macro recorder to generate the following:

Function Read_xmlUpdater() As String
    ActiveWorkbook.Queries.Add Name:="xlsmUploaderUpdate (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.FromColumns({Lines.FromBinary(Web.Contents(""https://mywebsite.com/xlsmUploaderUpdate.csv""), null, null, 1252)})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""xlsmUploaderUpdate (3)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [xlsmUploaderUpdate (3)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "xlsmUploaderUpdate__3"
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select
    Read_xmlUpdater = Range("A2").Select
End Function

But I am unable to modify the above code to actually get a return value "Test". How do I modify my code to get returned "Test" from the url.

Remove the .Select?

GSerg
– GSerg

2021-01-03 07:06:07 +00:00
Commented Jan 3, 2021 at 7:06 — GSerg
– GSerg, Commented Jan 3, 2021 at 7:06

Tim Williams · Accepted Answer · 2021-01-03 08:03:07Z

2

with workbooks.open("https://website.com/xlsmUploaderUpdate.csv")
    read_xmlupdater = .sheets(1).range("A1").value
    .close false
end with

answered Jan 3, 2021 at 8:03

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read string from text file in url using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related