0

I'm currently working on a vba code where I extract a graph, which is updated daily, from a website. I'll be running this code weekly to grab this graph.

Originally I extracted the graph into my Excel workbook by referencing the chart's element source code:

Sheets("Chart").Shapes.AddPicture "https://charts.website.com/c/422/charts/ClearChart_2054_980961381630.jpg" _
                      , msoFalse, msoTrue, 100, 100, 500, 600

My problem is that the _980961381630.jpg changes daily.

I'm trying to understand how to use VBA to update _980961381630 so that when I run the code I get the most recent, updated, table.

Right now I'm trying to use Extracting Specific Elements VBA and Extracting Varying Element Source Code as a resource.

The HTML element source code is:

<img chart-id="2672" alt="Chart ID 2672" class="chart-img" 
src="https://charts.website.com/c/422/charts/ClearChart_2054_758170607617.jpg" 
diagnostic-id="chart-img-2" style="max-height: 150px;">
a_js12
  • 329
  • 1
  • 8
  • You will get much better help if you post relevant parts your code as it looks now. – Sam Sep 16 '20 at 12:55
  • You could use something like [getElementByClass](https://stackoverflow.com/questions/17965956/how-to-get-element-by-class-name) to capture the image name – Zac Sep 16 '20 at 14:25

1 Answers1

0

You can scrape the current url. If there is no dynamic content to load, you can do it with a http xml request. Otherwise you must use the IE.

Try this for http xml request:

Sub ImportChart()

Const url As String = "https://charts.website.com/c/422/charts/" 'Page with chart

Dim xhr As Object
Dim htmlDoc As Object
Dim nodeChartLink As Object
Dim chartLink As String

  'Initialize variables
  Set htmlDoc = CreateObject("htmlfile")
  Set xhr = CreateObject("MSXML2.ServerXMLHTTP.6.0")
  
  '"Open" page
  xhr.Open "GET", url, False
  xhr.send
  'Build html document for DOM operations
  htmlDoc.body.innerHTML = xhr.responseText
  
  'Get chart url
  Set nodeChartLink = htmlDoc.getElementByID("2672")
  chartLink = nodeChartLink.src
  
  'Place chart in excel sheet
  Sheets("Chart").Shapes.AddPicture chartLink, msoFalse, msoTrue, 100, 100, 500, 600
End Sub
Zwenn
  • 2,147
  • 2
  • 8
  • 14
  • I like this idea a lot. I just gave this code a run and got an error on the line `chartLink = nodeChartLink.src` . The error says `Object variable or With block variable not set`. I'm not sure why this error is coming up so I'll have to look do some digging, but I will get back to you, after debugging, and let you know how the code works out. Thanks! – a_js12 Sep 16 '20 at 13:59
  • I would have expected this error one line before, if it occurs. Can you publish the URL? – Zwenn Sep 16 '20 at 18:20
  • I think its because of the way the site is set up which is kind of tricky. Once you enter into the website there are "reports" which include a booklet of charts to choose from. After finding the chart that you want, within the booklet, you can "download" the chart which opens up a new internet explorer with the url that I provided in my original post. All the charts, if downloaded have the same url, but the Element ID is different. I'm sorry, this was something I should've included in my OP, but now I'm thinking my whole code needs to be scrapped and rewritten. – a_js12 Sep 16 '20 at 19:20
  • I ask again. Can you publish the URL? It's not possible to help you without detailed information about the page. If I understand it right a popup occours. You must catch that popup. Here is an example how you can do that. It's not trivial. https://stackoverflow.com/questions/59113561/dom-vba-ie11-automate-placing-orders-on-a-website-trouble-with-onchange-and-pi/59115782#59115782 – Zwenn Sep 17 '20 at 08:05