469,072 Members | 1,802 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,072 developers. It's quick & easy.

looping thru URL

I have a list of urls that i would like to keep looping thru. Every time the url is opened curtain data is scraped out then posted in correct row and columns. so far I can get my marco to loop thur the url but the data that i want is not posting coreectly. please let me know how to fix. I have attached a copy of the speadsheet for you to review.
Attached Files
File Type: zip TEST RSS.zip (34.4 KB, 79 views)
Sep 14 '12 #1
5 1919
TheSmileyCoder
2,321 Expert Mod 2GB
Hi lasilva and welcome to Bytes.

Please do not take offense, but since this is your first post and I as such, do not know you, I have no intention of downloading attachments from you, and I believe that many of our experts feels the same way.

If you would care to poste the code here, indicating where you believe the problem might be, I would be happy to take a look at it for you.
Sep 14 '12 #2
ok Here is the code I am using now

Expand|Select|Wrap|Line Numbers
  1. Sub URL()
  2. ' Keyboard Shortcut: Ctrl+Shift+L
  3. Dim ARRURL() As String ' for splitting the URL
  4. Dim QT As QueryTable ' QueryTables in sheet
  5. Dim URLDATArow As Integer
  6. Dim URLDATAitem As String
  7. Dim URLDATAdata As String
  8. Dim URLDATAitemline As Integer
  9. ' go to first cell
  10.     Range("O2").Activate
  11. ' loop through sheet
  12.     Do While ActiveCell.Value <> ""
  13. ' split URL on "/" in array
  14.         ARRURL = Split(ActiveCell.Value, "/")
  15.         With ActiveSheet.QueryTables
  16.             With .Add(Connection:="URL;" & ActiveCell.Value, _
  17.                         Destination:=Range("URLdata"))
  18.                 ' search .Name !! URL can or can NOT end on "/"
  19. If InStrRev(ActiveCell.Value, "/") = Len(ActiveCell.Value) Then
  20.                     .Name = ARRURL(UBound(ARRURL) - 1)
  21.                 Else
  22. .Name = ARRURL(UBound(ARRURL))
  23.                 End If
  24. .FieldNames = True
  25.         .RowNumbers = False
  26.         .FillAdjacentFormulas = False
  27.         .PreserveFormatting = True
  28.         .RefreshOnFileOpen = False
  29.         .BackgroundQuery = True
  30.         .RefreshStyle = xlInsertDeleteCells
  31.         .SavePassword = False
  32.         .SaveData = True
  33.         .AdjustColumnWidth = True
  34.         .RefreshPeriod = 0
  35.         .WebSelectionType = xlEntirePage
  36.         .WebFormatting = xlWebFormattingNone
  37.         .WebPreFormattedTextToColumns = True
  38.         .WebConsecutiveDelimitersAsOne = True
  39.         .WebSingleBlockTextImport = False
  40.         .WebDisableDateRecognition = False
  41.         .WebDisableRedirections = False
  42.         .Refresh BackgroundQuery:=False
  43. End With
  44.         End With
  45.         ' clear QueryTables
  46.         For Each QT In ActiveSheet.QueryTables
  47.             QT.Delete
  48.         Next
  49. ' set data in cells and go 1 cell down
  50.         With ActiveCell
  51.             ' move data
  52.             For URLDATArow = 1 To Range("URLdata").Rows.Count
  53.                 URLDATAdata = Range("URLdata").Cells(URLDATArow, 2).Value
  54.                 If URLDATAdata <> "" Then
  55.                     If Range("URLdata").Cells(URLDATArow, 1).Value <> "" Then
  56.                         URLDATAitem = Range("URLdata").Cells(URLDATArow, 1).Value
  57.                         URLDATAitemline = 1
  58. Else
  59.                         URLDATAitemline = URLDATAitemline + 1
  60.                     End If
  61.                     Select Case URLDATAitem
  62. Case "displayEmail =:"
  63.                         Select Case URLDATAitemline
  64.                         Case 1
  65.                             .Offset(0, 9).Value = URLDATAdata
  66. Case 2
  67.                             .Offset(0, 10).Value = URLDATAdata
  68.                         End Select
  69.                     Case "<h2>:"
  70.                         .Offset(0, 11).Value = URLDATAdata
  71. End Select
  72.                 End If
  73.             Next
  74.             ' go 1 cell down
  75.             .Offset(1, 0).Activate
  76.         End With
  77.         ActiveSheet.Range("URLdata").Clear
  78.     Loop
  79. End Sub


I believe it has to do with destination part of the code I dont think im doing this right
Sep 14 '12 #3
TheSmileyCoder
2,321 Expert Mod 2GB
I have looked a bit at your code, and not springs to mind as "obviously wrong", but I haven't used any code similar to yours really. I am guessing its a logic error, and I would suggest you try stepping through the code.

For more information on debugging in vba try looking at:
Debugging in VBA
Sep 15 '12 #4
zmbd
5,400 Expert Mod 4TB
When you say the data isn't posting correctly, what do you mean... please give us an example.
Are you getting any errors? If so, what number and description?

-z
Sep 15 '12 #5
Guido Geurs
767 Expert 512MB
The code seems to be OK but in the URL pages there is no "displayEmail =:" on which you want to capt the data from.

PS: be carefully with names: there is also a macro with the same name as the named-range: "URLdata"

I have attached a code in which you can see the data of the URLs for 5 seconds.
If you want a longer time, change the 5 to a higher value.
Attached Files
File Type: zip TEST RSS_test1.zip (33.0 KB, 86 views)
Sep 18 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Krish | last post: by
5 posts views Thread by Craig G | last post: by
1 post views Thread by Jim in Arizona | last post: by
2 posts views Thread by pob | last post: by
1 post views Thread by tshad | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.