从网站导入数据：将Java脚本代码转换为VBA代码

I need to import some data from this website, for which I have written a small java script code which is mentioned below.

我需要从这个网站导入一些数据,为此我编写了一个小的java脚本代码,如下所述。

Code for extracting Molecular Weight

提取分子量的代码

x = document.getElementById('summary')
y = x.getElementsByTagName('h2')
count = x.getElementsByTagName('h2').length
flag=0
for (i = 0; i < count; i++){
  if(y.item(i).textContent == 'Molecular Weight'){
    console.log(y.item(i).nextSibling.textContent);
   flag=1;
  }
}
if(flag==0)
console.log("Nothing Found");

Code for extracting data from physical properties table

从物理属性表中提取数据的代码

x=document.getElementById('physical')
y=x.getElementsByClassName('TableRow')
count =x.getElementsByClassName('TableRow').length
for(i=0;i<count;i++){
   z=x.getElementsByClassName('TableRow').item(i)
   z.children[0].textContent
   z.children[1].textContent
}

So Is it possible to import to excel through java script?

那么可以通过java脚本导入excel吗?

I think VBA will be better for this purpose but I am new to VBA and not able to identify the appropriate way to write the code in VBA. Please give me hint so that I can proceed in right direction.

我认为VBA会更好用于此目的,但我是VBA的新手,并且无法确定在VBA中编写代码的适当方法。请给我提示,以便我可以朝着正确的方向前进。

1 个解决方案

#1

Instead of importing through javascript, you might want to try the following. In excel you can import a website into a spreadsheet by going to File -> Open Url. Assuming the structure of the site is the same regardless of the molecule you are looking at you could create VBA script to extract the data just by looking at the cell address. You can use Tools -> Macro -> Record New Macro to record your actions. Then once done you can view the VBA code by going to Tools -> Visual Basic Editor. Then you can improve the code from there to further automate the process.

您可能希望尝试以下操作,而不是通过javascript导入。在excel中,您可以通过转到文件 - >打开URL来将网站导入电子表格。假设无论您正在查看哪个分子,站点的结构都是相同的,您可以创建VBA脚本,仅通过查看单元格地址来提取数据。您可以使用工具 - >宏 - >记录新宏来记录您的操作。完成后,您可以转到工具 - > Visual Basic编辑器查看VBA代码。然后,您可以从那里改进代码,以进一步自动化该过程。

Here is an example:

这是一个例子:

Workbooks.Open Filename:="http://chem.sis.nlm.nih.gov/chemidplus/rn/112-92-5"
Range("A44").Select
Selection.Copy
Sheets.Add
Sheets("Sheet2").Select
Range("B4").Select
ActiveSheet.Paste