Ask Your Question
0

Recorded Macros snitched together with some other Google's search results some guru gave out won't run successfully, they run fine individually. Please modify so to make them work.

asked 2020-08-27 21:02:16 +0100

TingliChan gravatar image

Sub Final_import_A_TXT() Dim oDoc As Object : oDoc = ThisComponent

Dim opt(1) As New com.sun.star.beans.PropertyValue
path = ConvertToURL("C:\Temp\A.txt")
opt(0).Name = "FilterName"
opt(0).Value = "Text - txt - csv (StarCalc)"
opt(1).Name = "FilterOptions"
opt(1).Value = "124,34,32,1,,0,false,false,false,false"

oDoc = ThisComponent
cell = oDoc.Sheets.getByName("Sheet1").getCellRangeByName("A1")

' target = next_cell(cell) ' target = this_cell(cell) ' I avoided function call so to make everything done in 1 sub without going out anywhere else target = cell.SpreadSheet.getCellByPosition(0, 0)

csv = StarDesktop.loadComponentFromURL(path, "_blank", 0, opt())

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 = "Flags"
args1(0).Value = "C"
dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args1())

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

rem ----------------------------------------------------------------------
dim args3(20) as new com.sun.star.beans.PropertyValue
args3(0).Name = "SearchItem.StyleFamily"
args3(0).Value = 2
args3(1).Name = "SearchItem.CellType"
args3(1).Value = 0
args3(2).Name = "SearchItem.RowDirection"
args3(2).Value = true
args3(3).Name = "SearchItem.AllTables"
args3(3).Value = false
args3(4).Name = "SearchItem.SearchFiltered"
args3(4).Value = false
args3(5).Name = "SearchItem.Backward"
args3(5).Value = false
args3(6).Name = "SearchItem.Pattern"
args3(6).Value = false
args3(7).Name = "SearchItem.Content"
args3(7).Value = false
args3(8).Name = "SearchItem.AsianOptions"
args3(8).Value = false
args3(9).Name = "SearchItem.AlgorithmType"
args3(9).Value = 0
args3(10).Name = "SearchItem.SearchFlags"
args3(10).Value = 65536
args3(11).Name = "SearchItem.SearchString"
args3(11).Value = " "
args3(12).Name = "SearchItem.ReplaceString"
args3(12).Value = ""
args3(13).Name = "SearchItem.Locale"
args3(13).Value = 255
args3(14).Name = "SearchItem.ChangedChars"
args3(14).Value = 2
args3(15).Name = "SearchItem.DeletedChars"
args3(15).Value = 2
args3(16).Name = "SearchItem.InsertedChars"
args3(16).Value = 2
args3(17).Name = "SearchItem.TransliterateFlags"
args3(17).Value = 3328
args3(18).Name = "SearchItem.Command"
args3(18).Value = 3
args3(19).Name = "SearchItem.SearchFormatted"
args3(19).Value = false
args3(20).Name = "SearchItem.AlgorithmType2"
args3(20).Value = 1
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args3())

sheet = csv.Sheets.getByIndex(0)
cell = sheet.getCellRangeByName("A1")
cursor = sheet.createCursorByRange(cell)
cursor.collapseToCurrentRegion()
ra = cursor.RangeAddress

data = sheet.getCellRangeByPosition(ra.StartColumn, ra.StartRow+0, ra.EndColumn, ra.EndRow).DataArray

' copy_to(target, data) cellRa = cell.RangeAddress SpSheet = cell.SpreadSheet cols = cellRa.EndColumn + UBound(data(0)) rows = cellRa.EndRow + Ubound(data) range = SpSheet.getCellRangeByPosition(cellRa.StartColumn, cellRa.StartRow, cols, rows) range.DataArray = data

csv.close(True)

Dim oSheet As Object    
oSheet = oDoc.CurrentController.ActiveSheet
cell = oSheet.getCellRangeByName("A1")
cursor = oSheet.createCursorByRange(cell)
cursor.collapseToCurrentRegion()
ra = cursor.RangeAddress
Dim oEntireSheet As Object 
oEntireSheet = oSheet.getCellRangeByPosition(ra.StartColumn, ra.StartRow, ra.EndColumn, ra.EndRow)
oDoc.CurrentController.select(oEntireSheet)

rem Starting from here Errors shot, I guessed something wrong around ... (plus)

edit retag flag offensive close merge delete

Comments

1

Make the title short and meaningful.

gabix gravatar imagegabix ( 2020-08-28 06:29:39 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-08-28 07:02:57 +0100

Zizi64 gravatar image

You must write your own macros, and YOU MUST learn about the programming of the LibreOffice macros. The macro recorder has a limited capability. It is better to WRITE the whole (and all) macros based on the API functions and procedures - instead of recording them.

Andrew Pitonyak's free macro books will help you to learn the programming in the LO. Then you need an object inspecition tool (XrayTool) to list the lots of the properties and methods of the programming objects.

Andrew's macro books

XrayTool

edit flag offensive delete link more

Comments

1

Thank you for your advice. I downloaded Andrew Pitonyak's, 3-4 days ago and started to learn the least from that Big database knowledge, knowing this must be long way to go. Finding some examples in Japanese in google search, I'd been also kind of sneaking around these resources on hand to make things work in short period. Actually some of them worked out pretty fine with no clue what I actually were doing. So it became my inspiration to continue study these intriguing stuff.

TingliChan gravatar imageTingliChan ( 2020-08-28 07:58:07 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-27 21:02:16 +0100

Seen: 38 times

Last updated: Aug 28