File names from folders into rows and columns

Hi, I have a question if it is possible to import file names with extensions from folders into Calc as follows:

  • I have a lot of folders that contain 1 or 2 images.

  • If the folder contains 1 image, I need it to be on a new line in Calc (classic way).

  • If the folder contains 2 images, the first being on a new line and the second either:

    – next to the first in the same row (in one column), separated by the character | (example: photo 1.jpeg | photo 2.jpeg)

    – or to have their names in the columns (even without the | character) (the first option would be better, but the second is enough as well)

These are the product images whose names I need for import into Woocommerce - via a csv file.
well thank you

MS Windows: Start a command line session: cmd.exe

cd <the base folder for your product folders>
dir /w /s > filelist.txt

Linux: open a terminal window

cd <the base folder for your product folders>
ls -R > filelist.txt

Insert content of filelist.txt into Calc.

You may need to use Data - Text to columns to reorganize content

Filter out the rows you don’t need.

How to use “Text to columns” to move “picture 02.jpeg” to the next column and the same row as “picture 01.jpeg”?

Well, try this:

Option Explicit 

Sub createFileList
Dim oFolderDialog As Object, oUcb As Object 
Dim sStartFolder As String 
Dim aList As Variant, aRes As Variant 
Dim nRow As Long, i As Long 
Dim sFullName As String, sExt As String, sFile As String, sPath As String, sFolder As String
Dim oDoc As Variant, oSheet As Variant
	sStartFolder = ""
	oFolderDialog = CreateUnoService("")
	oUcb = createUnoService("")
	If oUcb.Exists(GetPathSettings("Work")) Then oFolderDialog.SetDisplayDirectory(GetPathSettings("Work"))
	If oFolderDialog.Execute() = 1 Then
		sStartFolder = oFolderDialog.GetDirectory()
		If oUcb.Exists(sStartFolder) Then sStartFolder = ConvertFromUrl(sStartFolder)
	End If
	If sStartFolder = "" Then Exit Sub
	aList = ReadDirectories(sStartFolder, True, True, False)
	ReDim aRes(UBound(aList))
	nRow = -1
	sFolder = ""
	For i = LBound(aList) To UBound(aList)
		sFullName = ConvertFromURL(aList(i,0))
		sExt = LCase(GetFileNameExtension(sFullName))
		If sExt = "jpeg" Or sExt = "jpg" Or sExt = "png" Then 
			sFile = FileNameOutOfPath(sFullName, GetPathSeparator())
			sPath = DirectoryNameOutOfPath(sFullName, GetPathSeparator())
			If sPath = sFolder Then 
				aRes(nRow)(1) = aRes(nRow)(1) + "|" + sFile
				 sFolder = sPath
				 nRow = nRow + 1
				 aRes(nRow) = Array(sFolder, sFile) 
	Next i
	If nRow < 0 Then Exit Sub
	ReDim Preserve aRes(nRow)
	oDoc =  CreateNewDocument("scalc")
	oSheet = oDoc.getSheets().getByIndex(0)
End Sub


Hi, can you please modify that code to load only the first file from each folder into the list? well thank you

Sorry, no. But this is not necessary. Divide Column B with Text to Columns Separated by Other - | (pipe) and remove columns from C to the end of the data.