Files Name

[mr moe]

HI I hope that someone can help me, I know it's not that hard, but I have never done it. Actually, I have a folder that has like 340 excel files. Is there a way I can write a vba code in either excel or access to get the file names in like a table or so, it's gonna take for ever to type them in manually. I only need to get the file name and they are all excel files. Please Help If You Can. Thanks.

 

[ghudson]

Searching the forum is a great way to discover and learn the answers to your questions.

My Browse [Find a directory or file] sample has what you need.

 

[mr moe]

I tried the link but it didn't really help me. I'm trying to store all the file names in a table. Like I said I have a folder with almost 250 excel files and I need to store the file names in a table. I will keep trying!!! thanks.

 

[Bat17]

here some code from something simmilar that I did earlier. Shout if you need help to work it through.

Option Compare Database
Option Explicit

'Using the FSO requires setting a reference to "Microsoft Scripting Runtime".

'Module-Level Variables/Constants
Private fso As Scripting.FileSystemObject
'Change the following constants to the appropriate folder names
Const cstrXLSpath As String = "\\Murph\home$\saunderp\border"

'This sub gets the whole process started...
Sub UpdateMsdsHyperlinks()
Dim FolderXLS As Scripting.Folder
Dim errorMes As Variant
'On Error GoTo Link_Error
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDelXLFiles")
DoCmd.SetWarnings True
Set fso = CreateObject("Scripting.FileSystemObject")
Set FolderXLS = fso.GetFolder(cstrXLSpath)
funFindXLS FolderXLS
Links_Exit:
Exit Sub
Link_Error:  ' Error-handling routine.
   Select Case Err.Number  ' Evaluate error number.
      Case 3109  ' no permision to delete records.
        GoTo Links_Exit
      Case Else
        errorMes = MsgBox("Error Number " & Err.Number, vbCritical, "Code Error")
        GoTo Links_Exit
   End Select
End Sub

'This sub does all the work
Private Sub funFindXLS(FolderXLS As Scripting.Folder)
Dim SubFolderXLS As Scripting.Folder
Dim oFile As Scripting.File
Dim strFile As String
Dim dbs As Database
Dim rstXLS As Recordset

Set dbs = CurrentDb
Set rstXLS = dbs.OpenRecordset("tblXLfiles")
With rstXLS
   'For each subfolder in the main folder, run this sub recursively
   If FolderXLS.SubFolders.Count Then
        For Each SubFolderXLS In FolderXLS.SubFolders
            funFindXLS SubFolderXLS
        Next 'SubFolderXLS
   End If
   
   'scan each file in the folder and to see if it is an XLS
   For Each oFile In FolderXLS.Files
        strFile = UCase$(oFile.Name)
        If Right(strFile, 3) = "XLS" Then
            'add file here
                  .AddNew
                  !filepath = oFile.Path
                  .Update
         End If
   Next 'oFile
   .Close
End With
Set rstXLS = Nothing
Set dbs = Nothing
End Sub

HTH

Peter

 

[mr moe]

Hi Bat17 thanks for your help. But you please give me some more assitance. Where do I call the sub. I mean I copeid everything, then i created a button on the form and after that onlick event I typed call funFindXLS, but the i get a messae argument not optional. Please help me.

 

[mr moe]

I got it thanks for all your help.

 

[ghudson]

I tried the link but it didn't really help me. I'm trying to store all the file names in a table. Like I said I have a folder with almost 250 excel files and I need to store the file names in a table. I will keep trying!!! thanks.

Glad you got it sorted with Bat17s code but I am not quite sure you understood what you were looking at in my sample. In my Browse [Find a directory or file] sample file the "fScanDirSelectFileFromTable" form will automatically populate the "tFiles" table with all the details [FilePathName, FilePath, FileName, ModifiedDate, FileSize] of each file found in a specific directory. All you had to do was change the sPath value in the FilesAndDetails() function in the "fScanDirSelectFileFromTable" form to the location [directory path] where you Excel files were located.