Data from Web

[SKFdave]

Hello everyone!

I have always been able to find a solution on this site to what ever excel or VBA problem I have had till now.

I need to pull data from a device with a built in web server. I have tried everything I can find to accomplish this and have made Zero progress. This is my first attempt to do such a thing and after three days of trying I need HELP! I tried using query and it does not find the data.

I need to pull the data as quickly as possible as it changes in milliseconds. then put the data into a cell for evaluation. the data in the grey area "Input id" is what I need.

Any help would be appreciated!

Dave

 

[Worf]

Welcome Dave

You posted in the Access area, is this correct? I can provide Excel guidance, as I am not an Access user.

It is relatively easy to transfer web data into Excel using VBA.

I am assuming we forum helpers cannot test with the actual server, is this correct?

 

[SKFdave]

Hello Worf, sorry for posting in the wrong forum. You are correct, the device has it's own web server and not publicly available. Any help is appreciated.

 

[Worf]

This example uses a conventional web page:

Public d As WebDriver

Sub Fri()
Dim col As Object
Const url As String = "https://www.aol.com"
Set d = New ChromeDriver
d.Start "Chrome"
d.get url
d.Wait 900
Set col = d.FindElementsByTag("input")                  ' create a collection
MsgBox col.Count, , "Number of input elements"
Set col = d.FindElementById("header-search-input")      ' get specific element
MsgBox col.tagName, , "Tag name"
End Sub

 

[SKFdave]

Hi Worf,

Thanks for the response, I am not able to get the code to function, because I do not have Chrome (company PC)

I have made some progress though I am now able to get the macro to run and get some data, I just figure out how to get the specific data I need. I attached two images that may help. the highlighted data is what I need.

The code is below

Sub ImportStackOverflowData()
Dim ie As InternetExplorer
Dim HTML As HTMLDocument

Set ie = New InternetExplorerMedium

ie.Visible = False
ie.navigate "http://169.254.168.150\EN\meas0020.html"
Do While ie.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
'show text of HTML document returned
Set HTML = ie.document
MsgBox HTML.DocumentElement.innerHTML
Set ie = Nothing
Set HTML = Nothing
Application.StatusBar = ""
Cells.Clear
End Sub

 

[SKFdave]

forgot the second attachment

 

[Worf]

This example shows how to get the element directly and also how to walk the DOM:

Sub WebData()
Dim ie As InternetExplorer, HTML As HTMLDocument, ob, i%, ob2
Set ie = New InternetExplorerMedium
ie.Visible = 1
ie.navigate "https://www.dropbox.com/login"
Do While ie.readyState <> READYSTATE_COMPLETE
    DoEvents
Loop
Set HTML = ie.Document
Set ob2 = HTML.getElementById("main-skip")
MsgBox ob2.id, , "Direct access"
Set ob = HTML.getElementsByTagName("form")
For i = 0 To ob.Length - 1
    MsgBox ob(i).ClassName, , i
Next
Set ob2 = ob(0).getElementsByTagName("div")
Set ob = ob2(0).getElementsByTagName("div")
Set ob2 = ob(0).getElementsByTagName("div")
For i = 0 To ob2.Length - 1
    MsgBox ob2(i).ClassName
Next
Set ob = ob2(1).getElementsByClassName("text-input-input")
MsgBox ob.Length & vbLf & ob(0).id, , "tii"
ob(0).Value = "manager@skf.com"                             ' write to page
End Sub

 

[SKFdave]

thanks Worf,

I am not making any progress with this and at this point I have no idea why. I don't even know what info I can give you to help you help me. Any advice?

 

[Worf]

The element has an ID so the following should work:

Sub WebData()
Dim ie As InternetExplorer, HTML As HTMLDocument, ob2 As Object
Set ie = New InternetExplorerMedium
ie.Visible = 1
ie.navigate "https://www.dropbox.com/login"     ' your address here
Do While ie.readyState <> READYSTATE_COMPLETE
    DoEvents
Loop
Set HTML = ie.Document
Set ob2 = HTML.getElementById("main-skip")      ' your Id here
MsgBox ob2.id, , "Direct access"
End Sub