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.
5 2120
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.
ok Here is the code I am using now - Sub URL()
-
'§ Keyboard Shortcut: Ctrl+Shift+L
-
Dim ARRURL() As String '§ for splitting the URL
-
Dim QT As QueryTable '§ QueryTables in sheet
-
Dim URLDATArow As Integer
-
Dim URLDATAitem As String
-
Dim URLDATAdata As String
-
Dim URLDATAitemline As Integer
-
'§ go to first cell
-
Range("O2").Activate
-
'§ loop through sheet
-
Do While ActiveCell.Value <> ""
-
'§ split URL on "/" in array
-
ARRURL = Split(ActiveCell.Value, "/")
-
With ActiveSheet.QueryTables
-
With .Add(Connection:="URL;" & ActiveCell.Value, _
-
Destination:=Range("URLdata"))
-
'§ search .Name !! URL can or can NOT end on "/"
-
If InStrRev(ActiveCell.Value, "/") = Len(ActiveCell.Value) Then
-
.Name = ARRURL(UBound(ARRURL) - 1)
-
Else
-
.Name = ARRURL(UBound(ARRURL))
-
End If
-
.FieldNames = True
-
.RowNumbers = False
-
.FillAdjacentFormulas = False
-
.PreserveFormatting = True
-
.RefreshOnFileOpen = False
-
.BackgroundQuery = True
-
.RefreshStyle = xlInsertDeleteCells
-
.SavePassword = False
-
.SaveData = True
-
.AdjustColumnWidth = True
-
.RefreshPeriod = 0
-
.WebSelectionType = xlEntirePage
-
.WebFormatting = xlWebFormattingNone
-
.WebPreFormattedTextToColumns = True
-
.WebConsecutiveDelimitersAsOne = True
-
.WebSingleBlockTextImport = False
-
.WebDisableDateRecognition = False
-
.WebDisableRedirections = False
-
.Refresh BackgroundQuery:=False
-
End With
-
End With
-
'§ clear QueryTables
-
For Each QT In ActiveSheet.QueryTables
-
QT.Delete
-
Next
-
'§ set data in cells and go 1 cell down
-
With ActiveCell
-
'§ move data
-
For URLDATArow = 1 To Range("URLdata").Rows.Count
-
URLDATAdata = Range("URLdata").Cells(URLDATArow, 2).Value
-
If URLDATAdata <> "" Then
-
If Range("URLdata").Cells(URLDATArow, 1).Value <> "" Then
-
URLDATAitem = Range("URLdata").Cells(URLDATArow, 1).Value
-
URLDATAitemline = 1
-
Else
-
URLDATAitemline = URLDATAitemline + 1
-
End If
-
Select Case URLDATAitem
-
Case "displayEmail =:"
-
Select Case URLDATAitemline
-
Case 1
-
.Offset(0, 9).Value = URLDATAdata
-
Case 2
-
.Offset(0, 10).Value = URLDATAdata
-
End Select
-
Case "<h2>:"
-
.Offset(0, 11).Value = URLDATAdata
-
End Select
-
End If
-
Next
-
'§ go 1 cell down
-
.Offset(1, 0).Activate
-
End With
-
ActiveSheet.Range("URLdata").Clear
-
Loop
-
End Sub
I believe it has to do with destination part of the code I dont think im doing this right
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 zmbd 5,501
Recognized Expert Moderator Expert
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: sparks |
last post by:
I was going to loop thru the tables and change some properties.
10 <<<<<< prp.value --prp.name = Type
False <<<<prp.value --prp.name = allow zero length
10 <<<<<< prp.value --prp.name = Type...
|
by: u473 |
last post by:
I am still a beginner with VBA and I need the VBA Code for the
following problem.
I have a list of activities with their Scheduled Start date, Duration,
and Working hours in Table1.
I need to...
|
by: Krish |
last post by:
I have Type System.Object and the value is {System.Array}
I see ....... in the watch window.
How do I traverse such a data structure.
thanks for any help.
|
by: Craig G |
last post by:
how do i go about this thru serverside code (VB.NET)? any links to any
articles anywhere?
basically i just want something simple that will loop thru all txt & cbo
server side controls, and then...
|
by: Jim in Arizona |
last post by:
Using VB ..
Is there a way to loop through all drop down lists on a web form
checking for a specific value (ddlDropDown.SelectedValue) and then,
based on that value, cause a specific label to go...
| |
by: pob |
last post by:
Whats the difference between using a control or a listbox when looping
thru a listbox. In example 1 it dims a listbox and an example 2 it
dims a control. Please explain. Thanks in advance
...
|
by: pds79 |
last post by:
Hi everyone,
I'm a newbie to the forum.
I have an issue and was hoping to get some assistance/ideas:
Im trying to read a XML file into two record sets.
I can acheive looping through the...
|
by: tshad |
last post by:
I was trying to do this in Javascript but found I can't do spans (which is
what Labels turn into).
I can do a loop thru a control list something like:
Public Sub LoopingControls(ByVal oControl...
|
by: mahowe |
last post by:
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission...
|
by: sparks |
last post by:
well I am trying to get around looking at all the buttons with a case
statement so I was trying this.
Public Sub looking(ctlname)
Dim ctl As OptionGroup
Dim btn As ToggleButton
ctl =...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |