-2

I am trying to pull minimum nighttime temperature for London from the Met office website using a VBA web scrape. I've tried to use code as posted here. While the code runs it isnt copying what i need it be copying. Assistance would be much appreciated.

Sub WebScrape_1()

'Create an Internet Explorer browser
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")

'Browse the browser to the target webpage
With appIE
    .Navigate "https://www.metoffice.gov.uk/public/weather/forecast/gcpvj0v07"
    .Visible = True ' False activate when happly with code
End With

'Wait while loading
Do While appIE.Busy
    DoEvents
Loop

'What aspect of the webpage to copy
Set allRowOfData = appIE.document.getElementById("nightValue0")

Dim myValue As String: myValue = allRowOfData.innerHTML

'Close the browser
appIE.Quit
Set appIE = Nothing

'Paste the data into the selected range
ActiveWorkbook.Sheets("Data_Temp").Range("C7").Value = myValue

End Sub
Karm
  • 111
  • 1
  • 5
  • 3
    `no luck` does not describe any kind of a problem ... i do not think that anybody here will waste their time guessing what that means – jsotola Oct 17 '17 at 15:27
  • 1
    Agree with @jsotola. Do you get some results? Wrong results? No results? An error message? You need to provide more information about what's happening when you run your code. – freginold Oct 17 '17 at 15:37
  • Updated the question slightly. Unsure how to make my code copy the relevant section of the webpage. – Karm Oct 17 '17 at 15:37
  • I have quickly checked your code. For me it works for the IE-part, so I end up with `myValue` holding the `` element with the temperature of the night. As this is still HTML-code, you need some string handling to extract the temperature. Set a breakpoint on the `Quit`-statement and check what the content of `myValue` is. – FunThomas Oct 17 '17 at 15:43
  • Question: What the heck are you doing with VBA4, when VBA7 has been current since the advent of 64-bit data types, almost a decade ago, and before that VBA6 was all over the place, since before the beginning of the millenium? – Mathieu Guindon Oct 17 '17 at 15:52
  • @Mat'sMug Think you are getting this from the title. There are a number of questions with this title I just happen to be the fourth one – Karm Oct 17 '17 at 16:12
  • 2
    Oh (*facepalm*) - perhaps consider using a title that actually describes what the problem is instead? People come to this site through search engines when they are facing a specific coding issue: having a searchable title that's more or less what you would google up to find a solution for it, makes a much better question for this site. – Mathieu Guindon Oct 17 '17 at 16:13

1 Answers1

2

All you have to do is add "FirstChild" and "innertext" to your code. If I were you I would write the code differently especially on the "wait while loading" piece. It is not an efficient way to do it. Anyways, find your working code below:

Sub WebScrape_1()

'Create an Internet Explorer browser
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")

'Browse the browser to the target webpage
With appIE
    .Navigate "https://www.metoffice.gov.uk/public/weather/forecast/gcpvj0v07"
    .Visible = True ' False activate when happly with code
End With

'Wait while loading
Do While appIE.Busy
    DoEvents
Loop

'What aspect of the webpage to copy
Set allrowofdata = appIE.document.getElementById("nightValue0").FirstChild
allrowofdata = allrowofdata.innertext

'Close the browser
appIE.Quit
Set appIE = Nothing

'Paste the data into the selected range
ActiveWorkbook.Sheets("Sheet1").Range("C7").Value = myValue

End Sub
Nick is tired
  • 6,860
  • 20
  • 39
  • 51
Boris
  • 21
  • 3
  • 2
    It's not about being rude, it's about asking clear questions that are useful for others, not just the OP - *that is the purpose of this site*: Stack Overflow is not a "help **ME** debug **MY** code" site. You should already know that though - I'm sure you've taken the [tour] and read [ask] before? With 1 rep and 2 months of history you could have kept that meta-ranting to yourself. – Mathieu Guindon Oct 17 '17 at 15:56
  • Thaks @rlemmi I'll give this a try tomorrow when I am back in the office – Karm Oct 17 '17 at 16:14
  • You're welcome! Just don't forget to change your title to something more meaningful please. @Mat'sMug Apologies if you took it offensive. I just think it is good to be polite to new members. You want to foster a good community. You and your 1,000+ rating should know that by now. You could've asked him to change the title or his question in many better ways. Try to use the words "Please" or "Thank you" next time. I hope this user with 1 rep and 2 months of history was able to teach you something :). – Boris Oct 17 '17 at 16:51
  • 2
    Look at you, all green and innocent! I hope you keep that inner happiness... I think I'm growing increasingly irritated with the sheer quantity of crap questions in this tag, duplicates, people that want an answer *yesterday* but can't be bothered to ask a clear question, people that don't realize that SO isn't about *them* or *their code*, countless duplicates because nobody seems to bother *searching* before they ask, etc, etc, etc.. No offense taken, I just got annoyed at the meta-rant that doesn't belong on an otherwise good-looking answer to a poor question. Here, have an upvote. – Mathieu Guindon Oct 17 '17 at 16:58
  • Hahaha thanks! I will take that as a compliment? I completely agree with ALL your points though. It would frustrate me as well if I was a big contributor like you. All I want is to new users (like me) to have a good experience using this community. Of course we need to remind them to keep questions clear/searchable. Thanks for the upvote! Hey next time, just grab a beer and watch some baseball if you lose your temper. It will make your day better. – Boris Oct 17 '17 at 17:21