The Christmas holiday is not a good time to expect a quick answer to a question since most answerers will be from American or Europe and will be busy with their families.
Your question includes statements that might be relevant to your solution but you give no indication that you have attempted to link those statements into a working macro. You also give no indication of what else you know. Do you know how to loop through a list of workbooks? Do you know how to loop through all the worksheets of a workbook? You give no indication of how this macro will be used. Are you adding to an existing summary or creating a new one? Is this macro to be used once, once a month, once a week or once a day? The frequency of use will significantly affect the best design for your macro.
This site exists for programmers to help one another develop; it is not a free coding service. Even if someone was willing to write your macro for you, your specification is too vague to allow them to do so.
This answer is an attempt to get you started.
You say you have many analysis Excel workbooks. If your macro is only to be used once or if a totally different set of workbooks are to be selected each time, GetOpenFilename may be suitable. However, this method is awkward as a way of selecting many files particularly if you have to use it frequently.
Is it possible to answer “Yes” to any of these questions?
- Are all the analysis workbooks in a single folder?
- Can all the analysis workbooks be moved to a single folder?
- Can one create a list of folders in which every workbook is an analysis workbook?
- Can you create a complete list of the workbooks to be summarised?
If you can answer “Yes” to questions 1 or 2 then macro Approach1 may give you a start. This macro creates a file containing the names of all the workbooks within the same folder as the workbook containing the macro except the workbook containing the macro.
This macro serves two purposes. Firstly it shows a technique that might meet your first requirement: to locate all the analysis files. Secondly it shows how to develop a macro when you are not sure how to write it. Before you can think about opening workbooks and copying selected data to a summary worksheet, you must locate the required workbooks. Run the macro and look at the contents of “Test.txt”. Does it contain a list of every analysis file and no other file? If it does, you have completed step one of your macro. If not, further development of the macro is needed. Can you, for example, adjust the file specification to get the list you require?
If you cannot answer “Yes” to questions 1 or 2 then macro Approach2 may be a better starting point. Here I have a worksheet named “Target Files” that lists folders and file specifications:
This paths and file specification are from my laptop but show the sort of file selections you can make. The text in column C are comments. In columns A and B I have folders and file specifications. This is a technique I find convenient when I want to pick up files scattered across several folders. Again I have output the files found to “Text.txt”. However, this time there are error messages such as “Folder does not exist”. You would need to adjust the list until there are no errors and every analysis file was listed.
These two macros should give you ideas for your first step. If neither is quite right, attempt to adjust the one that is nearest or use them as a source of ideas for your macro. Alternatively, perhaps every run of your macro will process a different list of files and GetOpenFilename is a better approach. Write a macro that uses GetOpenFilename to get some file names and output those names to a text file as I have. If you encounter difficulties come back with your faulty code and a clear description of what is not working. This is the type of question that will be answered quickly here.
Once you macro lists the correct analysis files, you will be ready for the next steps. I suggest the second version of your macro opens and closes each analysis file. For the third version I suggest listing the names of the worksheets from which you wish to extract data.
Developing your macro one step at a time is much easier than trying to achieve everything in one go.
Option Explicit
Sub Approach1()
' This macro assumes all analysis workbooks are in the same
' folder as the workbook containing this macro.
Dim FileName As String
Dim FileObj As Object
Dim FileSysObj As Object
Dim Path As String
Dim WbkThis As Workbook
Set WbkThis = ThisWorkbook ' The workbook containing this macro
Path = WbkThis.Path
Set FileSysObj = CreateObject("Scripting.FileSystemObject")
' True means overwrite existing file with same name
Set FileObj = FileSysObj.CreateTextFile(Path & "Test.txt", True)
FileName = Dir$(Path & "*.xl*")
Do While FileName <> "" And FileName <> WbkThis.Name
Call FileObj.WriteLine(FileName)
FileName = Dir$
Loop
FileObj.Close
End Sub
Sub Approach2()
' This macro uses the worksheet Target Files to
' specify the required analysis workbooks.
Const ColTgtPath As Long = 1
Const ColTgtFile As Long = 2
Dim AtLeastOneMatchingFileFound As Boolean
Dim FileName As String
Dim FileObj As Object
Dim FileSpec As String
Dim FileSysObj As Object
Dim Path As String
Dim RowTgtCrnt As Long
Dim WbkThis As Workbook
Set WbkThis = ThisWorkbook ' The workbook containing this macro
Set FileSysObj = CreateObject("Scripting.FileSystemObject")
' True means overwrite existing file with same name
Set FileObj = FileSysObj.CreateTextFile(WbkThis.Path & "Test.txt", True)
RowTgtCrnt = 2
Path = ""
Do While True
With WbkThis
With .Worksheets("Target Files")
If .Cells(RowTgtCrnt, ColTgtFile).Value = "" Then
' No file name so end of list
Exit Do
End If
FileSpec = .Cells(RowTgtCrnt, ColTgtFile).Value
If .Cells(RowTgtCrnt, ColTgtPath).Value <> "" Then
Path = .Cells(RowTgtCrnt, ColTgtPath).Value
If Right(Path, 1) <> "" Then
' Ensure path ends in ""
Path = Path & ""
End If
End If
End With
End With
If FileSysObj.FolderExists(Path) Then
' Have existing folder
FileName = Dir$(Path & FileSpec)
If FileName = "" Then
Call FileObj.WriteLine("No files matching specification " & _
FileSpec & " found in folder " & Path)
AtLeastOneMatchingFileFound = False
ElseIf FileName = WbkThis.Name And Path = WbkThis.Path & "" Then
' This workbook is the workbook containing this
' macro which cannot be an analysis file.
' Try for another matching file
FileName = Dir$
If FileName = "" Then
Call FileObj.WriteLine("No files matching specification " & _
FileSpec & " other than " & WbkThis.Name & _
" found in folder " & Path)
AtLeastOneMatchingFileFound = False
Else
' A matching file other than workbook containing macro found
AtLeastOneMatchingFileFound = True
End If
Else
' At least one acceptable file within folder matches specification
AtLeastOneMatchingFileFound = True
End If
If AtLeastOneMatchingFileFound Then
Do While FileName <> ""
Call FileObj.WriteLine(Path & FileName)
FileName = Dir$
Loop
End If
Else
Call FileObj.WriteLine("Folder " & Path & " does not exist")
End If
RowTgtCrnt = RowTgtCrnt + 1
Loop
FileObj.Close
End Sub
