Ask Your Question
1

How to get file names from a folder

asked 2016-11-05 12:37:49 +0100

FredSim gravatar image

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2016-11-05 20:50:44 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2016-11-05 21:19:47 +0100 )edit

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 gravatar imageJack2359 ( 2018-11-08 21:11:35 +0100 )edit
0

answered 2016-11-06 08:40:10 +0100

pierre-yves samyn gravatar image

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

edit flag offensive delete link more

Comments

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

rautamiekka gravatar imagerautamiekka ( 2016-11-07 15:51:56 +0100 )edit
0

answered 2018-11-08 21:14:18 +0100

Jack2359 gravatar image

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!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-05 12:37:49 +0100

Seen: 1,627 times

Last updated: Nov 08