[Solved] Automation from Calc to IE

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
User avatar
caravas
Posts: 37
Joined: Sun Jul 15, 2012 3:53 am

[Solved] Automation from Calc to IE

Post by caravas »

Hello folks,

I need to adapt VBA macro, from excel spreadsheet, to StarBasic.
The following code allows you to open a web page, using a formatted URL with information taken from a spreadsheet:

Code: Select all

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub GRU()
 Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim contador As Integer
    Dim j As Integer
    Dim compara
    
 
    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    IE.Visible = True
 
    ' Send the form data To URL As POST binary request
    
    contador = Application.CountA(Range("A:A")) - 1
    j = 0 + Application.CountA(Range("S:S")) - 1
    
         
     Do While j < contador
       CODIGOUG = Range("b" & (j + 2)).Value
       CODIGOGESTAO = Range("c" & (j + 2)).Value
       CODRECOLHIMENTO = Range("d" & (j + 2)).Value
       NUMEROPROCESSO = Range("f" & (j + 2)).Value
       COMP = Left(Range("g" & (j + 2)).Value, 2) & "%2F" & Right(Range("g" & (j + 2)).Value, 4)
       VENCIMENTO = Left(Range("h" & (j + 2)).Value, 2) & "%2F" & Right(Left(Range("h" & (j + 2)).Value, 5), 2) & "%2F" & Right(Range("h" & (j + 2)).Value, 4)
       CPFCNPJ = Range("j" & (j + 2)).Value
       NOMECONTRIBUINTE = Range("k" & (j + 2)).Value
       VALORPRINCIPAL = Range("l" & (j + 2)).Value
       Total = Range("r" & (j + 2)).Value
       URL = "https://Some_Page/gerarHTML.asp?codigo_favorecido=200118&gestao=00001&codigo_correlacao=4027&nome_favorecido=+Some_guy&codigo_recolhimento=28848-9&nome_recolhimento=OUTRAS+MULTAS&referencia=" & NUMEROPROCESSO & "&competencia=" & COMP & "&vencimento=" & VENCIMENTO & "&cnpj_cpf=" & CPFCNPJ & "&nome_contribuinte=" & NOMECONTRIBUINTE & "&valorPrincipal=" & VALORPRINCIPAL & "&descontos=&deducoes=&multa=&juros=&acrescimos=&valorTotal=" & Total & "&boleto=3&impressao=SA&pagamento=1&campo=NRCR&ind=0"

      IE.Navigate URL
       Sleep 3000
       Do While IE.Busy
           Application.Wait DateAdd("s", 1, Now)
           IE.Navigate URL
           Sleep 1000
       Loop
       
       ''''''''''''''''''''''''''''''''''''''''''''Printing without warning'''''''''''''''''''''''''''''''''''
       IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Range("S" & (j + 2)).Value = "OK"
    j = j + 1
    Loop
End Sub

The code works well, requiring only adjustment to the print command that generates error: "IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER"
Is there any way around this error? Any suggestion is welcome.
Thank you in advance.
Last edited by caravas on Tue Apr 12, 2016 11:52 pm, edited 1 time in total.
LO 4.3.4 on Windows 8.1
User avatar
caravas
Posts: 37
Joined: Sun Jul 15, 2012 3:53 am

Re: Automation from Calc to IE

Post by caravas »

Adding the following lines worked perfectly:

Code: Select all

OLECMDID_PRINT = 6
OLECMDID_PRINT2 = 49
OLECMDEXECOPT_DONTPROMPTUSER = 2
PRINT_DONTBOTHERUSER_WAITFORCOMPLETION = 3
       
IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER, PRINT_DONTBOTHERUSER_WAITFORCOMPLETION
Thanks to all!
LO 4.3.4 on Windows 8.1
Post Reply