Results 1 to 1 of 1

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

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

    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.

    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").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 by boswelljw; 2015-02-02 at 01:56 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts