0

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]))
barny
  • 5,280
  • 4
  • 16
  • 21
Fornazari
  • 27
  • 4
  • Why not just use python to write to an Excel workbook/csv? Is this part of a larger VBA script? There is also selenium basic for VBA chrome automation. Finally, have you verified that info is not available by xhr? – QHarr May 01 '21 at 20:21

0 Answers0