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 here as the document object had been declared Error message said: Variable doc… already defined If I Rem to comment them out Next Err msg said: property or method not found:CurrentController. With no background in programming language I kinda try to make things work out of the trials and errors. Someone with the wisdom may have this figured out with no difficulty. Thank you in advance.
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”)
dispatcher.executeDispatch(document, “.uno:SelectColumn”, “”, 0, Array())
rem ----------------------------------------------------------------------
Dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = “aExtraWidth”
args4(0).Value = 270
dispatcher.executeDispatch(document, “.uno:SetOptimalColumnWidth”, “”, 0, args4())
Dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
End Sub