VBA beginner requesting complex (?) help

[Jackkelly]

Hey,
First post here and I can usually find the help I need after some googling. Today I have failed since I could only partly find what I was looking for. First I tried power query and failed but then I ended up at VBA which most likely will fix the problem for me, but I just don't have the knowledge or brain power to manage.

It might be hard for me to explain this thoroughly so I might have to break it down in clear steps instead. I am trying to automatize work that we today do manually, i.e filling in a status report document (xlsm-file, will name as document 1) with results from test cases (other xlsm-files, will name as document X) which are either OK or NOK. The result is located in Cell "E8" in every test case document. The procedure I am looking for will look something like:

1. Open first folder and check for document X. If nothing exists continue to the next folder or subfolder and check for test result document. The folder will contain several sub-folders, and all sub-folders should be searched through.

2. Copy the result from cell "E8" in document X and paste it in a specific cell in document 1. What specific cell? Well, I will get to that (follow the star*)

3. When all documents are checked and document 1 is updated with all results from (all) document X, it shall
"MsgBox "Result from all files updated"" (this part was easy enough for me).


*Regarding what cell it shall be copied to. Let me give an example and see if this makes any sense. Document x is mentioned as Result Test Case 2_2019-06-13 and is located in folder 3. Document 1 will contain a long list of test case names, so if the name of document X exist in a row in document 1, then that is the row it shall be copied to. What column it will appear in I want to be able to control based on a folder-name.
Document 1[TABLE="width: 500"]
<tbody>[TR]
[TD]Test case[/TD]
[TD]Folder 1[/TD]
[TD]Folder 2[/TD]
[TD]Folder 3[/TD]
[/TR]
[TR]
[TD]Result Test Case 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result Test Case 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]NOK[/TD]
[/TR]
[TR]
[TD]Result Test Case 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I have figured out so far is how to open and close a document in a folder (not sub-folders) including saving it, but I am not completely sure on the syntax and I am having difficulty really getting into it. Below is what I have so far but it doesn't come close to what I am after. Is below usable or should I re-do it in an easier way?

Sub OpenAllWorkbooks()


    Dim MyFiles As String
    Dim Subfolder As String
    Dim ProvResultat As Characters
    
    MyFiles = Dir("C:\Folder1\*.xlsm")
    Do While MyFiles <> ""


    Workbooks.Open "C:\Folder1" & MyFiles


'run some code here, this is where I want to copy data from the newly opened excel file to the source-file
    
    MsgBox ActiveWorkbook.Name


    ActiveWorkbook.Close SaveChanges:=True




    MyFiles = Dir
    Loop




MsgBox "Result from all files updated"


End Sub

 

[Rijnsent]

Hi Jackkelly,
to chop your question up into smaller chuncks:
1) get a list of all XLSM files in the folder/subfolders, you could e.g. use this: https://stackoverflow.com/questions...a-given-folder-recursively-through-sub-folder or this one: https://stackoverflow.com/questions...hrough-subfolders-and-files-within-subfolders
2) with that list, loop through all files, and for every file, loop though the rows of your data to check if the file exists. If that file name exists in your shee, open the file, and take the value of E8.
Hope that helps, don't hesitate to post the next version of your code for more feedback.
Koen