Programming a macro to draw info from a list of links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Snowman - 23 Jan 2008 11:25 GMT

Sorry, I'm really, really ignorant when it comes to programming macros. I
currently have an Excel file with two worksheets. Worksheet 2 is named
"Links" and consists of a list of links (not in hypertext format... not sure
of this could be the problem or not). Sheet 1 is named Data, and is
currently blank.

What I want to do is be able to run this macro in sheet 1 and have it open
the top link in sheet 2, cut the second table from that webpage, and paste it
into the first available rows on sheet 1. I then want to be able to repeat
and have it do the same with the second link, and then the third, and so on,
until I have copied that chart from each of the 273 webpages linked on sheet
2.

Unfortunately, by attempts to do so are not working. I get run-time error
#1004, and a message that the address isn't valid. Here's the code:

Sub URL_Get_Data()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & _
Sheets("Links").Select, _
Destination:=Cells(Rows.Count, 1).End(xlUp)(2))

.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.Refresh BackgroundQuery:=True
.SaveData = True
End With
End Sub

Any help as to where I've gone wrong would be greatly appreciated.

Reply to this Message

Joel - 23 Jan 2008 13:14 GMT

Sub URL_Get_Data()
celladdress = Cells(Rows.Count, "A").End(xlUp)(2).address
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & _
"Destination:=Sheets(""Links"").Range(" & celladdress & ")")

.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.Refresh BackgroundQuery:=True
.SaveData = True
End With
End Sub

> Sorry, I'm really, really ignorant when it comes to programming macros. I
> currently have an Excel file with two worksheets. Worksheet 2 is named
[quoted text clipped - 32 lines]
>
> Any help as to where I've gone wrong would be greatly appreciated.

Reply to this Message

Snowman - 24 Jan 2008 01:28 GMT

Run-time error 450, Wrong number of arguments or invalid property assignment.

> Sub URL_Get_Data()
> celladdress = Cells(Rows.Count, "A").End(xlUp)(2).address
[quoted text clipped - 13 lines]
> End With
> End Sub

Reply to this Message

Joel - 24 Jan 2008 11:13 GMT

Can you post the code that worked before you made modifications. Queries are
hard to debug unless you havve the actual database or webpage.

Usually I get Queries to work by recording a macro and then modifiying the
recorded macro as necessary.

> Run-time error 450, Wrong number of arguments or invalid property assignment.
>
[quoted text clipped - 15 lines]
> > End With
> > End Sub

Reply to this Message

Snowman - 25 Jan 2008 00:17 GMT

I never had any that worked. I began with the sample from this article:

http://support.microsoft.com/kb/213730

> Can you post the code that worked before you made modifications. Queries are
> hard to debug unless you havve the actual database or webpage.
[quoted text clipped - 21 lines]
> > > End With
> > > End Sub

Reply to this Message