How to use Sub code in primary macro

Hello Guys, I have little stuck in macro My macro running successfully but after that I have some function that I need to apply in the macro (I didn't know so much knowledge of programming but I am learning) last time I also stuck but then i got answer from you macro ninja :) let me explain what is an issue

as per I told you my macro is running successfully , only functions are not working Macro is in one file (Its not in libreoffice library) that means If I want to use macro in any PC then I could. this is macro code

    Sub AGImacrofortejet2

Dim oDoc As Object
Dim sUrl As String

Dim Prop(1) as New com.sun.star.beans.PropertyValue

Pro

p(0).name="FilterName"
Prop(0).value="Text - txt - csv (StarCalc)"
Prop(1).name="FilterOptions"
Prop(1).value="59/9,34,0,1,1/1/1/1/1/1/1/1"

sUrl = convertToURL("D:\abc\xyz\Artikelliste.csv")

if fileExists(sUrl) then
else
end if

dim document as object
dim dispatcher as object

document = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Name"
args1(0).Value = "abc_source"

dispatcher.executeDispatch(document, ".uno:RenameTable", "", 0, args1())


above code is working fine Now this code is not working I mean Its not getting error but Its not performing operation

    sub filter_ON_Quantity()

dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Dim xRange as object
Dim FilterDesc as Object
Dim FilterFields(1) as new com.sun.star.sheet.TableFilterField

Dim data_da
Dim data_a

xRange = thiscomponent.getcurrentcontroller.activesheet.getCellRangeByName("C1:C60000")
FilterDesc = xRange.createFilterDescriptor(true)

FilterFields(0).Field = 0
FilterFields(0).IsNumeric = false
FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FilterFields(0).StringValue = "0"

FilterDesc.SetFilterFields(FilterFields)
xRange.Filter(FilterDesc)

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfDataSel", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

End sub


above code is regarding filter , that remove data which has data 0 but its not performing , In my Pc its running successfully but there macro is stored in "My Macros & Dialogs" and this macro is stored in one ods file (I am just thinking probably error could be this line I am not professional programmer but I just wonder

document   = ThisComponent.CurrentController.Frame


I hope you could get my point Thank you

edit retag close merge delete

Sort by » oldest newest most voted

Try not to use Dispatcher unnecessarily. UNO contains all the necessary tools to solve different problems. Your macro could look like this:

Sub AGImacrofortejet3
Dim oDoc As Object
Dim sUrl As String
Dim Prop(1) as New com.sun.star.beans.PropertyValue
Dim oSheet As Variant
REM Add this line to use the functions from the standard library "Tools" -
REM OpenDocument(), GetColumnIndex() etc.
Prop(0).name="FilterName"
Prop(0).value="Text - txt - csv (StarCalc)"
Prop(1).name="FilterOptions"
Prop(1).value="59/9,34,0,1,1/1/1/1/1/1/1/1"
sUrl = "D:\abc\xyz\Artikelliste.csv"

oDoc = OpenDocument(convertToURL(sUrl), Prop())
If IsNull(oDoc) Then
MsgBox  "File " + sUrl + " not found or can not be opened"
Exit Sub
End If
oSheet = oDoc.getSheets().getByIndex(0) ' We know that this workbook has only one sheet '
oSheet.setName("abc_source")    ' Rename it '
Call removeRowsByFltr(oSheet, GetColumnIndex(oSheet, "C1")) ' Word "Call" is optional '
End Sub

Sub removeRowsByFltr(oSheet As Variant, zeroColumnIndex As Long, Optional filterValue As Variant)
Dim oCursor As Variant      ' The cursor helps select used range of the sheet '
Dim oDataArray As Variant   ' Array of arrays with values from all cells from used range '
Dim rowCount As Long        ' Height of used range '
Dim colCount As Long        ' Width of used range '
Dim i As Long, j As Long    ' Indexes of cycles '
If IsMissing(filterValue) Then filterValue = 0  ' Default value for our "filter" '
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True) ' Now cursor have all used range '
oDataArray = oCursor.getDataArray()
rowCount = UBound(oDataArray)
colCount = UBound(oDataArray(0))
If zeroColumnIndex > colCount Then Exit Sub ' Nothing to do '
For i = rowCount To 0 Step -1
If oDataArray(i)(zeroColumnIndex) = filterValue Then
For j = i To rowCount-1
oDataArray(j) = oDataArray(j+1)
Next j
rowCount = rowCount-1
EndIf
Next i
oCursor.clearContents(7) ' Remove from used range values, text and date '
If rowCount >= 0 Then
ReDim Preserve oDataArray(rowCount)
oSheet.getCellRangeByPosition(0, 0, colCount, rowCount).setDataArray(oDataArray)
EndIf
End Sub


(I can not write more one answer. So write here)

Your last code snippet can be so:

Sub AGImacrofortejet2_small_version
Dim sUrl As String  ' Path and name of source file
Dim Prop(1) as New com.sun.star.beans.PropertyValue ' Define type of source file
Prop(0).name="FilterName"
Prop(0).value="Text - txt - csv (StarCalc)"
Prop(1).name="FilterOptions"
Prop(1).value="59/9,34,0,1,1/1/1/1/1/1/1/1"

sUrl = "/home/jvishal/Macro/AGI_Testing/Artikelliste-Gesamt.csv"
REM In vain you throw this line from your code!
REM Open document from sUrl and set to oDoc link on it
oDoc = OpenDocument(convertToURL(sUrl), Prop())
REM Rename sheet. With dispatcher you must write 5 lines
oDoc.getSheets().getByIndex(0).setName("AGI_Source")
REM Insert "Sheet2". With dispatcher you must write 6 lines
oDoc.getSheets().insertNewByName("Sheet2",1)
REM Write word "sku"  to A1. This is your 9 lines
oDoc.getSheets().getByIndex(0).getCellByPosition(0,0).setString("sku")
End Sub

more

Ok I think this function could work but I dont know How to remove Dispatcher code from code I mean you mention that don't use Dispatcher unnecessarily so just tell me How do I remove that code from code

dim document as object
dim dispatcher as object
document = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")


I think this is variable assign or Dispatcher assignment now just sugget here How Can I avoid this code

( 2016-02-15 13:39:00 +0200 )edit

because whole macro contain this code I mean Its using Dispatcher .. let me share one thing here that I don't know VB coding :)

( 2016-02-15 13:40:18 +0200 )edit

( 2016-02-15 13:56:36 +0200 )edit

Does your task is "open existing file Artikelliste-Gesamt.csv and store to Output_AGI.csv some data from it - columns 6, 12 and 13 (F, L and M) for each row that have "not equal zero" in column M"? Right? If in plain words the problem is stated so short, so the length of the program may not be so big.

( 2016-02-15 15:21:43 +0200 )edit

No.. i think we are making mistake here Yes I have input file 1.I need to open that file and give name "agi_source" 2.Then Insert a blank sheet 3.In that blank sheet I have make proper data from "agi source" Note : Don't repair input file(agi_source) just open that file, take require data and copy into new sheet and repair if need any thing Can you tell me how could I attach file for your reference then I could attach here so you can understand fir your info : I have 50 macros and I have to do

( 2016-02-16 09:33:55 +0200 )edit

"Don't repair" must mean "not modify"? 50 macros... And all of them was created with "macro recorder"? It's a lot of work and hard work! My e-mail johnsun at i dot ua

( 2016-02-16 10:16:00 +0200 )edit

I have send you an email pls check it and try to test it

( 2016-02-16 15:10:28 +0200 )edit

Hi John just inform me what should be change here

    Sub AGImacrofortejet2
Dim oDoc As Object
Dim sUrl As String
Dim Prop(1) as New com.sun.star.beans.PropertyValue
Prop(0).name="FilterName"
Prop(0).value="Text - txt - csv (StarCalc)"
Prop(1).name="FilterOptions"
Prop(1).value="59/9,34,0,1,1/1/1/1/1/1/1/1"
sUrl = convertToURL("/home/jvishal/Macro/AGI_Testing/Artikelliste-Gesamt.csv")

dim document as object
dim dispatcher as object

document = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Name"
args1(0).Value = "AGI_Source"

dispatcher.executeDispatch(document, ".uno:RenameTable", "", 0, args1())
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Name"
args2(0).Value = "Sheet2"
args2(1).Name = "Index"
args2(1).Value = 1
dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args2())

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "StringName"
args4(0).Value = "sku"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

End Sub


Now If I dont want to use Dispatcher then I think I have change code here

dim document as object
dim dispatcher as object
document = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")


But What ? Simple How can avoid dispatcher I want to use your function It would be good I hope

more

OK. If you realy don't want small programm... Try to remove lines for jump to C2 and GoDownToEndOfDataSel from filter_ON_Quantity() - let only xRange.Filter() and DeleteRows. After call of subroutine add repeat jump to C1 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7()) and remove filter dispatcher.executeDispatch(document, ".uno:DataFilterRemoveFilter", "", 0, Array())

( 2016-02-11 09:38:22 +0200 )edit

what is meaning of "After call of subroutine add repeat jump to C1" I can't understand here what is subroutine ? and what should I change code exactly?

( 2016-02-12 13:31:38 +0200 )edit

The "Subroutine" is a small independent piece of code between words Sub and End Sub. In your case it is sub filter_ON_Quantity(). "call of subroutine" - in your macro this is a line filter_ON_Quantity before line Dim Propval(1) as New com.sun.star.beans.PropertyValue. Just add between this lines code from my previous comment

( 2016-02-12 20:53:46 +0200 )edit

Hi John, This code seems for remove filter... but I have already code for remove filter, I don't know why you are asking for put this code Do I need to any change in my Filter code i mean in "sub filter_ON_Quantity()" ? then pls mention here, actulayy I am getting so much confusion here, you wrote here remove to jump c1 and EndOfTheData....

OR ..probably you getting misunderstand here mate This is filter code

( 2016-02-15 13:21:06 +0200 )edit