Merge Two Files once importing them by filedialog and copy the result to another workbook

Question

Dears: i have an issue with below code as i need to Merge Two Files once importing them by filedialog then copy the result direclty to another workbook the below cope is working but suddnly it copies only data of the first file which its file size is the bigger and i do not know why

Sub IIII_Import_BSS_Stock_All_Files()


Dim ws As Worksheet
Dim wb As Workbook
Dim Imported1wb As Workbook
Dim Imported1ws As Worksheet
Dim DialFirstFile As FileDialog
Dim Imported1FileName As String
Dim Imported1LastRow As Long

Dim Imported2wb As Workbook
Dim Imported2ws As Worksheet
Dim DialSecondFile As FileDialog
Dim Imported2FileName As String
Dim Imported2LastRow As Long

'FileName after using File Len for ordering the first file to be (available) and the second one to be (Maintain)
Dim FileLenNameSize1 As Long
Dim FileLenNameSize2 As Long
Dim TempFileOrder As String

Dim UpperArray() As String
Dim LowerArray() As String
Dim split_len As Long

Dim Imported1FileNameLnSpFn As String
Dim Imported2FileNameLnSpFn As String

Dim DestinationRange As Range
Dim ImportSelectARange As Range
Dim ImportSelectBRange As Range
Const SelectCols As String = "D:D,A:A,C:C,H:H,K:K,I:I"


    Application.ScreenUpdating = False
    Set DialFirstFile = Application.FileDialog(msoFileDialogFilePicker)
        DialFirstFile.AllowMultiSelect = False
        DialFirstFile.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        DialFirstFile.Show
        Imported1FileName = DialFirstFile.SelectedItems.Item(1)

need.

    If InStr(Imported1FileName, ".xls") = 0 Then
        Exit Sub
    End If
    Set Imported1wb = Workbooks.Open(Imported1FileName)


    Application.ScreenUpdating = False
    Set DialSecondFile = Application.FileDialog(msoFileDialogFilePicker)
        DialSecondFile.AllowMultiSelect = False
        DialSecondFile.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        DialSecondFile.Show  
        Imported2FileName = DialSecondFile.SelectedItems.Item(1)

    If InStr(Imported2FileName, ".xls") = 0 Then
        Exit Sub
    End If
    Set Imported2wb = Workbooks.Open(Imported2FileName)

  Set ws = ActiveSheet
  FileLenNameSize1 = FileLen(Imported1FileName)
  FileLenNameSize2 = FileLen(Imported2FileName)

  If (FileLenNameSize1 < FileLenNameSize2) Then
      TempFileOrder = Imported1FileName
      Imported1FileName = Imported2FileName
      Imported2FileName = TempFileOrder
  End If
    
  UpperArray = Split(Imported1FileName, "\")
  LowerArray = Split(Imported2FileName, "\")
  split_len = UBound(UpperArray) - LBound(UpperArray)
  Imported1FileNameLnSpFn = UpperArray(split_len)
  Imported2FileNameLnSpFn = LowerArray(split_len)
   
  Set Imported1ws = Workbooks(Imported1FileNameLnSpFn).Worksheets("Default")
  Set Imported2ws = Workbooks(Imported2FileNameLnSpFn).Worksheets("Default")
  
  Imported1LastRow = Imported1ws.Cells(Imported1ws.Rows.Count, "A").End(xlUp).Offset(1).Row
  Imported2LastRow = Imported2ws.Cells(Imported2ws.Rows.Count, "A").End(xlUp).Row
    
  'Copy & Paste to the total stock sheet from the merged file
  ' but only Copy Selection of Non Adjacent Columns of the imported file not copying the entire sheet
  Set Imported1ws = Workbooks(Imported1FileNameLnSpFn).Worksheets("Default")
  'Selection of data in sheet 2 to be copied
  Set ImportSelectARange = Intersect(Imported1ws.Range(SelectCols), Imported1ws.Rows("2:" & Imported1LastRow))
  Set ImportSelectBRange = Intersect(Imported2ws.Range(SelectCols), Imported2ws.Rows("2:" & Imported2LastRow))
  'Selection of last empy row at Sheet 1 to be copy data into it
  
  'Copy from sheet 2 to sheet 1
  Set Destination2FRange = ThisWorkbook.Worksheets("Total Stock").Range("A2")
  Set Imported1ws = Workbooks(Imported1FileNameLnSpFn).Worksheets("Default")
  Imported2ws.Range("A2:L" & Imported2LastRow).Copy Destination:=Imported1ws.Cells(Imported1LastRow + 1, "A")

  'Copy from sheet 1 to sheet 2
  Set Imported1ws = Workbooks(Imported1FileNameLnSpFn).Worksheets("Default")
  Imported2ws.Range("A2:L" & Imported2LastRow).Copy Destination:=Imported1ws.Cells(Imported1LastRow + 1, "A")
  ImportSelectARange.Copy _
  Destination:=Destination2FRange
 
  Workbooks(Imported1FileNameLnSpFn).Close
  Workbooks(Imported2FileNameLnSpFn).Close

Answer 1

Please look at this part of your code.

  UpperArray = Split(Imported1FileName, "\")
  LowerArray = Split(Imported2FileName, "\")
  split_len = UBound(UpperArray) - LBound(UpperArray)
  Imported1FileNameLnSpFn = UpperArray(split_len)
  Imported2FileNameLnSpFn = LowerArray(split_len)

The first 2 lines create 2 arrays. They would have the same number of elements if the two files were taken from the same directory, like "C:\User\MyFile.xls". But if one of the files is from a sub-directory it would have more elements, like "C:\User\MyFolder\MyFile.xls". The third line of code examines this difference and assigns it to the variable split_len. We therefore know that split_len may contain 0, a positive or negative low number.

In the next 2 lines this number is used to define an element of the arrays first created. The chance that this will be a file name are remote because the file name is in the last element of each array. This code would extract it.

Imported1FileNameLnSpFn = UpperArray(UBound(UpperArray))
Imported2FileNameLnSpFn = LowerArray(UBound(LowerArray))

The variable split_Len is ill-conceived and not useful as an array index. It can only be sheer coincidence that it does work on occasion. On principle, you may improve your code if you don't handle the two files parallel. Instead, develop a sub routine that handles one file at a time, call it twice with different files name or file objects as argument after determining which file to handle first.

Answer 2

I'm not sure that abandoning FileDialog is a good idea. In fact, if your two workbooks are in the same file location you could open them in one go by allowing multiple selection in the file open dialog box. In the code below the presumption is that the files are in different folders. Therefore I made a loop to call a function that returns one workbook at a time.

Option Explicit

Sub MergeFiles()
    ' 174
    
    Dim Src(2)      As Workbook             ' sources
    Dim Title       As String
    Dim FolderPath  As String
    Dim f           As Integer              ' Loop counter
    
    ' set the arguments for the first loop
    Title = "Choose the first workbook to open"
        ' FolderPath can be a full file name (path & file name)
        ' or it can be just a folder name, ending on backslash
    FolderPath = "D:\PVT Archive\Class 1\1-2021 (Jan 2023)\"
    For f = 1 To 2
        Set Src(f) = FileToOpen(Title, FolderPath)
        If Src(f) Is Nothing Then Exit Sub  ' user made no selection
        
        ' now set the arguments for the second loop
        Title = "Choose the second workbook to open"
        FolderPath = Environ("UserProfile") & "\Desktop\"
    Next f
    
    Debug.Print Src(1).Name
    Debug.Print Src(2).Name
End Sub

Private Function FileToOpen(MyTitle As String, _
                            StartAt As String) As Workbook
    ' 174
    ' https://www.wallstreetmojo.com/vba-filedialog/

    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Title = MyTitle
        .AllowMultiSelect = False
        .InitialFileName = StartAt
        
        If .Show = -1 Then Set FileToOpen = Workbooks.Open(.SelectedItems(1))
    End With
End Function

At the end of this code you have your two files. Comparing their size is not so easy. You probably need to find a function on the internet. Better use the InitialFileName in the above code to open the bigger file first, or find a way to differentiate them by their content.

I couldn't figure out from your code (I think I see only a part of it) what you want to do with the two files but whatever it is it starts where my above code ends and it would clearly exceed the scope of this thread. The above code puts your logic on a slightly different path but you can try to append your previous code to it and then ask a new question if you need more help.