Like in topic. Can anyone help me write a macro? Please:D
No, @Zizi64, judging by this comment, he will not read Pitonyak’s books - he will torture the macro recorder until he succeeds or breaks the office. He also will ignores my code because he does not understand.
1 Like
Dear JohnSUN,
I am strongly focused on achieving my goal. I wrote the first macro in my life yesterday evening. Yesterday evening I also downloaded the Pitonyak’s book. Yes you are right. I don’t understand everything in this book, but Rome wasn’t built in a day. Your code is working fine. Thanks ;D
Apply standard filter “- not empty -”
Copy result to blank sheet.
Save as csv
Option Explicit
Sub SaveNonEmptyV21V221
Dim aData As Variant, aOut As Variant, i As Long, j As Long
Dim sFileName As String
GlobalScope.BasicLibraries.loadLibrary("Tools")
aData = ThisComponent.getSheets().getCellRangesByName("V21:V221")(0).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("RozkrĂłj (.###)", ".###")
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
JohnSun,
Could it be possible to change the macro so that after starting the file saving window appears with the suggested file name from value of cell V21? Thank you in advance for your help.
SaveDataToFile(ConvertToURL("C:\Users\esiu\Desktop\" & inputBox("Enter File Name","File Name",aData(0)(0))),aOut)
…and you can get the actual Desktop path (if you want use this macro on an another /Windows?/ computer):
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=26130
REM ***** BASIC *****
option Explicit
Function myDesktopPath() as string
Dim wsh As Variant
Dim folderName As String
wsh = CreateObject("WScript.Shell")
folderName = "Desktop"
'MsgBox(wsh.SpecialFolders.Item(folderName), 0, folderName)
myDesktopPath = wsh.SpecialFolders.Item(folderName)
End functionThank you JohnSUN. Good job. Is it possible for this window to be like in windows 10? Ultimately, I would like to choose a different save location. And can the file have the .txt extension?
My .ods file has many sheets. Can the script handle the active sheet?
Thank you JohnSUN. Good job!
-
Is it possible for “save window” to be like in windows 10 (like on screenshot below)?
-
Is it possible to set the default saving path to: “C:\Ecru\Nowy Rozkrój” ?
Ultimately, I would like to choose a different save location (one of the folders) -
Can the file have the “.###” extension (not “.txt”)?
-
Could the default filename be the value of cell “V21”. ###?
I will be very grateful for your or anyone else’s help.
JohnSUN you are great!!
Wow! Everything works as I wanted. I’m impressed. Well done! Thank you very much again!
@JohnSUN: Is it possible to simplify/speed up the code and do not iterate through all the cells, checking each one to see if it is empty?
E.g.
oRanges = oRange.queryContentCells(nContentFlags) 'nContentFlags:=1+2+4+16
OR in your case: getCellRangesByName(“V21:V221”)
And further:
oEnum = oRanges.Cells.CreateEnumeration
Do While oEnum.hasMoreElements
oCell = oEnum.NextElement
' NOTE: oCell value is not empty here,
' the empty cell are skipped automatically (!)
Loop
The CreateEnumeration method ignores empty cells.
Okay, let’s think about it together. First of all, let’s try to understand what @esiu meant by “empty cells”. Judging by the fact that write to the file the values from column V, he designed a calculation sheet, where combined the input form (in columns A:B descriptions of the input data, then several columns with the input data), several columns of formulas that calculate the result, and finally in column V formulas that collect the result of the calculation, prepared for output. In other words, “empty cells” in this case meant cells with “empty strings”. That is, nContentFlags: = ... +4+...+ 16 will return all these cells and all this "" will be stored in output file, right?
Now please pay attention to the phrase
Remember how you learned programming, in what order you learned the “loop” construction. First you learned the word “For”, then mastered the words “While” and “Until”. And only then did you hear the word “iterator”. It was in that order, am I wrong?
Your construction Do While oEnum.hasMoreElements (as well as data = [row[0] for row in data if row[0]!='']) introduces an extremely steep learning curve for a person who takes the first steps in programming, this is of little use.
We must to be more merciful to people. (This is just my humble opinion, you have every right to disagree with it)
And about speeding up the code. Do you really think that for 200 cells the difference in code execution speed will be noticeable?
Okay, I agree. It’s not about speed, but about the elegance of the code. But all in good time. In any case, the discussion is not useless.
@karolus is already ahead of us on this point (see below). 
JohnSun,
Today, I tested the macro and everything works perfect but I miss two things. Will you help me? Please.
- Is it possible that at the time of selecting the path and saving the file “. ###” to save this path in clipboard with the extension changed to “.pdf”? (I need to use it in another program)
- Is it possible that at the end of the macro the file “.ods” from which I get the data will be overwritten?
Is it possible that at the time of selecting the path and saving the file “. ###” to save this path to the clipboard with the extension changed to “.pdf” (I need to use it in another program.)
Oh my dear BASIC-hackers!!
from pathlib import Path
home_path = Path().home()
def backup_v():
doc = XSCRIPTCONTEXT.getDocument()
data = doc.CurrentController.ActiveSheet['V21:V221'].DataArray
data = [row[0] for row in data if row[0]!='']
with open(home_path / data[0] , 'w' , encoding='utf8' ) as outfile:
for entry in data:
outfile.write(f'{entry}\n' )
#takes from current_active_Sheet
#takes filename from cell V21
#handles utf8
#@zizi64 not Windows only
1 Like
home_path = Path().home()? Perhaps os.path.expanduser("~/Desktop")?