Windows 10 Help with VBA Web Queries Excel

Bob07984

Active Member
In Excel, i have a Userform.
On this Userform is a Command Button, and a Label.
When i click the Command Button, i want excel to perform a web query, and then put the output into the caption of the label.

The web query is simply a .txt document on a webserver, containing one line of text, and i am trying to get excel to print this into the caption of the label.

So far, the code i have running when i click the Command Button is as follows:

Code:
Private Sub CommandButton1_Click()
  With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.exampleurl.com/textfile.txt", Destination:= _
        Range("A1"))
        .PostText = "local"
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 1
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

And this successfully puts the line of text contained in the txt file into cell A1, however i can't figure out how to tell it to put that text into the label, which is called "Label1", instead of the cell.

My VBA ability is very limited, so keep that in mind...

Hoping someone can help...
 

Neemobeer

Cloud Security Engineer
Staff member
Something like this?

Code:
Private Sub CommandButton1_Click()
Dim objWeb As Object
Dim str As String

Set objWeb = CreateObject("Microsoft.XMLHTTP")

objWeb.Open "GET", "https://filesamples.com/samples/document/txt/sample3.txt", False
objWeb.Send

str = objWeb.responseText
Label1.Caption = str

Set objWeb = Nothing
End Sub
 

Bob07984

Active Member
Hello, thanks for the suggestion 👍

However, when i try and use this code, i get the following error (Shown in screenshot)

I have tried to debug the code I.E i have changed the URL, added a comma here and there, added quotation marks here and there, tried a few semicolons, etc, however with no success.

As i said, when it comes to VBA my understanding really is limited, i suspect a small typo somewhere?

Thanks a lot though...

****EDIT - I have just looked through your code on the forum once again, and noticed it did not quite match the code i had pasted into my VBA editor, so i have copied and pasted again, and am getting more luck this time - i guess something got a bit muddled up during me transferring the code into excel, my bad. Seems to be working as intended now, thanks a lot! ****
 

Attachments

  • VBA Error.png
    VBA Error.png
    20 KB · Views: 18
Last edited:
Top