I know it's ugly. I used the macro generator, and did everything needed to fix the code afterwards.... So it works, but has the following issues:
1 - I'm forced to define a range, but it will change every month. I would like to substitute row count, or some kind of better auto fill to make the range dynamic based on content.
2 - Everything opporates in the GUI, would love to make it non-GUI.
3 - Save automatically and just append _new to the end of the file.
For anyone looking to fix their recorded macro... here's my ugly fix
Code: Select all
REM ***** BASIC *****
Sub MainFixed
rem ----------------------------------------------------------------------
rem define variables
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")
rem ---------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args4())
rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1:$A$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
dispatcher.executeDispatch(document, ".uno:ChangeCaseToTitleCase", "", 0, args5())
rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$B$1:$B$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())
dispatcher.executeDispatch(document, ".uno:ChangeCaseToTitleCase", "", 0, args6())
rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())
rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "StringName"
args8(0).Value = "=TRIM(A1)"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args8())
dim args8a(0) as new com.sun.star.beans.PropertyValue
args8a(0).Name = "ToPoint"
args8a(0).Value = "$C$1:$C$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8a())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())
rem --------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$D$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())
rem --------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "StringName"
args10(0).Value = "=TRIM(B1)"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args10())
rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "$D$1:$D$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())
rem ----------------------------------------------------------------------
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "ToPoint"
args22(0).Value = "$E$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args22())
rem ----------------------------------------------------------------------
dim args23(0) as new com.sun.star.beans.PropertyValue
args23(0).Name = "StringName"
args23(0).Value = "=C1&"+CHR$(34)+" "+CHR$(34)+"&D1"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args23())
rem ----------------------------------------------------------------------
dim args25(0) as new com.sun.star.beans.PropertyValue
args25(0).Name = "ToPoint"
args25(0).Value = "$E$1:$E$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args25())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())
rem ----------------------------------------------------------------------
dim args30(0) as new com.sun.star.beans.PropertyValue
args30(0).Name = "ToPoint"
args30(0).Value = "$G$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args30())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args30())
rem ----------------------------------------------------------------------
dim args31(0) as new com.sun.star.beans.PropertyValue
args31(0).Name = "ToPoint"
args31(0).Value = "$F$1:$F$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args31())
dispatcher.executeDispatch(document, ".uno:ChangeCaseToLower", "", 0, Array())
rem ----------------------------------------------------------------------
dim args33(17) as new com.sun.star.beans.PropertyValue
args33(0).Name = "SearchItem.StyleFamily"
args33(0).Value = 2
args33(1).Name = "SearchItem.CellType"
args33(1).Value = 0
args33(2).Name = "SearchItem.RowDirection"
args33(2).Value = true
args33(3).Name = "SearchItem.AllTables"
args33(3).Value = false
args33(4).Name = "SearchItem.Backward"
args33(4).Value = false
args33(5).Name = "SearchItem.Pattern"
args33(5).Value = false
args33(6).Name = "SearchItem.Content"
args33(6).Value = false
args33(7).Name = "SearchItem.AsianOptions"
args33(7).Value = false
args33(8).Name = "SearchItem.AlgorithmType"
args33(8).Value = 0
args33(9).Name = "SearchItem.SearchFlags"
args33(9).Value = 71680
args33(10).Name = "SearchItem.SearchString"
args33(10).Value = "@gmail.con"
args33(11).Name = "SearchItem.ReplaceString"
args33(11).Value = "@gmail.com"
args33(12).Name = "SearchItem.Locale"
args33(12).Value = 255
args33(13).Name = "SearchItem.ChangedChars"
args33(13).Value = 2
args33(14).Name = "SearchItem.DeletedChars"
args33(14).Value = 2
args33(15).Name = "SearchItem.InsertedChars"
args33(15).Value = 2
args33(16).Name = "SearchItem.TransliterateFlags"
args33(16).Value = 1280
args33(17).Name = "SearchItem.Command"
args33(17).Value = 3
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args33())
rem ----------------------------------------------------------------------
dim args35(17) as new com.sun.star.beans.PropertyValue
args35(0).Name = "SearchItem.StyleFamily"
args35(0).Value = 2
args35(1).Name = "SearchItem.CellType"
args35(1).Value = 0
args35(2).Name = "SearchItem.RowDirection"
args35(2).Value = true
args35(3).Name = "SearchItem.AllTables"
args35(3).Value = false
args35(4).Name = "SearchItem.Backward"
args35(4).Value = false
args35(5).Name = "SearchItem.Pattern"
args35(5).Value = false
args35(6).Name = "SearchItem.Content"
args35(6).Value = false
args35(7).Name = "SearchItem.AsianOptions"
args35(7).Value = false
args35(8).Name = "SearchItem.AlgorithmType"
args35(8).Value = 0
args35(9).Name = "SearchItem.SearchFlags"
args35(9).Value = 65536
args35(10).Name = "SearchItem.SearchString"
args35(10).Value = "yahoo.con"
args35(11).Name = "SearchItem.ReplaceString"
args35(11).Value = "yahoo.com"
args35(12).Name = "SearchItem.Locale"
args35(12).Value = 255
args35(13).Name = "SearchItem.ChangedChars"
args35(13).Value = 2
args35(14).Name = "SearchItem.DeletedChars"
args35(14).Value = 2
args35(15).Name = "SearchItem.InsertedChars"
args35(15).Value = 2
args35(16).Name = "SearchItem.TransliterateFlags"
args35(16).Value = 1280
args35(17).Name = "SearchItem.Command"
args35(17).Value = 3
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args35())
rem ----------------------------------------------------------------------
dim args36(0) as new com.sun.star.beans.PropertyValue
args36(0).Name = "ToPoint"
args36(0).Value = "$G$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args36())
rem ----------------------------------------------------------------------
dim args37(0) as new com.sun.star.beans.PropertyValue
args37(0).Name = "StringName"
args37(0).Value = "=TRIM(F1)"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args37())
rem ----------------------------------------------------------------------
dim args38(0) as new com.sun.star.beans.PropertyValue
args38(0).Name = "ToPoint"
args38(0).Value = "$G$1:$G$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args38())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())
rem ----------------------------------------------------------------------
dim args39(0) as new com.sun.star.beans.PropertyValue
args39(0).Name = "ToPoint"
args39(0).Value = "$E$1:$E$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args39())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args47a(0) as new com.sun.star.beans.PropertyValue
args47a(0).Name = "Nr"
args47a(0).Value = 2
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args47a())
rem ----------------------------------------------------------------------
rem dim args44(1) as new com.sun.star.beans.PropertyValue
rem args44(0).Name = "Name"
rem args44(0).Value = "Sheet2"
rem args44(1).Name = "Index"
rem args44(1).Value = 2
rem dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args44())
rem dispatcher.executeDispatch(document, ".uno:FocusCellAddress", "", 0, args44())
rem ----------------------------------------------------------------------
dim args45(0) as new com.sun.star.beans.PropertyValue
args45(0).Name = "ToPoint"
args45(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args45())
rem ----------------------------------------------------------------------
dim args46(5) as new com.sun.star.beans.PropertyValue
args46(0).Name = "Flags"
args46(0).Value = "A"
args46(1).Name = "FormulaCommand"
args46(1).Value = 0
args46(2).Name = "SkipEmptyCells"
args46(2).Value = false
args46(3).Name = "Transpose"
args46(3).Value = false
args46(4).Name = "AsLink"
args46(4).Value = true
args46(5).Name = "MoveMode"
args46(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args46())
rem ----------------------------------------------------------------------
dim args47(0) as new com.sun.star.beans.PropertyValue
args47(0).Name = "Nr"
args47(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args47())
rem ----------------------------------------------------------------------
dim args48(0) as new com.sun.star.beans.PropertyValue
args48(0).Name = "ToPoint"
args48(0).Value = "$G$1:$G$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args48())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args49(0) as new com.sun.star.beans.PropertyValue
args49(0).Name = "Nr"
args49(0).Value = 2
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args49())
rem ----------------------------------------------------------------------
dim args49a(0) as new com.sun.star.beans.PropertyValue
args49a(0).Name = "ToPoint"
args49a(0).Value = "$B$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args49a())
rem ----------------------------------------------------------------------
dim args50(5) as new com.sun.star.beans.PropertyValue
args50(0).Name = "Flags"
args50(0).Value = "A"
args50(1).Name = "FormulaCommand"
args50(1).Value = 0
args50(2).Name = "SkipEmptyCells"
args50(2).Value = false
args50(3).Name = "Transpose"
args50(3).Value = false
args50(4).Name = "AsLink"
args50(4).Value = true
args50(5).Name = "MoveMode"
args50(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args50())
rem ----------------------------------------------------------------------
dim args51(2) as new com.sun.star.beans.PropertyValue
rem args51(0).Name = "URL"
rem args51(0).Value = "file:///Users/username/file.csv"
rem args51(1).Name = "FilterName"
rem args51(1).Value = "Text - txt - csv (StarCalc)"
rem args51(2).Name = "FilterOptions"
rem args51(2).Value = "9/44,34,76,1,,0,false,false"
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args51())
End Sub