Page 1 of 1

Excel VBA to Open Office code

Posted: Mon Aug 14, 2017 1:30 pm
by Open_the_Office
Hello,

I fail to convert this excel macro to open office and would be happy if somebody can help me. This macro checks automatically the prices from ebay products.
Can somebody tell me, if it is possible in Open Office, to excess web pages and extract info's?

Code: Select all

Sub Get_eBay_Product() 
    'Macro to extract Product Details from eBay shopping website for single product
    'Author     : Raghu Ram Alla
    'Date       : March 19, 2015
    'Website    : http://www.QuadExcel.com
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    iRow = 5:       iCol = 1
    
    URL = Wks_eBay_Prod.Range("ProdURL").Value
    xmlHttp.Open "GET", URL, False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send

    Dim html As Object
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = xmlHttp.responseText
    
    'Item Condition
    Set objShipping = html.getElementById("vi-itm-cond")
    If Not objShipping Is Nothing Then
        Wks_eBay_Prod.Cells(iRow, iCol).Value = objShipping.innerText
    End If
    iCol = iCol + 1
    
    'Product Name
    Set objShipping = html.getElementById("vi-lkhdr-itmTitl")
    If Not objShipping Is Nothing Then
        Wks_eBay_Prod.Cells(iRow, iCol).Value = objShipping.innerText
    End If
    iCol = iCol + 1
    
    'Price
    Set objShipping = html.getElementById("prcIsum")
    If Not objShipping Is Nothing Then
        Wks_eBay_Prod.Cells(iRow, iCol).Value = objShipping.innerText
    End If
    iCol = iCol + 1
    
    'Shipping
    Set objShipping = html.getElementById("shippingSection").getElementsByTagName("td")(0)
    If Not objShipping Is Nothing Then
        Set divShip = objShipping.ChildNodes(1)
        Wks_eBay_Prod.Cells(iRow, iCol).Value = divShip.innerHTML
    End If
End Sub

Re: Excel VBA to Open Office code

Posted: Mon Aug 14, 2017 1:49 pm
by Villeroy
Stick with Excel to run that code or write your own program that interfaces with OpenOffice or pay someone to do so.

Re: Excel VBA to Open Office code

Posted: Sun Aug 20, 2017 2:19 pm
by kiloran
It's certainly possible to extract web page info with OpenOffice Basic, though it's not as nice as Excel VBA. If you use Python macros with OpenOffice, there's no limit to what you can do, it's excellent.

Re: Excel VBA to Open Office code

Posted: Sun Aug 20, 2017 2:59 pm
by Zizi64

Code: Select all

    URL = Wks_eBay_Prod.Range("ProdURL").Value
Please give us more details:

What is the Wks_eBay_Prod? Is it a Sheet in the actual/another spreadsheet document?
What is the .Range("ProdURL")? Is it a Named range on the sheet?


Can you upload your sample spreadsheet file here?


Maybe the LibreOffice can run your macros with the option "VBAsupport 1", because the LO has a littlebit higher competibility with the foreign file formats and the VBA. (I just tried it, but I got an error message at the quoted code line.)

Re: Excel VBA to Open Office code

Posted: Wed Aug 23, 2017 3:20 pm
by Open_the_Office
Hello,
this is the complete file.