CODEWITHSUNDEEP
htmlexcelvbscript
Jill448
Jill448

Reputation: 1793

Read from html and write to Excel

I want read html and write few columns from it into an Excel table.I am currently using a macro to do it, but need it in VBScript.

I want to count the number of fail occurrences for compliance check and oracle table and write it into an Excel doc.

Full size image of sample html and desired Excel file result.

Sample html and desired excel output

Upvotes: 1

Views: 2001

Answers (2)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200503

Excel can be controlled from VBScript like this:

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wb = xl.Workbooks.Add

The HTML file could be parsed into a DOM document:

Set doc = CreateObject("Msxml2.DOMDocument.6.0")
doc.async = True
doc.load "C:\path\to\your.html"

Use an XPath expression to select al <td> elements:

Set td = doc.selectNodes("//tr/td")

At this point td contains a collection of all <td> elements in the document. You can process them like this:

numrows = doc.selectNodes("//tr").Length
numcols = td.Length / numrows

row = 0
For i = 0 To td.Length - 1 Step numcols
  If td(i).Text = "Fail" Then
    row = row + 1
    wb.Sheets(1).Cells(row, 1).Value = CDate(Split(td(i+2).Text)(0))
    If InStr(td(i+1).Text, "compliance") > 0 Then
      wb.Sheets(1).Cells(row, 2).Value = 1
    ElseIf InStr(td(i+1).Text, "Oracletable") > 0 Then
      wb.Sheets(1).Cells(row, 3).Value = 1
    End If
  End If
Next

The above will create a table like this:

2/9/2012    1    
2/9/2012         1
2/9/2012         1
.
.
.

You can then use Excel's Consolidate method to consolidate the data:

Const xlSum = -4157

wb.Sheets(2).Range("A1").Consolidate _
  Array(wb.Sheets(1).Name & "!R1C1:R" & row & "C3"), xlSum

Upvotes: 3

Tamar
Tamar

Reputation: 2066

You can use any dom library to read in the html and the OpenXML sdk to write out to Excel (2007 format - xlsx). Does that answer your question or do you have something specific you are struggling with?

EDIT

Sorry, I thought for some reason you were talking about doing it in VB.Net, now I realize you are already in Excel. So I'm not clear on what you are asking - how to open the html file? how to calculate or store the values?

Perhaps post the script you have so far and be specific on what is not working.

Upvotes: 0

Related Questions