Assistance needed to loop code when pulling info from a web page and pasting to excel

boswelljw

New member
Joined
Sep 20, 2013
Messages
2
Reaction score
0
Points
0
Hello
I hope I'm pasting enough code here. This isn't the entire code I have written, but hopefully enough to understand what I'm trying to do.
What I want to do is place the code into a loop, so that it runs until the values in column H are empty.
Basically, I'm copying a value that I place in column H, the code goes out to a web page and pastes that value and then searches for detailed information on that value. The information found is then copied and placed back into an excel worksheet and then I perform a search within excel worksheet and copy specific information to specific cell into a different worksheet.
I don't want to specify certain cells to copy and paste data... I want the code to grab info from H1.. etc.. then paste to I1, then do the same for H2 .. etc... paste to I2, that is if H2 is populated.
Thanks in advance for your assistance.. if more info or code is needed, I can supply.

Code:
Sub populate_decom_doc()  
Dim IE As Object 
Set IE = CreateObject("internetexplorer.application") 
IE.Visible = True apiShowWindow 
IE.hwnd, SW_MAXIMIZE  
'Copy servername from Excel Workbook Workbooks("Book1.xlsm").Sheets("Sheet1").Range("H1").Copy  
'Navigate to some internal work webpage \IE.Navigate "work URL goes here"  
'Wait for webpage to load 
Do DoEvents Loop Until IE.readyState = 3 Do DoEvents Loop Until IE.readyState = 4  
'Paste servername into work webpage SendKeys ("^v")  
'Tab to General Tab on work webpage Application.SendKeys "{F3}" SendKeys "General"  
'Select all server detail data on General tab on work webpage Application.Wait (Now + TimeValue("0:00:03")) 
IE.ExecWB 17, 0 '// SelectAll 
IE.ExecWB 12, 2 '// Copy selection  
'Create Worksheet called Sheet2 Dim ws As Worksheet Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)) ws.Name = "Sheet2"  
'Paste General tab from work webpage into Workbook 
Workbooks("Book1.xlsm").Sheets("Sheet2").Select ActiveSheet.Paste  
'Find location info and paste into proper cell 
Workbooks("Book1.xlsm").Sheets("Sheet2").Range("B2").Select 
Workbooks("Book1.xlsm").Sheets("Sheet2").Cells.Find(What:=("Tulsa "), After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Selection.Copy Workbooks("Book1.xlsm").Sheets("Sheet1").Select 
Workbooks("Book1.xlsm").Sheets("Sheet1").Range("I1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
 
Last edited:
Back
Top