Posts by Mykail


    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

    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
    Set xlBook = Nothing
    Set xlApp = Nothing
    Set xlBook = ActiveWorkbook
    Set Sh = xlBook.Sheets("Hoja1")
    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!