Apache OpenOffice Community Forum

Code: Select all

Private Sub Macro_VLOOKUP()
     Dim oDoc, oDoc2 as Object
     Dim oRange1, oRange2, oRange3, oRange4, oRange5 as Object
     Dim oTextField1, oTextField2, oTextField3 as Object
     Dim oSheet, oSheet2  as Object
     Dim oFile, oFile2 as String
     Dim oURL, oURL2 as String
     Dim Text1, Text2, Text3 As Object
     Dim Sel1 As New com.sun.star.awt.Selection
     'Dim a, b, c, iLastrow As Long, i As Long, ii As Long
     Dim CellRange, CellRange2 as Object
     Dim SearchValue as Object
     Dim Column as Variant
     Dim Mode as Variant
     Dim svc
     Dim arg
     Dim Value
     Dim i, r as Integer
        oFile = "C:\Users\User\Desktop\Reserved_template.ods"
   	oURL = convertToUrl  (oFile)
        oDoc = starDeskTop.loadComponentFromURL (oURL, "_blank", 0, _
  	                                                                                    Array() )
        oSheet = oDoc.Sheets (0)
        oFile2 = "C:\Users\Евгений\Desktop\MB52.xlsx"
		oURL2 = convertToUrl  (oFile2)
		oDoc2 = starDeskTop.loadComponentFromURL (oURL2, "_blank", 0, _
		                                                     Array() )
  	                                                     
		oSheet2 = oDoc2.Sheets (0)
		CellRange = oDoc2.Sheets (0)
		
		oSheet = oDoc.getSheets().getByIndex(0)
		
		CellRange = oDoc2.getSheets().getByIndex(0).getCellRangeByName("A1:B10000")
		For i = 1 to 10000
		SearchValue = oDoc.getSheets().getByIndex(0).getCellByPosition (9,i) ("I & i+1 & ")
		Next i
		Column = 2
		Mode = 0
		svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
		arg = Array(SearchValue, CellRange, Column, Mode)

		Value = svc.callFunction("VLOOKUP",arg) 'here appear error
		For r = 1 to 10000
		CellRange2 = oDoc.getSheets.getByIndex(0).getCellByPosition (8,r) ("H & r+1 &")
		Next r
		CellRange2.setDate(svc.callFunction) 'here I wish set the resulting value

Code: Select all

Sub Macro_VLOOKUP()
      oDoc = ThisComponent
      oSheet = oDoc.Sheets (0)
      CellRange = oSheet.getCellRangeByName("A1:B10000")
      SearchValue = 13
      Column = 2
      Mode = 0
      svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
      arg = Array(SearchValue, CellRange, Column, Mode)
      Value = svc.callFunction("VLOOKUP",arg)
      print typename(value), value
End Sub

Code: Select all

CellRange2.setDate(svc.callFunction) 'here I wish set the resulting value

Code: Select all

CellRange.setValue(value)

Code: Select all

someCell.setValue(srv.callFunction("VLOOKUP",Array(range,2,0))

Code: Select all

Sub Test()
Dim common(2 To 5, 3 To 7) As Variant
common(2,3) = 3   : common(3,3) = 17  : common(4,3) = -8  : common(5,3) = 7
common(2,5) = "A" : common(3,5) = "B" : common(4,5) = "C" : common(5,5) = "D"
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
find = 17 : col = 3 : mode = 0
r = fa.CallFunction("VLOOKUP",Array(find, common, col, mode)
MsgBox(r)
End Sub

Code: Select all

res = functionAccessObject.CallFunction("THISFUNCTIONNAME",myArray)