-1
  1. tr---- 0495024988

  2. 14.08.1996

  3. 04/04/130/02514/AM96/

  4. 23.01.1996

  5. 0495024988

6. tr----(here the text is there which i have copy to my excel sheet)

here i have read many post where the javascript code is given but the vba code is not there. please help me out of this.

Community
  • 1
  • 1
Mahi Reddy
  • 9
  • 1
  • 2
  • 5
  • The main question is how do you get access to the HTML from Excel VBA. Please show how you are doing that. For example, if you have access to the DOM within a browser then the further methods are not much different between JavaScript and VBA. – Axel Richter May 22 '16 at 06:54
  • hi alex, the code i have used to access is pasted below. – Mahi Reddy May 27 '16 at 16:37

1 Answers1

0

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application Dim URL As String Dim i As Long, strText As String

Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object Dim tb As Object, bb As Object, tr As Object, td As Object

Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

Dim sws As SHDocVw.ShellWindows Dim IE As Object

Dim vIE As SHDocVw.InternetExplorer

Set wb = Excel.ActiveWorkbook Set ws = wb.ActiveSheet

Set appIE = CreateObject("InternetExplorer.Application") URL = "http://dgft.delhi.nic.in:8100/dgft/IecPrint"

y = 1 'Column A in Excel z = 1 'Row 1 in Excel

With appIE .navigate URL .Visible = True

Do While .busy: DoEvents: Loop Do While .ReadyState <> 4: DoEvents: Loop

.document.getElementById("iec").Value = "0495024988"

.document.getElementById("name").Value = "AMB"

End With

On Error Resume Next

With appIE.document

Set elems = .getElementsByTagName("input") For Each e In elems

If (e.getAttribute("value") = "Submit Query") Then e.Click Exit For End If

Next e

End With Set sws = New SHDocVw.ShellWindows For Each vIE In sws 'If Left(vIE.LocationURL, 4) = "http" Then 'avoid explorer windows/etc this way 'If MsgBox("IE Window found. The URL is:" & vbCrLf & vIE.LocationURL & vbCrLf & _ 'vbCrLf & "Do you want to see the html?", vbYesNo) = vbYes Then 'Show html in a msgbox ' MsgBox vIE.document.body.innerHTML 'Or put it to a file 'dim vFF as long 'vff=freefile 'open "C:\thehtml.txt" for output as #vff 'print #vff,vie.document.body.innerhtml 'close #vff ' End If 'End If

 Set doc = vIE.document
 Set hTable = doc.getElementsByTagName("table")


 For Each tb In hTable

    Set hBody = tb.getElementsByTagName("tbody")
    For Each bb In hBody

        Set hTR = bb.getElementsByTagName("tr")

        MsgBox hTR.Length
        For Each tr In hTR


             Set hTD = tr.getElementsByTagName("td")
             MsgBox hTD.Length
             y = 1 ' Resets back to column A
             For Each td In hTD

               ws.Cells(z, y).Value = td.innerText
               y = y + 1
             Next td
             DoEvents
             z = z + 1

        Next tr
        Exit For
    Next bb
Exit For

Next tb

Next

End Sub

Mahi Reddy
  • 9
  • 1
  • 2
  • 5