Is there a way to store all Excel VBA Google searches in a separate sheet in the Excel file?-CodePudding

I have the code below and it works perfectly the way I want. I type a certain phrase in Excel pop-up window using VBA to search using Google Search. However, I would love to be able to store all the Excel VBA Google searches, either in the same Excel file (same/another sheet) or in another file. Does anybody know if this is possible to do? I don't know if it's the code that should be modified or the Excel settings in some way.

Private Const LicenseRegistration As String = " brott och straff"
Private Sub CommandButtonSearch_Click()
   Dim query As String
   Dim search_string As String
   Dim googleChromePath As String
   
   query = InputBox("Enter your keyword", "Google Search")
   search_string = Replace(query, " ", " ") & LicenseRegistration
   
   googleChromePath = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
   
   Shell (googleChromePath & " -url http://google.com/search?q=" & search_string)

With ThisWorkbook.Worksheets("AnotherSheet")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Value = search_string
End With

End Sub

A window pops up titled "Microsoft Visual Basic" and it says: Run-time error "9": Subscript out of range

I copied and pasted the code exactly as you wrote it, even tried to modify it a bit, but to no avail. I really hope you can see the picture with this link: https://drive.google.com/file/d/1me7xBn8jGvtmpRADp5QFUFmR9k2oGzBs/view?usp=sharing

CodePudding user response：

If you're just looking to keep a running list of search_string, then you can use:

With ThisWorkbook.Worksheets("AnotherSheet") ' change sheet name as needed
   .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Value = search_string
End With

before (or even after) the Shell call.