How to obtain the row count of a QueryTable from a Web query
 

I use a Web query to obtain a table on a Web page. How can I know the number
of rows in the result QueryTable?

The current code I use is as follows:

Sub Test()
Dim MyStr As String
MyStr = "URL;http://mySite/currentuser"
With ActiveSheet.QueryTables.Add(Connection:=MyStr, _
Destination:=Range("a10"))

.RowNumbers = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "11"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


End Sub

How to obtain the row count of a QueryTable from a Web query
 

Hi,
I could not get much info, but please refer below link which may help you.

http://www.java2s.com/Code/VBA-Excel...erywithVBA.htm

http://blogs.msdn.com/excel/archive/...b-queries.aspx

http://www.mrexcel.com/tip072.shtml


Regards,
Sanjay

Mark "Yes", if the post if helpful.


--------------------------------------------

"Felix_Jiang" wrote:

I use a Web query to obtain a table on a Web page. How can I know the number
of rows in the result QueryTable?

The current code I use is as follows:

Sub Test()
Dim MyStr As String
MyStr = "URL;http://mySite/currentuser"
With ActiveSheet.QueryTables.Add(Connection:=MyStr, _
Destination:=Range("a10"))

.RowNumbers = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "11"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


End Sub

How to obtain the row count of a QueryTable from a Web query
 

?? Maybe something as simple as
msgbox cells(rows.count,"a").end(xlup).row

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Felix_Jiang" wrote in message
...
I use a Web query to obtain a table on a Web page. How can I know the
number
of rows in the result QueryTable?

The current code I use is as follows:

Sub Test()
Dim MyStr As String
MyStr = "URL;http://mySite/currentuser"
With ActiveSheet.QueryTables.Add(Connection:=MyStr, _
Destination:=Range("a10"))

.RowNumbers = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "11"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


End Sub

How to obtain the row count of a QueryTable from a Web query
 

Thank you Sanjay. I want to know the row count in the QueryTable returned by
ActiveSheet.QueryTables.Add. Then after I put the QueryTable into Excel, I
can calculate the last affected row in Excel. This will help me to know where
to start adding new QueryTable.

"Sanjay" wrote:

Hi,
I could not get much info, but please refer below link which may help you.

http://www.java2s.com/Code/VBA-Excel...erywithVBA.htm

http://blogs.msdn.com/excel/archive/...b-queries.aspx

http://www.mrexcel.com/tip072.shtml


Regards,
Sanjay

Mark "Yes", if the post if helpful.


--------------------------------------------

"Felix_Jiang" wrote:

I use a Web query to obtain a table on a Web page. How can I know the number
of rows in the result QueryTable?

The current code I use is as follows:

Sub Test()
Dim MyStr As String
MyStr = "URL;http://mySite/currentuser"
With ActiveSheet.QueryTables.Add(Connection:=MyStr, _
Destination:=Range("a10"))

.RowNumbers = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "11"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


End Sub

How to obtain the row count of a QueryTable from a Web query
 

I found the answer. The row count can be obtained by using
QueryTable.ResultRange.Rows.Count

"Felix_Jiang" wrote:

Thank you Sanjay. I want to know the row count in the QueryTable returned by
ActiveSheet.QueryTables.Add. Then after I put the QueryTable into Excel, I
can calculate the last affected row in Excel. This will help me to know where
to start adding new QueryTable.

"Sanjay" wrote:

Hi,
I could not get much info, but please refer below link which may help you.

http://www.java2s.com/Code/VBA-Excel...erywithVBA.htm

http://blogs.msdn.com/excel/archive/...b-queries.aspx

http://www.mrexcel.com/tip072.shtml


Regards,
Sanjay

Mark "Yes", if the post if helpful.


--------------------------------------------

"Felix_Jiang" wrote:

I use a Web query to obtain a table on a Web page. How can I know the number
of rows in the result QueryTable?

The current code I use is as follows:

Sub Test()
Dim MyStr As String
MyStr = "URL;http://mySite/currentuser"
With ActiveSheet.QueryTables.Add(Connection:=MyStr, _
Destination:=Range("a10"))

.RowNumbers = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "11"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


End Sub