A spreadsheet with the list of filenames in that folder

ECB

Board Regular
Joined
Mar 3, 2009
Messages
60
Office Version
  1. 365
Platform
  1. MacOS
  2. Mobile
Hi there,

Hope you are well.

I'm looking to gather information on the files in a folder.

This thread here is helpful:


What I would like to do is:

I have a folder on onedrive.

In that folder there are a combination of pdf and jpg files

The excel spreadsheet that I propose will reside in this same folder

In the spreadsheet I would like a list of all the filenames:

for example if the folder contains

1.pdf
2.pdf
3.pdf
4.pdf

I would like to output into the spreadsheet into cell

A1: 1.pdf
B1: 2.pdf
C1: 3.pdf
D1: 4.pdf

is this possible?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Maybe

VBA Code:
Sub ListAllFile()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add

    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"

    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.txt" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub
 
  • Like
Reactions: ECB
Upvote 0
Thanks for your help...

I'm getting an error message:

ActiveX component can't create object

Which I think might be unrelated to the Visual Basic.

I'm running Microsoft Excel for Mac Version 16.47.1 (21032301)

I'm looking into what I need to do to fix it.
 
Upvote 0
I'm running Microsoft Excel for Mac Version 16.47.1 (21032301)

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
  • Like
Reactions: ECB
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Thanks for the patience - and the helpful reply, I will do.
 
Upvote 0
Don't really know why it does not work for you. It works without error for me. I am on a Win 10 machine and running O365.
 
Upvote 0
You can't use "Scripting.FileSystemObject" on a Mac as it's ActiveX
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top