Web query

[Guest]

In Macros i would like to write web query to extract data which consist of
more than 1500 rows, In Visual Basic the tables are mentioned as under
..WebTables = "3"

How can i mention 1500 tables by writing FOR Next....

or any other alternative.

 

[Don Guillett]

A for/next loop. How about the actual url and an your web query macro

 

[Guest]

Actual Web URL is "URL;http://www.sebi.gov.in/FIIIndex.jsp?fiiIndxName=O",
Destination:=Range( _
"A1"))
.Name = "FIIIndex.jsp?fiiIndxName=%"

it consist of more that 1500 rows and it requires to be ticked individually.
The macro for the same is as under;

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.sebi.gov.in/FIIIndex.jsp?fiiIndxName=O",
Destination:=Range( _
"A1"))
.Name = "FIIIndex.jsp?fiiIndxName=%"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone

X = 3

For X = 3 To 1500

.WebTables = X

Next X


.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

I am not getting all data from the above For Next ........... Can you
please suggest any other alternative to get full data.

Thank you

 

[Tim Williams]

If you don't mind some actual programming:

'****************************
Option Explicit

Sub Tester()

Dim IE As Object
Dim allTabs, t, x As Integer
Dim r As Long, s As String

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate2 "http://www.sebi.gov.in/FIIIndex.jsp?fiiIndxName=Q"

Do While IE.ReadyState <> 4
DoEvents
Loop
Set allTabs = IE.document.getelementsbytagname("TABLE")

r = 2
For Each t In allTabs
'tables with company info have 8 rows
If t.Rows.Length = 8 Then
For x = 0 To 7
s = t.Rows(x).Cells(4).innerHTML
s = Replace(s, "<BR>", vbLf)
ThisWorkbook.Sheets("Info").Cells(r, x + 1).Value = s
Next x
r = r + 1
End If
Next t

End Sub
'*******************************

Tim

 

[NickHK]

If you use:
..WebSelectionType = xlEntirePage

instead and delete the ".WebTables", you will get the whole page.
This does seem to be an incredibly slow server/connection, so it will take a
while.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.sebi.gov.in/FIIIndex.jsp?fiiIndxName=%",
Destination:=Range("A1"))
.Name = "AllEntries"
'....
.WebSelectionType = xlEntirePage
'....
.Refresh BackgroundQuery:=False
End With


NickHK