Excel VBA to Open Office code

Creating a macro - Writing a Script - Using the API

Excel VBA to Open Office code

Postby Open_the_Office » Mon Aug 14, 2017 1:30 pm

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   Expand viewCollapse view
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
Last edited by RoryOF on Mon Aug 14, 2017 2:01 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
OpenOffice 4.1 on Windows 10
Open_the_Office
 
Posts: 2
Joined: Mon Aug 14, 2017 1:19 pm

Re: Excel VBA to Open Office code

Postby Villeroy » Mon Aug 14, 2017 1:49 pm

Stick with Excel to run that code or write your own program that interfaces with OpenOffice or pay someone to do so.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel VBA to Open Office code

Postby kiloran » Sun Aug 20, 2017 2:19 pm

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.
--kiloran
LibreOffice 4.2.3.3 on Windows 7
kiloran
 
Posts: 25
Joined: Sun Oct 14, 2012 2:06 pm

Re: Excel VBA to Open Office code

Postby Zizi64 » Sun Aug 20, 2017 2:59 pm

Code: Select all   Expand viewCollapse view
    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.)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7826
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Excel VBA to Open Office code

Postby Open_the_Office » Wed Aug 23, 2017 3:20 pm

Hello,
this is the complete file.
Attachments
Extract_eBay_Product_Details Upwork.xlsm
(20.29 KiB) Downloaded 107 times
OpenOffice 4.1 on Windows 10
Open_the_Office
 
Posts: 2
Joined: Mon Aug 14, 2017 1:19 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 9 guests