Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet and Directory Functions

    Can anyone give me an idea of how to Count the number of files in a directory and create the same number of worksheets with the corresponding filenames? Not the Directories, but just the filenames.

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet and Directory Functions

    The following is a longwinded way of doing what I think you want.
    The procedure firstly counts the files in the directory specified and then proceeds to obtain the file names and then inserts a sheet for each file name, with the sheet named with the file name.

    Its not perfect and am sure a more elegant method will present itself.
    <font color=448800>
    Sub GetFileNames()
    Dim FileName() As String
    Dim Directory As String
    Dim FileNumber As Integer

    On Error Resume Next

    Directory = InputBox("Enter Directory to use : ", "List of Files in Directory")

    If Directory <> "" Then
    If Right(Directory, 1) <> "" Then
    Directory = Directory & ""
    End If
    File = Dir(Directory, vbNormal)
    Do While File <> ""
    FileNumber = FileNumber + 1
    File = Dir
    Loop
    MsgBox "The Directory " & Directory & " holds " & FileNumber & " Files"
    ReDim FileName(FileNumber)
    FileName(1) = Dir(Directory, vbNormal)
    Application.ScreenUpdating = False
    For fName = 2 To FileNumber
    FileName(fName) = Dir
    Next
    For fName = 1 To FileNumber
    Worksheets.Add
    With ActiveSheet
    .Name = FileName(fName)
    End With
    Next
    Application.ScreenUpdating = True
    End If
    End Sub
    </font color=448800>


    Regards,

    Andrew C

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet and Directory Functions

    I have recoded the previous effort as follows :
    <font color=448800>
    Sub GetFiles()

    Dim FileName As String
    Dim Directory As String
    Dim FileNumber As Integer

    On Error Resume Next

    Directory = InputBox("Enter Directory to use : ", "List of Files in Directory")
    Application.ScreenUpdating = False

    If Directory <> "" Then

    If Right(Directory, 1) <> "" Then
    Directory = Directory & ""
    End If

    FileName = Dir(Directory, vbNormal)

    Do While FileName <> ""
    FileNumber = FileNumber + 1
    Worksheets.Add
    With ActiveSheet
    .Name = FileName
    FileName = Dir
    End With
    Loop

    MsgBox "The Directory " & Directory & " holds " & FileNumber & " Files"

    Application.ScreenUpdating = True

    End If
    End Sub
    </font color=448800>

    Neither have been tested with large directories. Furthermore I am not sure if all valid filenames would be valid sheetnames.

    Regards,

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •