I'm new to VBA but have some experience on Python, and I'm working on a project that needs to scrape a webpage in order to get some info. When this info is fetched, it must fill a worksheet cell.
I've found some help here and here (mainly the second link) but guess that I'm missing something, because the Python shell window blinks quickly on the screen and then it does nothing. I've used a MsgBox
to "print" the return value and got nothing as well, like if the script wasn't running.
Disclaimer: I'm not using Shell.Run
because I want to receive my Python script return value on VBA, so I can put cell by cell accordingly.
Here's the code:
Private Sub CommandButton1_Click()
Dim codigo As String
codigo = InputBox("Leia o QRCode.", "...")
'Data
Dim dateString As String
code1 = Now
'Hora
Dim hourString As String
code2 = Hour(Now)
'Modelo
Dim theShell As Object
Dim theExec As Object
Dim runPath As String
Dim modelName As String
Dim model As String
Dim theOutput As Object
Set theShell = CreateObject("WScript.Shell")
runPath = "python " & ActiveWorkbook.Path & "\get_modelo.py " & "'" & codigo & "'"
Set theExec = theShell.Exec(runPath)
Set theOutput = theExec.StdOut
modelName = theOutput.ReadLine
While Not theOutput.AtEndOfStream
modelName = theOutput.ReadLine
MsgBox modelName
If modelName <> "" Then model = model & modelName
Wend
Set theShell = Nothing
Set theExec = Nothing
Set theOutput = Nothing
'Tem que ver alguma forma de conseguir
'o link ao ler o QRCode, ou teremos
'que gerar o link no nosso script também
'Lote
Worksheets("database").Range("A2").Value = dateString
Worksheets("database").Range("B2").Value = hourString
Worksheets("database").Range("C2").Value = model
End Sub
My Python code:
from selenium import webdriver
from webdriver_manager.chrome import ChromeDriverManager
import webbrowser
import sys
def get_modelo(address_string):
driver = webdriver.Chrome(ChromeDriverManager().install())
driver.set_window_position(-10000, 0)
driver.get(address_string)
model = driver.find_element_by_xpath(r'//*[@id="top90"]/h1')
return model.text
print(get_modelo(sys.argv[1]))