I hope my question is not redundant for this forum; I only want the most rudimentary solution to get data from a series of RSS links that provides data in XML format into a series of blocks (sets of columns of rows and sheets) in an additional sheet. I am working in Excel 2003 (and Windows XP, if that matters).
Here is what I have so far: a series of URLs to the RSS feeds in column F starting in row 2 and going to row 5858.
Each of those URLs generates data that fits in columns A through R, and generally spans rows 1 (row 1 has column headers) to typically around row 22. There may be some variability in the numbers of rows but the number of columns should be consistant.
Here is the code I have:
******************
Sub Macro1()
Dim i As Long
Dim LR As Long
Dim lr1 As Long
LR = Cells(Rows.Count, "F").End(xlUp).Row
For i = 2 To LR
With ActiveWorkbook.XmlMaps("rss_Map" & i)
.ShowImportExportValidationErrors = False
.AdjustColumnWidth = True
.PreserveColumnFilter = True
.PreserveNumberFormatting = True
.AppendOnImport = False
End With
lr1 = Cells(Rows.Count, "G").End(xlUp).Row
ActiveWorkbook.XmlImport URL:=Cells(i, "F").Value, _
ImportMap:=Nothing, Overwrite:=True, Destination:=Sheets("Sheet1").Range("$A" & lr1)
Next i
End Sub
********************
In the Sheet1, I do get the entry of data from the first cell in the series (F2) from the sheet that contains the URLs to the RSS feeds. Those data appear in columns A through R and rows 1 through 22, but then I get the following error message:
The error message I get is: Run-time error '1004': The operation cannot be completed because the XML list is bound to a different XML Map.
So what I need is that part that would return the XML data from each subsequent URL of the RSS feeds (in column F, rows 3 through 5858) from the source sheet--to successive blocks (with each new set starting, say, 30 rows down from the previous one).
Is that possible? What would be the modification to the code in the macro? Am I going to be limited by the number of rows allowed in Excel 2003? It is going to be somewhere around 5858 X 30 = 175,740 rows.
Here is what I have so far: a series of URLs to the RSS feeds in column F starting in row 2 and going to row 5858.
Each of those URLs generates data that fits in columns A through R, and generally spans rows 1 (row 1 has column headers) to typically around row 22. There may be some variability in the numbers of rows but the number of columns should be consistant.
Here is the code I have:
******************
Sub Macro1()
Dim i As Long
Dim LR As Long
Dim lr1 As Long
LR = Cells(Rows.Count, "F").End(xlUp).Row
For i = 2 To LR
With ActiveWorkbook.XmlMaps("rss_Map" & i)
.ShowImportExportValidationErrors = False
.AdjustColumnWidth = True
.PreserveColumnFilter = True
.PreserveNumberFormatting = True
.AppendOnImport = False
End With
lr1 = Cells(Rows.Count, "G").End(xlUp).Row
ActiveWorkbook.XmlImport URL:=Cells(i, "F").Value, _
ImportMap:=Nothing, Overwrite:=True, Destination:=Sheets("Sheet1").Range("$A" & lr1)
Next i
End Sub
********************
In the Sheet1, I do get the entry of data from the first cell in the series (F2) from the sheet that contains the URLs to the RSS feeds. Those data appear in columns A through R and rows 1 through 22, but then I get the following error message:
The error message I get is: Run-time error '1004': The operation cannot be completed because the XML list is bound to a different XML Map.
So what I need is that part that would return the XML data from each subsequent URL of the RSS feeds (in column F, rows 3 through 5858) from the source sheet--to successive blocks (with each new set starting, say, 30 rows down from the previous one).
Is that possible? What would be the modification to the code in the macro? Am I going to be limited by the number of rows allowed in Excel 2003? It is going to be somewhere around 5858 X 30 = 175,740 rows.
