How to use Sub code in primary macro

English
, ,
#1

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 :slight_smile:
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
    oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop())
else
    msgbox "Not found"
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 ----------------------------------------------------------------------
rem get access to the document
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)
   
   FilterDesc.ContainsHeader = 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

#2

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.
GlobalScope.BasicLibraries.LoadLibrary("Tools")
	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 oDoc As Object	' Variable with your source spreadsheet
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!
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
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
#3

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

#4

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

#5

please refer my edited answer

#6

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.

#7

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

#8

“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

#9

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

#10

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

#11

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())

#12

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?

#13

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

#14

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

#15

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.