Master excel creation

[Karthikeyanbt]

I have multiple .xlsm files in a folder. I want to compile data from a specific sheet from all the files and edit the data there which should update the respective source file in the folder.

I consolidated data using MS-Excel>Data>Get Data> From file> from folder option. I tried macros but in vein. I also tried Microsoft access but it showed This Recordset is not updatable. Expecting - Editable Masterdata for xlsm files.

 

[NdNoviceHlp]

Hi *Karthikeyanbt and Welcome to the Board! It's not clear what you're trying to achieve. Do you want to get data from a specific sheet from multiple XL files in a folder... OR... Do you want to edit/make changes to a specific sheet in multiple XL files in a folder... OR... something else? The code below outlines the general process to make changes to all XL files in a folder. This code is set to edit/change each file (it adds "Bart" to Sheet1 A1) and saves the changes. The folder name, folder path and sheet name needs to be adjusted to suit. HTH. Dave

Sub Test()
Dim objFSO As Object, PathFolder As String
Dim objFolder As Object, objFile As Object, sht As Worksheet
On Error GoTo Erfix
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'****adjust YourFolderName to suit path
PathFolder = "C:\YourFolderName\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(PathFolder)
For Each objFile In objFolder.Files
'access only XL files
If objFile.Name Like "*" & ".xls" & "*" Then
Workbooks.Open Filename:=objFile
For Each sht In Workbooks(objFile.Name).Worksheets
'only access specific sheet
'****adjust sheet name to suit
If LCase(sht.Name) = LCase("Sheet1") Then
'****make adjustment(s) to sheet as needed
Workbooks(objFile.Name).Sheets(sht.Name).Range("A1").Value = "Bart"
'save sheet changes
Workbooks(objFile.Name).Close savechanges:=True
Exit For
End If
Next sht
End If
Next objFile

Erfix:
If Err.Number <> 0 Then
MsgBox "Error"
On Error GoTo 0
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Set objFolder = Nothing
Set objFSO = Nothing
End Sub

 

[Karthikeyanbt]

@NdNoviceHlp
Sorry for not making it clear. Thanks for your effort.
To be more clear on my requirements,
Step 1: I want to get data from a specific sheet(''Product'') from multiple .xlsm files of a folder consolidated into a single sheet of a new excel (For review)
Step 2: If i find any observations or comments, i need to edit/make changes to the data there, which should update the source file in that folder.
The solution can be anything, a template with button or if a button to be created in ribbon.
Thank you in advance.

 

[NdNoviceHlp]

OK for Step 1, do you want to create a whole new XL wb to store the data or just store it in the wb that has the calling code? Also where is the data in the Product sheet and where do you want to put it in the consolidated sheet? Please be specific. What's the folder name? Provide all sheet names and row and column locations of data stored and placed.
Step 2 Where are you going to find the observations/comments? Edit the data with what from where? Update what source file? Usually you update a source file and then update any dependent files.
I think you should split this thread into the 2 steps outlined as they seem like they are going to require separate coding. Step 1 seems doable with minor modifications to the previously posted code. Please remember that members do not have your wb (unless you post it) and we're not psychic, so providing as much detailed information as possible not only helps you get a better answer but also prevents members from wasting their volunteer time. Also, if you're able, it helps to put some effort into solving your question yourself and then requesting assistance when you run into trouble. I will continue with assistance for resolving Step 1 and provide code comments which may help you move forward with Step 2. So for now, please just respond to my Step 1 inquires. Dave