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.

 

[halobender]

The query returned is in one column and is 10 rows

example query

1:You Name It - City
2:
3:####-A Major Hwy
4:City, State #####
5:
6:view map
71669.3 miles away)
8:
9hone: ###-###-####
10:Back

Ultimately and ideally I would just need rows 3, 4, and 9, and if I could get those to copy to the columns E, F, and G then I would be a major happy camper!!

 

[SkipVought]

Sub Main()
    Dim r As Range
    
    With [Expanded].Parent
        For Each r In [Expanded]
            queryedit r.Value
    'rows 3, 4, and 9, and if I could get those to copy to the columns E, F, and G
            .Cells(r.Row, "E").Value = Sheets("Test").Cells(3, "A")
            .Cells(r.Row, "F").Value = Sheets("Test").Cells(4, "A")
            .Cells(r.Row, "G").Value = Sheets("Test").Cells(9, "A")
        Next
    End With
End Sub

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

 

[halobender]

You my friend are brilliant!! works like a charm!!

 

[SkipVought]

We went thru a process of discovery, that can be reproduced in its essence in other situations, although the details of modification might need some expertise.

Glad it works for you.

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

 

[halobender]

I will try to put this to use on another project as well.. I understand the vb once its written and can edit it but as far as doing it all on my own thats another story