|You will need to produce a function that 1) allows you to select a root folder, or you can specify this in your code, then 2) loops through all the files in that folder and then do something with them. The code below is just an example of how to browse to a folder, it will then print each of the filenames in the debug window.|
This can easily be modified to only select Excel files, by checking the extension of each filename, for example
if right(file,4) = ".xls"
if right(file,5) = ".xlsx" for the later versions of Excel
So this code will need to be modified to do what you want it to, you will need to open each file then, well do what you want with it.
Private Sub Command0_Click()
RootFolder = BrowseForFolder
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir(RootFolder & "\")
While (file <> "")
Debug.Print Right(file, 4)
file = Dir()
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
message edited by AlwaysWillingToLearn