0

I want to retrieve a table from the URL of https://s.cafef.vn/screener.aspx#data using VBA. This task is difficult because the table contains JSON data embedded in an html file, but it was so kind of Tanaike, an GAS expert who helped me to create a custom function for Google Apps Scripts. (IMPORTHTML() doesn't work in this webpage structure) The function looks like:

function SAMPLE(url) {
   const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
   const html = res.getContentText().match(/var jsonData \=([\S\s\w]+\}\])/);
   
   if (!html) return "No tables. Please confirm URL again.";
       
       const table = JSON.parse(html[1].replace(/\n/g, ""));
       
       const header = ["", "FullName", "Symbol", "CenterName", "ChangePrice", "VonHoa", "ChangeVolume", "EPS", "PE", "Beta", "Price"];
       
       return table.reduce((ar, e, i) => {

       const temp = header.map(f => f == "" ? i + 1 : e[f]);
       ar.push(temp);
       return ar;
   }, [header]);  }

This function works perfect in Google Sheets environment, but my goal now is to convert it into VBA, or in other words, writing a VBA module which can get the table at https://s.cafef.vn/screener.aspx#data.

Many thanks for any help or suggestions

Cao Doremi

Cao Doremi
  • 91
  • 6
  • 1
    Split up your task. Try to solve following individual problems one after another: **1)** How to send an HTTP request with VBA? ([example](https://stackoverflow.com/q/38246789)) Once you have the HTML - **2)** How to search for a substring? ([example](https://stackoverflow.com/q/8146485)) Once you have located the JSON string - **3)** How to parse JSON with VBA? ([example 1](https://stackoverflow.com/q/19360440) [example 2](https://stackoverflow.com/a/33317622) [example 3](https://stackoverflow.com/a/2782393)) – Tomalak Jul 06 '21 at 18:27
  • 1
    And then **4)** How to loop over the raw data from the JSON and write into an Excel table. Very probably nobody will come along to simply translate that entire piece of Apps Script code for you. The VBA solution will be quite a bit longer than that, and you will have to combine knowledge from all the links above, and probably from others you find while doing it. But when you start, and come across a *specific* question about one of the above steps: Post it as a stand-alone question, along with your code so far, then it's much more likely that people will jump in and help. – Tomalak Jul 06 '21 at 18:33

0 Answers0