How to get file names from a folder

[FredSim]

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.

[Ratslinger]

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.

[Ratslinger]

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.

[Jack2359]

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.

[Jack2359]

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!

[PYS]

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…
• in the file c:\Tests\liste.txt

Regards

[rautamiekka]

I’d separate > with a space on both sides for clarity.