CODEWITHSUNDEEP
excelvba
Zanam
Zanam

Reputation: 4807

Read string from text file in url using VBA

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.

Upvotes: 0

Views: 375

Answers (1)

Tim Williams
Tim Williams

Reputation: 166181

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

Upvotes: 2

Related Questions