excel web query

[halobender]

Hey all I have an issue and could use some extra brains.

Here is what I have..

I have a web query created in excel 2010 which has imported three columns and 500 rows

A = web hyperlink text
B = place
C = place

I need to find a way to use vba or the web query itself to visit the web hyperlink copy all text and paste back to column D

I am at a loss and have no code to even offer as everything I've tried has resulted in ultimate failure.

 

[Gruuuu]

I'm confused. Your web query returns results into three columns, and you just need to copy the results to the next column?

 

[halobender]

Not exactly sorry for not being descriptive enough.

The original query imports a list of information into three columns

Column A = a hyperlink to a small page of text on the web
Column B = text
Column C = text

The information I need to obtain is located in column A but only after clicking the hyperlink. I could click it and copy and paste the information back to excel manually but i have to be able to imagine an easier way. I am hoping to find a way to get the information from the web and import that back into column D with VBA or an excel formula I don't know about.

Is there a way to query the web link in column A and paste that information back into column D?

 

[halobender]

Any suggestions?

 

[SkipVought]

What happens when you, on a new sheet, do a web query using, for instance, the URL for the first hyperlink?

What data would you return? You have to look at the CODE that you produce when you do this web query and determine, how you might assign the correct properties as needed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

Skip thanks for the reply. I have seen some other posts you have made kind of about the same topic.

If i run a query on the first link I get the information I am after which is two tables containing 11 rows of information. That information is what I am ultimately after but manually querying 900 links is not my idea of fun!!

halo

 

[SkipVought]

From what you stated in your previous post, it implied 1) more than one column and 2) more than one row.

How does what you retrieved, fit into "and paste back to column D."?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

Okay I think we both got a little confused. The original web query gives me info in three columns and 900+ rows. In column a is another web link containing more information I need to obtain.

When you asked me to use sheet two to create a query based on the link in column a that is what the previous post was in response to. That query is what I want to have put back into column d on sheet 1.

Ideally it should be able to be completed automatically with the help of vba but I am unsure how to make excel communicate with firefox or ie and visit the link from column a, copy all text, and paste back to column d and run a loop until column a is blank.

 

[SkipVought]

My intent was to get you to 1) MANUALLY MAKE THE QUERY ON A SEPARATE SHEET, that you intend to happen for each url in column A, in order for you to 2) see and 2) report what would be returned in such a query, in order to 4) determine what you might want to do with the returned data in order to 5) "paste back to column D."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

Yes I manually added a new query to sheet two like you said. It returns a few lines of text in ten rows. I don't need all of the information but I can trim the information at a later step if needed. I do however need that information and 899 more querys. As far as query manually I am using the excel query from web. Is there in fact another way to preform a query without going about it the way I am?

 

[SkipVought]

Turn on your macro recorder and RECORD editing that new query on this other sheet.

Post back with your recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

Here you go not that it does a whole lot..

    Sub webcopy()
'
' webcopy Macro
'

'
    Sheets("Sheet1").Select
    ActiveCell.Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Sheets("Sheet2").Select
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "Company" & Chr(10) & "" & Chr(10) & "555 Big Street Name" & Chr(10) & "City, State #####" & Chr(10) & "" & Chr(10) & "view map" & Chr(10) & "(1580.9 miles away)" & Chr(10) & "" & Chr(10) & "Phone: ###-###-####" & Chr(10) & "Back" & Chr(10) & ""
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

 

[SkipVought]

That is NOT what I asked for.

I asked you to do a web query on a new sheet using the url of the first hyperlink. then turn on your macro recorder and record EDITING that query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

Okay if I understand you correctly.

1 - Copy the link from A2 and use that to build a new web query on sheet 2
2 - Record a macro of me editing that query
3 - paste it back here

I hope this is what you meant.

Sub queryedit()
'
' queryedit Macro
'

'
    With Selection.QueryTable
        .Connection = _
        "URL;[URL unfurl="true"]http://www.longassweblink.com/otherstuff/thensome.html"[/URL]
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = True
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

OK. Now we're on our way!

On your FIRST query, SELECT ALL the DATA & HEADINGS then Formulas > Defined Names > Create from selection -- Create names from values in the TOP row.

Copy this code and PASTE in the same module, OVER your original recorded code.

Sub Main()
    Dim r As Range
    
    For Each r In [[highlight]????????[/highlight]]
        queryedit r.Value
    Next
End Sub


Sub queryedit(URL As String)
'
' "URL;[URL unfurl="true"]http://www.longassweblink.com/otherstuff/thensome.html"[/URL]
'
    With ActiveSheet.QueryTables(1)
        .Connection = "URL;" & URL
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = True
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Go to the Name Box and SELECT & COPY the NAME that corresponds to column A, and Replace [highlight]????????[/highlight] in the code, with that Name.

ALSO, check the values in column A, as having the form...
[tt]
http://something
[/tt]
http:// is essential. It that is NOT in each value in column a, then it must be inserted in the code as...

        .Connection = "URL;[URL unfurl="true"]http://"[/URL] & URL

Now you are ready to run a TEST.

ACTIVATE the sheet that has the SECOND web query.

STEP into the MAIN() procedure (F8) and then STEP OVER (SHIFT + F8) thru several URL values and OBSERVE that the corresponding query is executed and returns the data from that page. You are not doing anything with the returned data yet -- just checking that the data from each web page is returned.

The next step is to decide 1) WHAT data you want for each web page and 2) HOW to get that data from that sheet back to the original sheet.

In the code, I would change [highlight]ActiveSheet[/highlight] to a specific sheet name, using [highlight]Sheets("SomeSheetName")[/highlight]




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

Okay Skip I am trying to hang with you on all of this.. I'm hung up on a few issues..

1 - I followed your step directions and am a little confused as to what I am watching for.. It does say to query [Name] where "Name" is column A name.

2 - It errors out on .Refresh BackgroundQuery:=False

okay think I figured something out.. since the value in column a is wrote as "a name here" and not the expanded length I was able to use another column to expand the link and reran the step test and it worked. The information pasted to sheet 2 changed every step.

As far as the next step I would like to copy and paste each query back to the main sheet into say column F.

Here is the updated code that is working

Sub Main()
    Dim r As Range
    
    For Each r In [Expanded]
'Expanded is the name of the column I had to create with the expanded link
        queryedit r.Value
    Next
End Sub


Sub queryedit(URL As String)
'
' "URL;[URL unfurl="true"]http://www.longassweblink.com/otherstuff/thensome.html"[/URL]
'
    With Sheets("Test").QueryTables(1)
'Test is the name of sheet 2
        .Connection = "URL;" & URL
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = True
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

OK. When you go to the Name Box and Select Expaneded, does it HIGHLIGHT the DATA in column I?

If I used the url for this forum...
[tt]

http://tek-tips.com/threadminder.cfm?pid=707

[/tt]
Is your data of this FORM in column I?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[halobender]

No it is in column D but I could move it if necessary

 

[halobender]

I guess I should clarify

Column 1 or A is named "Name" the values in that column are listed as a bunch of text or names instead of the link itself

I have created a new column 4 or D and labeled it "Expanded" which is the actual link that column a refers to so its values are "

http://www.etc.com"

and so forth

 

[SkipVought]

OK, you stated, "the updated code that is working."

Sorry, I missed that.

so you stated, regarding the next step...

As far as the next step I would like to copy and paste each query back to the main sheet into say column F.

So how do you propose, conceptually, to get multiple rows from each query (and I assume from this kind of query, that each query returns MULTIPLE ROWS and MULTIPLE COLUMNS) into ONE ROW in column F?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]