importing file names from a directory

  • Thread starter Thread starter NoodNutt
  • Start date Start date
N

NoodNutt

G'day everyone

Some time ago, someone provided a code that allowed me to import file names
from a specific folder.

This code only copied the names of the files including the .ext directly
into a table.

I lost this code due to PC meltdown a while ago, and wouldn't you know it, I
have use for it again.

any assistance is appreciated

TIA
Mark.
 
G'day Allen

As usual, you are a mountain of valuable information.

Many thanks
Regards
Mark.

PS
Will this code work in Excel also...?
 
NoodNutt said:
Will this code work in Excel also...?
Click to expand...

Dunno. Try it and let us know.

At a glance, it would need some modification, e.g. Excel might not know how
to respond to:
SysCmd acSysCmdClearStatus
 
G'day Allen

It just so happens, someone else posted a similar question in one of the
Excel NG's.

Here is the Excel equivalent:

Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim destRng As Range
Dim oFSO As Object
Dim oFolder As Object
Dim ofile As Object
Dim sFolderName As String
Dim i As Long

Const sPath As String = _
"C:\Users\Norman\Documents" '<<==== CHANGE

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
Set destRng = SH.Range("B2") '<<==== CHANGE

Set oFSO = CreateObject("Scripting.FileSystemObject")
sFolderName = sPath & Application.PathSeparator

On Error Resume Next
Set oFolder = oFSO.GetFolder(sFolderName)
On Error GoTo XIT
If Not oFolder Is Nothing Then
For Each ofile In oFolder.Files
destRng.Offset(i).Value = ofile.Name
i = i + 1
Next ofile
End If

XIT:
Set ofile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing
End Sub

It may come in handy for you at a later date.

Regards
Mark.
 
Back
Top