[Solved] Importing HTML Source?

mokanman · Post by **mokanman** » Sat Feb 23, 2019 1:15 am

I have a spreadsheet with an external link to https://www.timeanddate.com/weather/@z-us-64746/ext which is once again working very well for me thanks to Villeroy. However, there is a column for wind direction that contains a graphic that always points north in the spreadsheet after downloading whereas the web page actually displays a graphic depicting the direction being forecasted. I doubt there is a way to convert that directly from that download, however, I'm wondering if there is a way to import the HTML source to parse it. I found, using Firefox, that I can view the source code which actually contains the information in degree increments (between 0 and 359). If I could somehow import that source then I could parse it based on the text layout and convert it to normal directional conventions (N,S,E,W). I found that the source contains the characters wd followed by a quote, then a colon, then the degree number followed by a comma. This should be easy to parse. I successfully copied the entire source manually from the Firefox source display into a new worksheet so it seems feasible. Does anyone know of a way to import HTML source into a spreadsheet.

Lupp · Post by **Lupp** » Sat Feb 23, 2019 3:17 am

The file is starting with

<!DOCTYPE html>

Of course, I'm sure that my script (most simple Basic) is not subject to permission by anybody. It may be different, however, with the evaluation of the content. I'm not a lawyer.

My crude code did NOT try to analyse the content. It simply was

Code: Select all

Sub Main
sheet = ThisComponent.Sheets(0)
rg = sheet.getCellRangeByName("A2:A10001") 
da = rg.getDataArray
url = "https://www.timeanddate.com/weather/@z-us-64746/ext"
If FileExists(url) Then
  num = FreeFile
  Open url For Input As #num
  k = -1
  While NOT EoF(#num)
    k = k + 1
    Line Input #num, oneLine
    da(k)(0) = oneLine
  Wend
  rg.setDataArray(da)
End If
Close #num   
End Sub

mokanman · Post by **mokanman** » Sat Feb 23, 2019 8:59 pm

I guess I need to start paying more attention to comments in HTML source code. I would have to agree, however, with your (humorous) interpretation of that vaguely and potentially mis-worded comment. Also, anyone using the external links capability of LibreOffice (and I'm sure many other available software) would never even know that comment was there unless they also happened to look at the source code. Anyway, it is good to know that your script is not subject to permission by anybody. Thanks for the reply and the helpful code!

mokanman · Post by **mokanman** » Sun Feb 24, 2019 10:57 pm

While I appreciate Lupp's helpful reply and will possibly use that knowledge for future endeavours I've actually switched my external link to a different web site that has all the information I need without requiring me to do any additional coding. I'm marking this issue "solved" as Lupp provided the information I requested and I have also pursued an alternative approach.

[Solved] Importing HTML Source?

[Solved] Importing HTML Source?

Re: Importing HTML Source?

Re: Importing HTML Source?

Re: Importing HTML Source?