Excel Web scraping to search items in google and Print Screen to Word

Question

I am trying to search some value from excel and paste the capscreen to a word file. It work fine when there is only one item to search.

But I wonder if it is possible to search through a list and paste them all in to a word file.

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Private Const VK_SNAPSHOT As Byte = 44
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Private Const SW_SHOWMAXIMIZED = 3
Private Const VK_LCONTROL As Long = &HA2
Private Const VK_V = &H56
Private Const KEYEVENTF_KEYUP = &H2

Sub Sample()
Dim IE As Object
Dim hwnd As Long 
IECaption As String
Dim workRng As Range
Dim searchword As String

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True    
IE.Navigate "www.Google.com"    
Sleep 5000    
IECaption = "Google - Internet Explorer"    
hwnd = FindWindow(vbNullString, IECaption)    
ShowWindow hwnd, SW_SHOWMAXIMIZED
Sleep 1000
DoEvents

I tried to make a loop for the code below but it seems that it could only search for A1 A2, or totally not working. Even if it managed to search A2 it still cannot paste the screen shot to word.

Set workRng = Range("A1:A10")
For Each searchword in workRng
IE.Document.All("q") = searchword.Value
IE.Document.All("btnK").Click
Sleep 1000
Call keybd_event(VK_SNAPSHOT, 0, 0, 0)
Set wordobj = CreateObject("Word.Application")
Set objDoc = wordobj.Documents.Add
    wordobj.Visible = True
Set objSelection = wordobj.Selection
    objSelection.Paste
Next searchword
End Sub

there is no loop in either of the code exmples that you posted. do web search for "VBA loops" — jsotola, Aug 13 '17 at 00:20
Sorry i didnt post it as it didnt work in my case, now i have included the loop in it again — Kinghin245, Aug 13 '17 at 02:45
when i run this loop it would popup an error said the range is invalid when it goes through the 2nd or 3rd value, im not sure its my comp problem or the code problem — Kinghin245, Aug 13 '17 at 15:24
you would get help a lot faster if you describe the error in detail. you still have not said which line produces the error. — jsotola, Aug 13 '17 at 18:38
[This answer](https://stackoverflow.com/a/35833174/2165759) may help. — omegastripes, Aug 14 '17 at 08:27

score 0 · Answer 1 · answered Aug 31 '17 at 20:15

Not 100% sure if this is what you are asking for. I have fixed couple bugs for you and here you go:

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Private Const VK_SNAPSHOT As Byte = 44
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Private Const SW_SHOWMAXIMIZED = 3
Private Const VK_LCONTROL As Long = &HA2
Private Const VK_V = &H56
Private Const KEYEVENTF_KEYUP = &H2

Sub Sample()

    Dim IE As Object
    Dim hwnd As Long
    Dim IECaption As String
    Dim workRng As Range
    Dim searchword

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate "www.Google.com"
    IEWait (1)
    Do Until IE.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    IECaption = "Google - Internet Explorer"
    hwnd = FindWindow(vbNullString, IECaption)
    ShowWindow hwnd, SW_SHOWMAXIMIZED

    IEWait (1)
    Do Until IE.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    DoEvents

    Set workRng = Range("A1:A10")
    For Each searchword In workRng
        IE.document.all("q") = searchword.Value
        IE.document.all("btnK").Click

        IEWait (1)
        Do Until IE.readyState = READYSTATE_COMPLETE: DoEvents: Loop

        Call keybd_event(VK_SNAPSHOT, 0, 0, 0)
        Set wordobj = CreateObject("Word.Application")
        Set objDoc = wordobj.Documents.Add
        wordobj.Visible = True
        Set objSelection = wordobj.Selection
        objSelection.Paste
    Next searchword

    IE.Quit
    Set workRng = Nothing
    Set IE = Nothing

End Sub

Function IEWait(t As Long)

    Do While IE.Busy
        Application.Wait DateAdd("s", t, Now)
    Loop

End Function

Some explanations here:

You are missing a Dim syntax for IECaption.
searchword can only be an Object or Variant type.
Use Excel to control IE sometimes has some weird reactions for sleep, and therefore, I throw in an IEWait function that should well control the webpage to the required amount of time.
Quit and close IE. You can remove these lines if this doesn't need to be done.

Hope this can help you.

Excel Web scraping to search items in google and Print Screen to Word

1 Answers1