Hi!
Below is a rewritten macro code originally written by JohnSUN. I need your help. I tried to figure out how to do it, but I failed.
I would like to split text by character “_” ( chracter mean next line ). Below is an example when I have 6 not empty cells in range W22:W221 (screenshot)
The first two lines in the file looks like this:
part1cellW22_part2cellW22_part3cellW22_part4cellW22_
part1cellW23_part2cellW23_part3cellW23_part4cellW23_
And I would like it to look like this:
part1cellW22
part2cellW22
part3cellW22
part4cellW22
part1cellW23
part2cellW23
part3cellW23
part4cellW23
The contents of the txt file
Desired content of the txt file
Sub SaveNonEmptyW22W221
Dim aData As Variant, aOut As Variant, i As Long, j As Long
Dim sFileName As String
GlobalScope.BasicLibraries.loadLibrary("Tools")
aData = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("W22:W221").getDataArray()
ReDim aOut(UBound(aData))
j = -1
For i = 0 To UBound(aData)
If Trim(aData(i)(0)) <> "" Then
j = j + 1
aOut(j) = Trim(aData(i)(0))
EndIf
Next i
If j < 0 Then Exit Sub
ReDim Preserve aOut(j)
sFileName = StoreDocTo(aData(0)(0))
If sFileName = "" Then Exit Sub
SaveDataToFile(ConvertToURL(sFileName),aOut)
End Sub
Function StoreDocTo(DefaultName As String) as String
Dim oStoreDialog As Object
Dim ListAny(0) as Long
ListAny(0) = com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION_PASSWORD
oStoreDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
oStoreDialog.Initialize(ListAny)
oStoreDialog.AppendFilter("Obrzeże (.txt)", ".txt")
oStoreDialog.SetDisplayDirectory("C:\Ecru\Nowy Rozkrój")
oStoreDialog.SetDefaultName(DefaultName)
oStoreDialog.setValue(com.sun.star.ui.dialogs.ExtendedFilePickerElementIds.CHECKBOX_AUTOEXTENSION,0, true)
If oStoreDialog.Execute() = 1 Then StoreDocTo = oStoreDialog.Files(0)
oStoreDialog.dispose()
End Function
Thank you in advance for your help.
[erAck: edited to format as code block]