using VBA to extract web information to a spreadsheet

[julius1]

I am using VB in a macro to follow a click on a hyperlink, and then copy a single field and paste it back to the spreadsheet. I have over 900 rows and have to do this weekly. I started a Macro with the VB in it. I just can't get it to repeat for all the lines in the range. Column A is the hyperlink, then column D is where I am pasting it back into. Here's my VB can someone help me with the repeat feature?

Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.WindowState = xlNormal
Range("D1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -3).Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.SmallScroll Down:=15
Application.WindowState = xlNormal
ActiveWindow.SmallScroll Down:=-18
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveSheet.Paste

Thanks everyone.

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[Chance1234]

Sub bob()
Dim intr As Integer

intr = 2

Do Until intr = 900
Debug.Print ActiveSheet.Cells(intr, 2).Value
intr = intr + 1
Loop

End Sub




Chance,

Filmmaker, gentlemen and explorer

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Loomah]

OR

Dim iRow As Integer
For iRow = 1 To 900
Cells(iRow, 1).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
'etc etc.
Next

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[julius1]

Loomah I am eager to try that, but not to sound really ignorant, but where do I add the additional code? My experience is more with SQL queries then VB maco script.
Would you mind helping me out?

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[Loomah]

Julius
I'd be glad to help but I'm not around much longer today (UK based and wanting a beer!)

Your code goes in the middle of the loop - either the one I posted or the one Chance posted. Both are valid!

Unfortunately what I have posted below is NOT tested. Also, I don't really have any experience of 'getting' web data in this way. I've tried to condense your code but I have a few problems:-

Firstly, what are you actually copying? There is no copy code here and so nothing to actually paste!

Secondly, are the hyperlinks targeting web docs or just other spreadsheets? If the latter is true there may be a better way of doing this - which I can't think of at the moment!!

Sub FollowHyperlinks()
Dim iRow As Integer
For iRow = 1 To 900
    With Cells(iRow, 1)
        .Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
        Application.WindowState = xlNormal  'NOT NECESSARY??????
        'What is copied?
        'Where is the copy code?
        .Offset(0, 3).Select
        ActiveSheet.Paste   'paste what?
    End With
Next
End Sub

If you run into problems post back and someone else may take up the problem.
Good Luck!!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[VBAjedi]

Loomah,

Enjoy the pub. . . I'm West Coast, USA, so I'll keep an eye on this one for a few hours. . .

VBAjedi

 

[julius1]

Heres the latest one I made recording it. I see there is nothing there per the script, but I just tested it by deleting the entry in column d. I ran the script and it worked.

Range("A378").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("D378").Select
ActiveSheet.Paste

I just need it to loop and run thru all hyperlinks in COlumn A and paste the order numbers into column D. I also have to find a way to get it to close the browser window it opened. I have over 700 rows and windows only lets 64 be open at a time. So I am trying to get it to open the browser by hyperlink on that line range A378 then copy the order number and then paste it to line 378 Column D. That works actually, but I am then trying to 1) get it to loop theu all of the entries in column a and paste them into column d. They are individual links too, and 2) either reuse the window or close and open a new after each line is completed.
Hope you can help..Im lost on it.

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[VBAjedi]

Julius,

Your last post doesn't make sense to me. . . did you try Loomah's code? And we need to know your answers to the questions Loomah asked.

VBAjedi

 

[julius1]

I can understand that, it seems odd to me as well, but it does work. The web site is for order status. The hyperlink in column A, once selected or clicked brings it up. I then copy the entry nest to order number and then I paste it into the cell D for that line item.
Not sure about window state. I actually need that window to close. It's the web broswer that it refers to I believe. It's odd, but the code does what it needs to do. I am really trying to get it to repeat that process for all lines from 4 to 900 and close the browser afer each one.

Like for the one above, is there an entry that can be made to close the broswer, then repeat for te remaining lines?

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[julius1]

I was thinking about this over the weekend, and I understand that it loops fine, but I am trying to get it to reuse the existing web window, or close it when it rolls down the next hyperlink. I tried it with another link.
test1 Macro
' Macro recorded 9/7/2004 by Julius Gazdag
'

'
Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("C1").Select
ActiveSheet.Paste
Range("A2").Select
End Sub

I used

http://www.yahoo.com

as the hyperlink. On the recording it records the copy of a specific line of text. If it changes the macro no longer works. For example I recorded it copying the text "10 free" from the top of the page, then pasted it to column d. I cleared the entry in column d and ran the macro. It worked fine. My question is how can I get it to either close the browser or reuse the window for the rest of the sheet as it loops. My only other option is to write a segmented macro for lines 1-63, then 64-?? up to900. Even though it does not state anything specific to copy it does work. I think it's because it looks for something constant, not a label or a field in particular. Hope this helps to clarify some.

thanks

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[julius1]

Ok, using Loomah's code it works fine, but I have only one problem, how can I tell it to reuse the window or close it after each line?
Here's the code I am using and it copying a line of text from a web page that will never change as far as the configuration:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/3/2004 by Julius Gazdag
'

'Dim iRow As Integer
For iRow = 4 To 900
Cells(iRow, 1).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A4").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("D4").Select
ActiveSheet.Paste
Next

End Sub

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[julius1]

The web link is a secured site but here is the hyperlink that is in column A.
original hyperlink is:

https://centurytelorderprocessing.c...ers2&uuid=0007FC63-3FCF-111E-85888873D18E006C

It brings up a window, granted it requires you to sign in, then I copy a line of text from the center of the page and paste it to column D which is the order number. It is a constant field. It is a web page. Once it is done, I would move down the list for all 900 of them. I am trying to get it to repeat the process for all 900 and either close the web browser or re use it.
The spreadsheet layout is :
15-Aug-04 CSR complete 1157884
The date is the actual hyperlink, the 1157884 is the actual order number that is to be copied from the web page.

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[CautionMP]

Better late than never? I was answering another post and did a quick search and found this one.

Here is a routine that will create and reuse a browser window. It is a stand alone routine so you can see all the pieces

Public Sub RecycleBrowserWindow()
Dim objIE As Object
Dim intURL As Integer
Dim strURL(2) As String

strURL(0) = "[URL unfurl="true"]http://www.yahoo.com"[/URL]
strURL(1) = "[URL unfurl="true"]http://finance.yahoo.com"[/URL]
strURL(2) = "[URL unfurl="true"]http://www.google.com"[/URL]

For intURL = 0 To 2
  Set objIE = CreateObject("InternetExplorer.Application")
  objIE.Navigate strURL(intURL)
  objIE.Visible = True
  Stop
  objIE.Quit
Next intURL
Set objIE = Nothing
End Sub

To modify to your purpose change the
[tt]objIE.Navigate strURL(intURL)[/tt]
to read
[tt]objIE.Navigate Cells(iRow, 1).Hyperlinks(1).Address[/tt]

Hope this helps,
CMP

Instant programmer, just add coffee.