I am trying to run a specific code on VBA and seeing the following error runtime error run time error-2147319765(8002802b)

Question

I am trying to run a code on excel VBA and trying to select a sheet during the macro run.

Sub Selstart()
Dim driver As ChromeDriver
Set driver = New ChromeDriver
driver.Get ("abc.com")
Dim a, b As String
a = "getlam"
b = "strak"
driver.FindElementByXPath(".//*[@id='loginForm']/div[1]/div[1]/input").SendKeys a
driver.FindElementByXPath(".//*[@id='loginForm']/div[1]/div[2]/input").SendKeys b
driver.FindElementByXPath(".//*[@id='Submit_button']").Click
Dim url0 As String
url0 = Sheets("Hyperlinks").Range("F5").Value
driver.Get url0
'open chrome and select page'

Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{a}"
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{c}"
Application.Wait (Now() + TimeValue("00:00:01"))
Sheets("dump").Select
Sheets("dump").UsedRange.ClearContents
'clear range contents'

Range("A1").Select
ActiveSheet.Paste
'paste the content'

driver.Get url0
Application.Wait (Now() + TimeValue("00:00:02"))
driver.FindElementByXPath(".//a[normalize-space()='Next']").Click
'Click on Next button'
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{a}"
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{c}"
Application.Wait (Now() + TimeValue("00:00:01"))
Sheets("dump").Select
Range("A130").Select
ActiveSheet.Paste

driver.Get url0
Application.Wait (Now() + TimeValue("00:00:02"))
driver.FindElementByXPath(".//a[normalize-space()='Next']").Click
Application.Wait (Now() + TimeValue("00:00:02"))
driver.FindElementByXPath(".//a[normalize-space()='Next']").Click
'Click on Next button*2'

Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{a}"
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{c}"
Application.Wait (Now() + TimeValue("00:00:01"))
Sheets("dump").Select
Range("A260").Select
ActiveSheet.Paste`

The code runs perfectly once in a while but it throws up an error when running "Sheets("dump").Select" . attaching error screenshot

Error screenshot

Answer 1

It's best to avoid using .Select/.Activate. Try the code below:

Sub Selstart()
Dim driver As ChromeDriver
Set driver = New ChromeDriver

Dim dumpWS As Worksheet
Dim linkWS As Worksheet

Set dumpWS = Worksheets("dump")
Set linkWS = Worksheets("Hyperlinks")


driver.Get ("abc.com")
Dim a, b As String
a = "getlam"
b = "strak"
driver.FindElementByXPath(".//*[@id='loginForm']/div[1]/div[1]/input").SendKeys a
driver.FindElementByXPath(".//*[@id='loginForm']/div[1]/div[2]/input").SendKeys b
driver.FindElementByXPath(".//*[@id='Submit_button']").Click
Dim url0 As String
url0 = linkWS.Range("F5").Value
driver.Get url0
'open chrome and select page'

Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{a}"
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{c}"
Application.Wait (Now() + TimeValue("00:00:01"))
dumpWS.UsedRange.ClearContents
'clear range contents'

dumpWS.Range("A1").Paste
'paste the content'

driver.Get url0
Application.Wait (Now() + TimeValue("00:00:02"))
driver.FindElementByXPath(".//a[normalize-space()='Next']").Click
'Click on Next button'
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{a}"
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{c}"
Application.Wait (Now() + TimeValue("00:00:01"))
dumpWS.Range("A130").Paste

driver.Get url0
Application.Wait (Now() + TimeValue("00:00:02"))
driver.FindElementByXPath(".//a[normalize-space()='Next']").Click
Application.Wait (Now() + TimeValue("00:00:02"))
driver.FindElementByXPath(".//a[normalize-space()='Next']").Click
'Click on Next button*2'

Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{a}"
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "^{c}"
Application.Wait (Now() + TimeValue("00:00:01"))
dumpWS.Range("A260").Paste

End Sub

Although, I don't think the .Select line is throwing the error. When you hit "Debug", are you sure that's the line that's highlighted? I'm thinking Element not Found is an issue with one of the XPaths...