Exactly as it says. I want to create a dropdown that automatically lists all the sheets in the same folder as the sheet the dropdown list is in.
I assume this has to be done (via a macro) in basic or python of which I do not know either.
Additional question: Do the other sheets have to be open to pull data from them? I ask because I plan on pulling data from specific cells in specific sheets within a folder but I hopefully do not need to have them open.
Depends. For a more or less static folders you can simply write a dir or ls command to a text-file (use .csv as extension). Importing this and using it as source-list for data–validity may give the same function.
Yes and no. No, because YOU don’t need to open the files. LibreOffice will do this. But therefore the file has to be accessible.
Long ago I used DDE-links for this kind of references. (Small scale. If yo plan to access lots of files, maybe you should consider a better structure for your data.)
For a macro you may start here:
@Madmaxneo
Did finally one of the posts provide a solution?
If so, mark that post as the solution.
== ==
Do you mean a cell using the >Data>Validity feature? How many results do you expect? Dropdown lists tend to grow, and get unhandy then.
== ==
Do you need a recursion through subfolders?
== ==
How?
Any process pulling data/content from a file needs to open it.
This does not mean that the file must be shown in a window.
Hallo
run the following python,
use the created list as a source for ⇒Data⇒Validation⇒Cellrange
from pathlib import Path
from uno import fileUrlToSystemPath as url_2_path
def ods_from_same_folder( *_ ):
"""
drops the ods_filenames from same folder
into first sheet below »A1«
"""
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets[0]
sheet['A:A'].clearContents(-1)
folder = ( _path:=Path(url_2_path(doc.URL)) ).parent
out = [ [p.name] for p in folder.glob("*.ods") if not p == _path ]
sheet[0:len(out), 0 ].DataArray = out
I had to install APSO for python scripts, but I can’t get it to work. I added Notepad++ as the editor but it still will not open a new python macro. No idea why.
Is there a tutorial for this somewhere?
I got it to work and it does what it is supposed to but, it locks up Calc. I am unable to edit cells or go to other sheets in a calc document in any of the open calc documents I have open. I have to force close Calc then reopen to edit any sheets.
EDIT: In fact even if I just open the doc with that macro in it, it freezes up my sheets.
EDIT 2: This may not be an issue with that code, I deleted that code and the sheet it was in and was still having an issue with creating a Data Validation list. I restarted my computer and no matter what sheet I open and try to create a data validation list, it locks all my calc sheets up.
EDIT3: I tried deleting the user profile but that did nothing except reset all my security settings. I ended up reinstalling LibreOffice to get it to work.
I got it to work now but had to reinstall LibreOffice.