Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTable.add naming problem (web query) | Excel Programming | |||
Problem:Query Cell contain Formula from QueryTable is always get old value | Excel Programming | |||
Excel Web Query using a QueryTable via HTTPS?? | Excel Programming | |||
Bad QueryTable Row Count | Excel Programming | |||
MS QUERY w/out querytable | Excel Programming |