title: Use VBScript to VLOOKUP values, or evaluate formula?
body: <p>For processing orders we're using VBScripts to import them into accounting software. There are several suppliers, each with their own file format, mostly CSV and XML. The first step is to extract all the order lines (custom function per supplier), do some additional processing and then write it to the database, which is the same for all suppliers.</p>
  
  <p>One new supplier uses Excel files with all the order lines in one sheet, except for the corresponding VAT percentage value which are available in another sheet. The VAT percentage per item can be looked up using the <code>itemcode</code> from the order sheet.</p>
  
  <p><a href="https://i.stack.imgur.com/2Q6Mk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2Q6Mk.png" alt="Libreoffice Calc VLOOKUP a VAT value on another sheet"></a></p>
  
  <p>The company only has LibreOffice Calc and I understand you could do something like this in macro. However, it is a fully automated process and every other file is already handled by VBScript so I'd rather not make an exception or handle just this one order type manually (opening Calc and running the macro). So it has to be <strong>VBS and LibreOffice</strong> in this case.</p>
  
  <p>Here is the VBScript code I have so far:</p>
  
  <pre><code>Option Explicit
  
  ' variables
  Dim oSM, oDesk
  Dim sFilename
  Dim oDoc
  Dim oSheet
  
  Dim iLine
  
  Dim sCode, iCount, sDesc, fCost, Perc
  
  Set oSM = WScript.CreateObject("com.sun.star.ServiceManager") 
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop") 
  
  sFilename = "file:///C:/orders/import/supplier_orderlist_08-01-2019.xls"
  
  set oDoc = oDesk.loadComponentFromURL( sFilename, "_blank", 0, Array() ) 
  set oSheet = oDoc.getSheets().getByName("Orderlist")
  
  For iLine = 11 to 12 ' testing first 2 lines
  
      sCode   = oSheet.getCellByPosition(1, iLine).getString()
      iCount  = oSheet.getCellByPosition(2, iLine).getString()
      sDesc   = oSheet.getCellByPosition(5, iLine).getString()
      fCost   = oSheet.getCellByPosition(8, iLine).getString()
  
      'lookup doesn't work
      Perc    = Macro_VLOOKUP(sCode, oDoc)
  
      WScript.Echo sCode &amp; " - " &amp; iCount  &amp; "x - " &amp; sDesc &amp; " =&gt; " &amp; fCost &amp; ", " &amp; Perc &amp; "%"
  Next 'iLine
  
  WScript.Quit 1
  
  Function Macro_VLOOKUP(SearchValue, oDocGlob)
      Dim oSheetLook, CellRange
      Dim Column, Mode, svc, arg, Value
  
      Set oSheetLook = oDocGlob.getSheets().getByName("Itemlisttotal")
      Set CellRange = oSheetLook.getCellRangeByName("A1:B10000")
  
      Column = 1
      Mode = 0
      svc = createUnoService("com.sun.star.sheet.FunctionAccess") '&lt;- error: variable not defined
      arg = Array(SearchValue, CellRange, Column, Mode)
      Value = svc.callFunction("VLOOKUP", arg)
      Macro_VLOOKUP = Value
  End Function
  </code></pre>
  
  <p>It gives an error on the line with <code>createUnoService</code>:</p>
  
  <blockquote>
    <p>Variable not defined 'createUnoService'</p>
  </blockquote>
  
  <p>which is probably a LibreOffice Basic function and needs to be translated to the VBScript equivalent. There isn't much documentation or examples on this, so I can only guess, but <code>Set svc = WScript.CreateObject("com.sun.star.sheet.FunctionAccess")</code> also doesn't work and gives a "class name not found" error.</p>
  
  <p>Is it possible to do a VLOOKUP (or something similar) from VBScript in LibreOffice Calc?</p>
  
  <p>Or is there a way to evaluate a cell formula from a string at runtime?</p>