Automatic update of excel spreadsheet

[mdensana]

Hi All!

I have an excel spreadsheet with kind of folder tree structure in it and what I need to do is to have it populated with names of the files from corresponding folders on a regular basis.

I have lots of different directories and it's quite time consuming so I was wondering if it's possible to create a macro that will extract all the filenames from certain folders after mapping folder names from the spreadsheet with actual folders and sub-folders in my directory. Ideally I will have the macro being able to only append the existing list of files in the spreadsheet and do that in different color (i.e red)

Is this possible and can anybody help me create the core of this macro?

Thanks for your good will!

Paul

[Dököll]

Anything is possible Paul, maybe difficult but possible... Do stay tuned! I will send over to Access for a closer look:-)

Hope you get this one pinned down. I would post a bit of anything if you have it just to give folks out there some idea what you're up against.

Good luck!

[ADezii]

Anything is possible Paul, maybe difficult but possible... Do stay tuned! I will send over to Access for a closer look:-)

Hope you get this one pinned down. I would post a bit of anything if you have it just to give folks out there some idea what you're up against.

Good luck!

Just check in, will return later.

[ChipR]

I've found the Dir Function useful. If you have questions about that, I will certainly do my best to help you learn.

[ADezii]

Hi All!

I have an excel spreadsheet with kind of folder tree structure in it and what I need to do is to have it populated with names of the files from corresponding folders on a regular basis.

I have lots of different directories and it's quite time consuming so I was wondering if it's possible to create a macro that will extract all the filenames from certain folders after mapping folder names from the spreadsheet with actual folders and sub-folders in my directory. Ideally I will have the macro being able to only append the existing list of files in the spreadsheet and do that in different color (i.e red)

Is this possible and can anybody help me create the core of this macro?

Thanks for your good will!

Paul

Hello mdensana, I'm a little confused as to what exactly you are requesting, and as to whether you wish to implement the functionality from within Access or Excel. In any event, I see 2 generic approaches to your problem.

• Link the Excel Spreadsheet to the Access Database, if you are going that route. I'm assuming that the Spreadsheet has a Column Header named Folder or something similar. Loop through every value (Folder Name) in that Column and pass it to a Function that will evaluate the Folder and process File Names contained within it if appropriate.
• The 2nd approach would be to parallel the functionality listed above, but use Automation Code to Open the Excel Spreadsheet then process the values in the Column.
• I'm still confused as to what you wish to do with the File Names for each Folder.
• I've written a Generic Function that should be a good starting point for you. Simply pass to this Function the Name of a Folder, and it will then return (Debug.Print) all Files contained within the Folder, or another appropriate Value.
• Let us know how this works out.
  

  Public Function fListFiles(strFolder As String) As String
  On Error GoTo Err_fListFiles
  Dim MyName As Variant
  
  strFolder = Trim(strFolder)
  
  'Was a Folder passed to the Function?
  If strFolder = "" Then
    fListFiles = "No Folder specified"
      Exit Function
  End If
  
  'We need a Trailing Backslash, if not present
  If Right(strFolder, 1) <> "\" Then
    strFolder = strFolder & "\"
  End If
  
  'Does the Folder even exist?
  If Dir(strFolder, vbDirectory) = "" Then
    fListFiles = "INVALID Folder"
      Exit Function
  End If
  
  '*********************************************************************
  'If we get here, we can retrieve all Files within the specified Folder
  MyName = Dir(strFolder)    ' Retrieve the first entry.
  
  Do While MyName <> ""       'Start the loop.
    'Not really sure what you want to do here!
    Debug.Print MyName
      MyName = Dir            'Get next entry.
  Loop
  '*********************************************************************
  
  Exit_Err_fListFiles:
    Exit Function
  
  Err_fListFiles:
    fListFiles = "ERROR in processing " & strFolder & "!"
    Resume Exit_Err_fListFiles
  End Function

[NeoPa]

Unless I'm misreading the history here, the only reason this Excel question is in this forum is that we are the closest match to the question.

This is an Excel question unrelated to Access, but we are the best people to help.

Right. That sorted, I think Chip has the right idea here (and probably ADezii too as far as the specifics of the code goes). However, a clearer representation of exactly what is required would be beneficial to all. Dir() can certainly help with this. Exactly what you require is essential before we can guide you further with any assurance.

[ADezii]

Unless I'm misreading the history here, the only reason this Excel question is in this forum is that we are the closest match to the question.

This is an Excel question unrelated to Access, but we are the best people to help.

Right. That sorted, I think Chip has the right idea here (and probably ADezii too as far as the specifics of the code goes). However, a clearer representation of exactly what is required would be beneficial to all. Dir() can certainly help with this. Exactly what you require is essential before we can guide you further with any assurance.

Did I miss another boat?

[NeoPa]

No worries. Your code is still the best suggestion so far ;)

I think we need a better explanation of the question before going any further though, as there is always the danger of wasting time going off in the wrong direction, which just confuses everything for later on.

[Guido Geurs]

dear,

This macro will add the files to the sheet.
In VBA you have to add the Reference = "Microsoft Scripting Runtime" !!
Select the cell with the path and run the macro.=>

Sub Macro_GetFiles()
'
' Macro recorded 21/11/2009 by Guido Geurs
'
'
Dim FOLDERNAME As String
Dim FSO As New Scripting.filesystemobject
Dim START_FOLDER As Scripting.Folder
Dim PATH_FILE As Scripting.File
   FOLDERNAME = ActiveCell.Value
   ActiveCell.Offset(1, 1).Activate
   On Error GoTo Error_FileName
   Set START_FOLDER = FSO.GetFolder(FOLDERNAME)
   For Each PATH_FILE In START_FOLDER.Files
      Selection.EntireRow.Insert
      ActiveCell.Value = Mid(PATH_FILE, InStrRev(PATH_FILE, "\") + 1)
      ActiveCell.Offset(1, 0).Activate
   Next
Exit Sub
Error_FileName:
   MsgBox ("Select a Drive or Folder")
End Sub

br,

[NeoPa]

There is another active thread (Table to include folders in a directory - Multilevel) with a very similar topic. You may find help in there for what you're trying to acieve.