Ask Your Question
0

How to use Sub code in primary macro

asked 2016-02-05 09:56:05 +0200

Lion gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-02-05 13:32:10 +0200

JohnSUN gravatar image

updated 2016-02-15 16:12:36 +0200

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
edit flag offensive delete link more

Comments

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

Lion gravatar imageLion ( 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 :)

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

please refer my edited answer

Lion gravatar imageLion ( 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.

JohnSUN gravatar imageJohnSUN ( 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

Lion gravatar imageLion ( 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

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

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

Lion gravatar imageLion ( 2016-02-16 15:10:28 +0200 )edit
0

answered 2016-02-10 13:44:52 +0200

Lion gravatar image

updated 2016-02-15 13:47:48 +0200

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

edit flag offensive delete link more

Comments

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

JohnSUN gravatar imageJohnSUN ( 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?

Lion gravatar imageLion ( 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

JohnSUN gravatar imageJohnSUN ( 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

Lion gravatar imageLion ( 2016-02-15 13:21:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-05 09:56:05 +0200

Seen: 767 times

Last updated: Feb 15 '16