Hi all
I need to keep a list of all the files I have in different folder. How am I able to get the name of the file in to LibreOffice Calc beside manually copy individual file name and paste it in LibreOffice Calc.
Hi all
I need to keep a list of all the files I have in different folder. How am I able to get the name of the file in to LibreOffice Calc beside manually copy individual file name and paste it in LibreOffice Calc.
You can do this with a macro:
Sub ExampleDir
Dim oSheet As Object
Dim iCounter As Integer
Dim stFileName As String
Dim stPath As String
oSheet=ThisComponent.CurrentController.ActiveSheet
'Change MYPATH to the name of the directory where files are to be obtained'
'Directories within this path will not be listed'
stPath = CurDir & GetPathSeparator() & "MYPATH" & GetPathSeparator()
stFileName = Dir(stPath, 0)
'This For loop clears Col A rows 2 through 50'
For iCounter = 1 to 49
'Change here if Column,Row of files is different - iCounter is Row'
oSheet.getCellByPosition(0,iCounter).String = ""
Next iCounter
iCounter = 0
Do While (stFileName <> "")
iCounter = iCounter + 1
'File names will start in Col `A` (signified by 0) Row 2 (iCounter)'
oSheet.getCellByPosition(0,iCounter).String = stFileName
stFileName = Dir()
Loop
End Sub
You can attach the macro to a user created Toolbar item (or pushbutton if you really want). There are comments in the code for these items: MYPATH
needs to be changed to the path you want to list; the routine clears Col A, Rows 2 through 50 for the list of files; files Start listing in Col A starting in Row 2. These should be changed according to your needs.
Forgot to mention - this will create the list on the current sheet. First line of code ( oSheet = ) needs to change if this is to be used from a different sheet.
Hi
Thanks for the macro. I found that this part was putting unwanted text on the front of my path statement, making it not work:
CurDir & GetPathSeparator() &
The macro worked after I had removed that piece of code.
Also, my own requirement was to read-in MYPATH from a cell (in another tab) of the spreadsheet, not to have it hard-coded in the macro. This is my modification to do that, incase it is useful for anyone else.
sub GenerateFileListFromPathStatementInAnotherTab
Dim oDoc As Object, oSheet As Object, oCell As Object
Dim iCounter As Integer
Dim stFileName As String
Dim stPath As String
oDoc = ThisComponent
REM THIS PART READS THE PATH STATEMENT FROM CELL P16 IN THE TAB, 'NAME_OF_YOUR_SOURCETAB'
oSheet = oDoc.Sheets.getByName("NAME_OF_YOUR_SOURCETAB")
oCell=oSheet.getCellByposition (NthColumn,YthRow) 'REM (NthColumn,YthRow) IS THE LOCATION OF THE CELL CONTAINING THE PATH STATEMENT (e.g. (13,15) for cell N16)
REM oCell.getString () is the path statement (without the trailing \)
stPath = oCell.getString () & GetPathSeparator()
stFileName = Dir(stPath, 0)
'REM THIS PART SWITCHES TO THE TAB WHERE YOU WANT TO PUT THE RESULTS
oSheet = oDoc.Sheets.getByName("NAME_OF_YOUR_TARGET_TAB")
'This For loop clears Col A rows 2 through 50'
For iCounter = 1 to 49
'Change here if Column,Row of files is different - iCounter is Row'
oSheet.getCellByPosition(1,iCounter).String = ""
Next iCounter
REM THIS PART WRITES THE FILENAMES INTO THE SPREADSHEET
iCounter = 0
Do While (stFileName <> "")
iCounter = iCounter + 1
'File names will start in Col `B` (signified by 1) Row 2 (iCounter)'
oSheet.getCellByPosition(1,iCounter).String = stFileName
stFileName = Dir()
Loop
It might be quite unelegant - it is my first ever macro!
Hi
You can also get the list by an operating system command. For example, with Windows, you can start a “command prompt” and type the following statement:
dir>c:\Tests\liste.txt
dir
you get a list of the current directory>
the list is send…c:\Tests\liste.txt
Regards
I’d separate >
with a space on both sides for clarity.