I have about 1,000 data discs and I want to make a list of their contents. The main item would be to extract the folder names from each disc using a Macro in Excel. It is possible that may be a file mixed in with these folders (e.g. there are three folders and one file on the root disc). Does anyone have a way to easily extract these names? A side would be to also include any sub-folders and files, but not necessary.
Share
Hi,
This solution allows you to select files using the Open File dialog, from inside Excel.
All files selected are appended to any data already in Column A of Worksheet ‘Sheet1’
The Drive and Path are placed in column A
The filename is placed in Column B.
When you run the Macro again, the next selection of files is added to the existing paths/filenames.
Create a Workbook named Getfilename.xls
On Sheet1 create a Command Button from the Control Toolbox. If this isn’t visible, right click on an existing toolbar and check the Control Toolbox.
Select the button Icon and draw a button
Right-click the button and select Command Button – Edit and change the name to something useful !
Right-click the button again and select View Code
In the code window that opens enter this:
Note that Private Sub CommandButton1_Click() and End Sub will already be there, so don’t duplicate them
Option Explicit goes before Private Sub CommandButton1_Click()
Use Alt +f11 to return to the Excel Window. From the Control Toolbox toolbar select the Exit Design Mode icon, and close the toolbar.
The Command button will now run the macro.
This routine worked on hard drive partitions, and a CD drive and also worked on a USB connected floppy as well as on a network drive.
Regards