How to merge file along its filename

[Venz Kenworth De Chavez]

Hi Kindly help with my problem of my macro. i am trying to create a merging file that also copying the file name. The file is working but the problem is when it copies the 2nd file, the previous filename is being replaced. Below is my code:

Sub MergeFilesWithoutSpaces_working6()
    Dim path As String, ThisWB As String
    Dim wbDest As Workbook, shtdest As Worksheet
    Dim filename As String, wkb As Workbook
    Dim copyRng As Range, Dest As Range
    Dim M_rows As Integer, M_columns As Integer
    Dim lastR As Range, ws As Worksheet
  
    Dim iRow As Integer, iCol As Integer, splitFile As Variant, sFile As String
    Dim mergeObj As Object
  
ThisWB = ActiveWorkbook.Name


path = Worksheets("Control").txtPath.Value


Application.EnableEvents = False
Application.ScreenUpdating = False

Set shtdest = ActiveWorkbook.Sheets("Summary")
Set ws = ActiveWorkbook.Sheets("Summary")

filename = Dir(path & "\*.xls", vbNormal)
If Len(filename) = 0 Then Exit Sub
Do Until filename = vbNullString
    If Not filename = ThisWB Then
        Set wkb = Workbooks.Open(filename:=path & "\" & filename, UpdateLinks:=False)
      
  'previous code
  'month of June
   With wkb.Sheets("June")
    M_rows = 32767
    M_columns = 7
  
        Set copyRng = wkb.Worksheets("June").Range("A2").Resize(M_rows, M_columns)
        Set Dest = shtdest.Range("A" & shtdest.Cells(rows.Count, 1).End(xlUp).Row + 1)
        copyRng.Copy
        Dest.PasteSpecial xlPasteFormats
        Dest.PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False 'Clear Clipboard
        wkb.Close False
      
    'splitFile = Split(sFile, "-")
    'For iCol = 0 To UBound(splitFile)
        'Sheets("Summary").Range("G2").Cells(iRow, iCol + 1) = splitFile(iCol)
        Set lastR = ws.Range("G:G" & ws.Cells(rows.Count, 1)).End(xlUp)(2)
            With Range("G:G").CurrentRegion
            With .Resize(.rows.Count - 1).Offset(1)
            lastR(1, 1).Resize(.rows.Count).Value = CreateObject("Scripting.FileSystemObject").GetBasename(filename)
          End With
        End With
   'Next iCol
    End With

    Application.ScreenUpdating = True
End If
  
filename = Dir()
Loop

End Sub

MOD EDIT: ADDED CODE TAGS. PLEASE USE [ CODE ] TAGS TO WRAP YOUR CODE.

 

[Chihiro]

Well. You are rewriting entire range with each iteration of the loop in below section of your code.

      Set lastR = ws.Range("G:G" & ws.Cells(rows.Count, 1)).End(xlUp)(2)
            With Range("G:G").CurrentRegion
            With .Resize(.rows.Count - 1).Offset(1)
            lastR(1, 1).Resize(.rows.Count).Value = CreateObject("Scripting.FileSystemObject").GetBasename(filename)
          End With
        End With

You should set the range using "Dest" row and lastR row. That way, it's confined within the range the data was copied to.

Try something like...

Set lastR = ws.Range("G:G" & ws.Cells(rows.Count, 1)).End(xlUp)(2)
    With Range("G" & Dest.Row & ":G" & lastR.Row)
        .Value = CreateObject("Scripting.FileSystemObject").GetBasename(filename)
    End With

 

[Venz Kenworth De Chavez]

Hi Chihiro,

thank you for your prompt response, really appreciate it as I struggled on this project for months.

I tried the code and it seems I can only get 1 filename, what i need is to get all the filenames on each workbooks that the macro will going to open and assigned it on each rows.

Please sample below:



Thanks,

Venz

 

[GraH - Guido]

Hi Venz Kenworth De Chavez,

Alternatively, if you are using Excel 2010 or later, you can achieve this very simple with a Power Query solution (and this is valid in many situations where people tend to use VBA, I might add).

There is very good tutorial available on the YouTube channel from Mike Girvin. (google this: Mike Girvin + Power Query and in his list of videos look for Excel Power Query #06.)
You will see, it is all done via the user interface of PQ.

You might want to check that out.

Kr
Guido

 

[Chihiro]

Yes, I agree with Guido. PQ is the best tool for this sort of operation.

If this isn't an option and you still need help with the code. I suggest you upload sample workbooks that's representative of your source workbook and result.

It's much easier for us to pinpoint the issue when we have sample to work with.

 

[Venz Kenworth De Chavez]

Thanks Chihiro and Guido for helping me out!

 

[Venz Kenworth De Chavez]

While exploring PQ, i have attached the file i am working with.

Compilator Macro - this is the macro file i am currently developing. The code i am working is on module 6 (i am not yet applying all the changes on all modules because it is not yet meeting my requirement). Kindly copy paste the source files (the other two attached files) on a folder then get its url then paste it on "file path" (located at control sheet) of the macro file.

The other two files are the data source. The objective is to copy all the info on a specify sheet along with the filename.

 

[GraH - Guido]

Hi Venz Kenworth De Chavez,

To get you started with PQ, I've made an initial version. In the file I've also added some screenshots as guidelines.

The applied steps (all but 1 are made with the user interface):
- read from folder
- select filenames (in my example I have not filter out any, but you might need to add this step)
- get content from files (with a formula, it is in the guidelines)
- delete columns (this will happen a couple of times)
- extend table (to read the content of the sheets e.g.)
- filter sheets you need (JAN-DEC)
- extend table for those sheets
- filter rows you do not need
- load data and done

New files, new data? -> just refresh the PQ with a right click in the table made by the PQ.

I invite you to open the PQ and go trough all the steps (at the right side, and click on the stelp. The preview window show the intermediate result). Whenever you see a gearbox icon at the right of the step, click it to review more details of the applied step. PQ is very much like recording macros to automate jobs, but it is more robust.

To make this work at your side, you need to change the folder path of the source step. Look at the guidelines, it should be obvious for you I guess.

Hope this helps you out.
Enjoy PQ life.

Cheers
Guido

 

[Venz Kenworth De Chavez]

Perfect! Thanks Guido for the help