Greetings,
Sorry to register just to ask a question, this would be the first time I have to develop in VBA, so I'm not well versed at all.
Hopefully somebody can help me make something out of this c:
This is what was thrown at me:
A set of directories with multiple WorkBooks with varying names and either xlsx or xlsm extension. All of these have a Sheet in common with the same name but it doesn't always have the same position (name = "Impresion").
Currently I'm being asked to create a Master WorkBook that will do the following, to be exact:
-From all these WorkBooks in Sheet "Impresion", import all rows from (example range B9 to E9) until the cell before one with the value "Total" is found.
-Sum all duplicates (Headers are Item description, Quantity and Unit) while adding an extra column to the right in the resulting row specifying the subtotal added by each instance found, leaving a single row for each type of item with the detail composing it to it's right.
No need to make it fast, graceful or to not open the additional workbooks.
Currently, I've been able to Iterate through the folders and subfolders no problem with this (borrowed code, lost the link to credit it)
Public Sub Process_Orders()
Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject
Process_XLS_Files Fso, "C:\Orders"
Set Fso = Nothing
End Sub
Private Sub Process_XLS_Files(Fso As Scripting.FileSystemObject, folderPath As String)
Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
Dim wb As Workbook
Set Folder = Fso.GetFolder(folderPath)
Application.ScreenUpdating = False
For Each Subfolder In Folder.SubFolders
For Each File In Subfolder.Files
If InStr(File.Name, ".xlsx") Or InStr(File.Name, ".xlsm") Then
End If
Next
Next
Application.ScreenUpdating = True
End Sub
And I was able to hazaphardly import from a closed worksheet with this other snippet
Sub CopyDataFromClosedWbk()
'Credit to https://www.exceltrainingvideo…closed-workbook-with-vba/
'copy data from closed workbook to active workbook
Dim xlApp As Application
Dim xlBook As Workbook
Dim Sh As Object
Dim k As Long
Set xlApp = CreateObject("Excel.Application")
'Path source workbook
Set xlBook = xlApp.Workbooks.Open("C:\Orders\sub\test.xlsx")
xlBook.Sheets(1).Range("A1:C" & Range("A1").End(xlDown).Row).Copy
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set xlBook = ActiveWorkbook
Set Sh = xlBook.Sheets("Hoja1")
'Sh.Activate
Sh.Paste
End Sub
However, I'm having a hard time tuning what I've found online to the stated specific needs.
I haven't been able to tell the xlApp to stop at the actual first empty row rather than where the last written cell.
I don't know how to set the copyData sub to continue from where it left off to continue with the next WorkSheet (those range specifications are so alien to me)
When I get to adding the contents, I wonder how I'll be able to filter the way it's being requested alongside adding those subtotals to the right.
Thank you for reading, ANY help will be very welcome!