Ask Your Question

File names from folders into rows and columns

asked 2020-06-30 12:59:08 +0200

temporalnaut gravatar image

updated 2020-06-30 13:01:30 +0200

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-07-01 13:03:01 +0200

JohnSUN gravatar image

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
edit flag offensive delete link more


THANK YOU!!!!!!!

temporalnaut gravatar imagetemporalnaut ( 2020-07-01 15:09:25 +0200 )edit

answered 2020-06-30 15:20:54 +0200

keme gravatar image

updated 2020-06-30 15:23:36 +0200

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.

edit flag offensive delete link more


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

temporalnaut gravatar imagetemporalnaut ( 2020-06-30 15:49:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-06-30 12:59:08 +0200

Seen: 41 times

Last updated: Jul 01