VBA to Open a file with variable name and version

[kevdragon1]

Hi,

I have an accounting software that produces reports. The software will name the excel reports for each store by their name and then add the version of each report. So for example, it will generate Store1_ver_1 today and tomorrow it will generate Store1_ver_2.

The goal of the macro will be to copy both versions for a store in a new excel file, in separate tabs. Version 2 would be in the tab called "Latest" and Version 1 would be in the tab called "Previous" of the same file.

I plan on creating a path on my computer and adding these 2 reports in that folder, run the macro, rinse and repeat for each store. I will be doing this for each store independently.

The problem is that, the version can be random example: version 6(latest) vs 4(previous). Another challenge is that the Store number can change, BUT at least the file names always end with "ver_x".

Can anyone help?

Thx

 

[Gokhan Aycan]

In case there is no SL, do you still get files for them or no files at all?

Maybe it's best to have a complete solution which won't make you do all the mundane tasks. When you get the reports do you get all files at once (maybe in a zip?) or have to do it for each store? We can create the folders and move the files, no need to manually copy paste etc. Just need to know the complete picture.

 

[kevdragon1]

In case there is no SL, do you still get files for them or no files at all?

Maybe it's best to have a complete solution which won't make you do all the mundane tasks. When you get the reports do you get all files at once (maybe in a zip?) or have to do it for each store? We can create the folders and move the files, no need to manually copy paste etc. Just need to know the complete picture.

In case there is no SL there is no file at all.

Unfortunately the mundane task of downloading and doing it store by store has to stay like this for now because we carefully review each store 1 by 1.

So after running the macro an analysis will have to be made by me or other colleagues. So really doing it store by store one at a time is the best for me right now. I don't mind moving 4 files in 2 folders each time.

 

[Gokhan Aycan]

Ok then, is this folder structure ok?

\STORE1_NAME
\STORE1_NAME\HL
\STORE1_NAME\SL
\STORE1_NAME\Output

 

[kevdragon1]

Ok then, is this folder structure ok?

\STORE1_NAME
\STORE1_NAME\HL
\STORE1_NAME\SL
\STORE1_NAME\Output

I was thinking of having 1 folder for the whole thing. I don't really need to keep the data once the output file is done. Something like this :
\HL
\SL
\Output

The idea is a get the 4 files, put them in the folders, run the macro, take the macro file save it somewhere else and analyze it, then delete the files in the HL and SL folders and repeat. No need to keep the reports once the macro is finished. Let me know if that makes sense. Should make everything easier.

 

[Gokhan Aycan]

Seems reasonable, the workbook containing the macro can reside in this folder as well. I am at the moment cooking some food, will be able to look at this in a few hours.

 

[Gokhan Aycan]

Sorry for late reply. I fell asleep after dinner last night

I modified the code a bit and just created a workbook for this. You can download from my GDrive - VBA Shared.

Remarks:
- You don't have to create the Output folder, it will be auto-created given HL and SL folders exist (either at file location or selected folder).
- I prepended an underscore before "Output" so it is always the first (or last if sorting is reversed) folder in File Explorer.
- Added some checks for files (number of files, file type, Hl or SL)
- Added a check whether store names for all files are the same
- The sheet sorting (HL before SL, and Last before Prev) does work here so if it doesn't for you idk

Also pasting the code here as per forum rules, it won't just work if copy pasted to another workbook due to usage of buttons, named range and sheet codename.

Option Explicit
Option Private Module

Dim InputFolder As String
Dim OutputFolder As String
Dim CurrentFolder As String
Dim wbDestination As Workbook
Dim fso As Object
Dim strFileName As String

Public Sub Select_Folder()
    
    '' Code taken from wellsr.com - https://wellsr.com/vba/2016/excel/vba-select-folder-with-msoFileDialogFolderPicker/
    
    Dim sFolder As String
    
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
        
        Settings.Range("StoreFolder").Value = sFolder
        
    End If
    
End Sub

Public Sub Create_Report()
    
'    Debug.Print "-------------"
'    Debug.Print "create report"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    InputFolder = IIf(Settings.Range("StoreFolder").Value = "", ThisWorkbook.Path, Settings.Range("StoreFolder").Value)
    OutputFolder = fso.BuildPath(InputFolder, "_Output")
    strFileName = ""
    
    ' Check whether HL and SL folders exist
    If Not (fso.FolderExists(fso.BuildPath(InputFolder, "HL")) And fso.FolderExists(fso.BuildPath(InputFolder, "SL"))) Then
        MsgBox "Either HL or SL, or both folders don't exist!", vbOKOnly, "Warning"
        GoTo endofsub
    End If
    
    ' Check whether Output folder exist - create if not
    If Not fso.FolderExists(OutputFolder) Then
        Debug.Print OutputFolder & " doesn't exist"
        fso.CreateFolder (OutputFolder)
    End If
    
    ' Check if 2 HL reports exist in the HL folder
    If Not Check_Folder("HL") Then GoTo endofsub
    
    ' Add a blank workbook, set to wb_destination and minimize it
    Set wbDestination = Workbooks.Add
    wbDestination.Windows(1).WindowState = xlMinimized
    
    ' Turn off stuff to speed up execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' Get the reports for HL
    Call Get_Reports("HL")
    
    ' Check if 2 SL reports exist in the SL folder, get the reports if so
    If Check_Folder("SL") Then Call Get_Reports("SL")
    
    ' Delete the default sheet1 and save the STORE workbook
    Application.DisplayAlerts = False
    
    wbDestination.Sheets(1).Delete
    wbDestination.Close Filename:=fso.BuildPath(OutputFolder, strFileName), SaveChanges:=True
    
    Application.DisplayAlerts = True
    
    ' Clean-up set variables
    Set wbDestination = Nothing
    
endofsub:
    
    Set fso = Nothing
    
    ' Turn stuff back on
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Private Function Check_Folder(ByVal ReportType As String) As Boolean
    
    Dim oFile As Object
    
    CurrentFolder = fso.BuildPath(InputFolder, ReportType)
    
    Check_Folder = True
    
    If fso.GetFolder(CurrentFolder).Files.Count <> 2 Then
        MsgBox "The " & ReportType & " folder doesn't have 2 files.", vbOKOnly, "Warning"
        Check_Folder = False
    Else
        For Each oFile In fso.GetFolder(CurrentFolder).Files
            ' Check whether store names are same
            If strFileName = "" Then
                strFileName = Split(oFile.Name, "_IFRS_")(0)
            Else
                If strFileName <> Split(oFile.Name, "_IFRS_")(0) Then
                    MsgBox "Store name anomaly detected in folder " & ReportType, vbOKOnly, "Warning"
                    Check_Folder = False
                End If
            End If
            
            ' Check if file is an Excel file
            If InStr("xlsx", fso.GetExtensionName(oFile.Path)) = 0 Then
                MsgBox oFile.Name & " in " & ReportType & " folder is not an Excel file", vbOKOnly, "Warning"
                Check_Folder = False
            End If
            
            ' Check if file is a HL file
            If InStr(oFile.Name, "_" & ReportType & "-") = 0 Then
                MsgBox oFile.Name & " in " & ReportType & " folder is not an " & ReportType & " report", vbOKOnly, "Warning"
                Check_Folder = False
            End If
        Next oFile
    End If
    
End Function

Private Sub Get_Reports(ByVal ReportType As String)

    'Debug.Print ReportType
    
    Dim arr(1 To 4, 1 To 3) As Variant
    
    ' Populate the array
    ' arr(#,1) = Store Name
    ' arr(#,2) = Version Number
    ' arr(#,3) = 1 if latest, 0 if previous
    
    Dim oFile As Object
    Dim FileVersion As Variant
    
    Dim InitialCounter As Integer
    Dim Counter As Integer
    
    InitialCounter = IIf(ReportType = "HL", 1, 3)
    Counter = InitialCounter
    
    For Each oFile In fso.GetFolder(CurrentFolder).Files
        
        FileVersion = Split(oFile.Name, "-Ver")(1)
        FileVersion = Val(Split(FileVersion, "_")(0))
        
        arr(Counter, 1) = oFile.Name
        arr(Counter, 2) = FileVersion
        
        If strFileName = "" Then strFileName = Split(oFile.Name, "_IFRS_")(0)
        
        Counter = Counter + 1
        
    Next oFile
    
    If arr(InitialCounter, 2) > arr(InitialCounter + 1, 2) Then
        ' First file is latest - arr(1,1)
        arr(InitialCounter, 3) = 1
        arr(InitialCounter + 1, 3) = 0
    Else
        ' Second file is latest - arr(2,1)
        arr(InitialCounter + 1, 3) = 1
        arr(InitialCounter, 3) = 0
    End If
    
    Dim wbSource As Workbook
    
    ' Iterate folders and copy to destination
    Dim i As Integer
    
    For i = InitialCounter To InitialCounter + 1
        ' Open source file and set to wb_Source
        Set wbSource = Workbooks.Open(fso.BuildPath(CurrentFolder, arr(i, 1)))
        
        ' Hide source file
        wbSource.Windows(1).Visible = False
        
        ' Copy and rename the activesheet of source file
        If arr(i, 3) = 1 Then
            ' file is latest
            wbSource.ActiveSheet.Copy after:=wbDestination.Sheets(wbDestination.Sheets.Count)
            wbDestination.ActiveSheet.Name = ReportType & "_Last_V"
            
        Else
            ' file is previous
            wbSource.ActiveSheet.Copy after:=wbDestination.Sheets(wbDestination.Sheets.Count)
            wbDestination.ActiveSheet.Name = ReportType & "_Prev_V"
        End If
        
        ' Close source file discarding any changes
        wbSource.Close SaveChanges:=False
        
    Next i
    
    ' Move HL_Last so it is always the first sheet
    wbDestination.Sheets(ReportType & "_Last_V").Move Before:=wbDestination.Sheets(ReportType & "_Prev_V")
    
    Set wbSource = Nothing
    
End Sub

 

[Gokhan Aycan]

Just in case, these are needed for the above code to work:

Insert a standard module and change name to "mdlStore"
Paste the code above to mdlStore.
Create a named range "StoreFolder"
Create a shape and assign macro "mdlStore.Create_Report"
Create another shape and assign macro "mdlStore.Select_Folder"

Folders "HL" and "SL", either where this workbook resides or the folder selected.

I hope I didn't miss anything.

 

[kevdragon1]

Sorry for late reply. I fell asleep after dinner last night

I modified the code a bit and just created a workbook for this. You can download from my GDrive - VBA Shared.

View attachment 47030

View attachment 47031
Remarks:
- You don't have to create the Output folder, it will be auto-created given HL and SL folders exist (either at file location or selected folder).
- I prepended an underscore before "Output" so it is always the first (or last if sorting is reversed) folder in File Explorer.
- Added some checks for files (number of files, file type, Hl or SL)
- Added a check whether store names for all files are the same
- The sheet sorting (HL before SL, and Last before Prev) does work here so if it doesn't for you idk

Also pasting the code here as per forum rules, it won't just work if copy pasted to another workbook due to usage of buttons, named range and sheet codename.

Option Explicit
Option Private Module

Dim InputFolder As String
Dim OutputFolder As String
Dim CurrentFolder As String
Dim wbDestination As Workbook
Dim fso As Object
Dim strFileName As String

Public Sub Select_Folder()
   
    '' Code taken from wellsr.com - https://wellsr.com/vba/2016/excel/vba-select-folder-with-msoFileDialogFolderPicker/
   
    Dim sFolder As String
   
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
   
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
       
        Settings.Range("StoreFolder").Value = sFolder
       
    End If
   
End Sub

Public Sub Create_Report()
   
'    Debug.Print "-------------"
'    Debug.Print "create report"
   
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    InputFolder = IIf(Settings.Range("StoreFolder").Value = "", ThisWorkbook.Path, Settings.Range("StoreFolder").Value)
    OutputFolder = fso.BuildPath(InputFolder, "_Output")
    strFileName = ""
   
    ' Check whether HL and SL folders exist
    If Not (fso.FolderExists(fso.BuildPath(InputFolder, "HL")) And fso.FolderExists(fso.BuildPath(InputFolder, "SL"))) Then
        MsgBox "Either HL or SL, or both folders don't exist!", vbOKOnly, "Warning"
        GoTo endofsub
    End If
   
    ' Check whether Output folder exist - create if not
    If Not fso.FolderExists(OutputFolder) Then
        Debug.Print OutputFolder & " doesn't exist"
        fso.CreateFolder (OutputFolder)
    End If
   
    ' Check if 2 HL reports exist in the HL folder
    If Not Check_Folder("HL") Then GoTo endofsub
   
    ' Add a blank workbook, set to wb_destination and minimize it
    Set wbDestination = Workbooks.Add
    wbDestination.Windows(1).WindowState = xlMinimized
   
    ' Turn off stuff to speed up execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    ' Get the reports for HL
    Call Get_Reports("HL")
   
    ' Check if 2 SL reports exist in the SL folder, get the reports if so
    If Check_Folder("SL") Then Call Get_Reports("SL")
   
    ' Delete the default sheet1 and save the STORE workbook
    Application.DisplayAlerts = False
   
    wbDestination.Sheets(1).Delete
    wbDestination.Close Filename:=fso.BuildPath(OutputFolder, strFileName), SaveChanges:=True
   
    Application.DisplayAlerts = True
   
    ' Clean-up set variables
    Set wbDestination = Nothing
   
endofsub:
   
    Set fso = Nothing
   
    ' Turn stuff back on
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

Private Function Check_Folder(ByVal ReportType As String) As Boolean
   
    Dim oFile As Object
   
    CurrentFolder = fso.BuildPath(InputFolder, ReportType)
   
    Check_Folder = True
   
    If fso.GetFolder(CurrentFolder).Files.Count <> 2 Then
        MsgBox "The " & ReportType & " folder doesn't have 2 files.", vbOKOnly, "Warning"
        Check_Folder = False
    Else
        For Each oFile In fso.GetFolder(CurrentFolder).Files
            ' Check whether store names are same
            If strFileName = "" Then
                strFileName = Split(oFile.Name, "_IFRS_")(0)
            Else
                If strFileName <> Split(oFile.Name, "_IFRS_")(0) Then
                    MsgBox "Store name anomaly detected in folder " & ReportType, vbOKOnly, "Warning"
                    Check_Folder = False
                End If
            End If
           
            ' Check if file is an Excel file
            If InStr("xlsx", fso.GetExtensionName(oFile.Path)) = 0 Then
                MsgBox oFile.Name & " in " & ReportType & " folder is not an Excel file", vbOKOnly, "Warning"
                Check_Folder = False
            End If
           
            ' Check if file is a HL file
            If InStr(oFile.Name, "_" & ReportType & "-") = 0 Then
                MsgBox oFile.Name & " in " & ReportType & " folder is not an " & ReportType & " report", vbOKOnly, "Warning"
                Check_Folder = False
            End If
        Next oFile
    End If
   
End Function

Private Sub Get_Reports(ByVal ReportType As String)

    'Debug.Print ReportType
   
    Dim arr(1 To 4, 1 To 3) As Variant
   
    ' Populate the array
    ' arr(#,1) = Store Name
    ' arr(#,2) = Version Number
    ' arr(#,3) = 1 if latest, 0 if previous
   
    Dim oFile As Object
    Dim FileVersion As Variant
   
    Dim InitialCounter As Integer
    Dim Counter As Integer
   
    InitialCounter = IIf(ReportType = "HL", 1, 3)
    Counter = InitialCounter
   
    For Each oFile In fso.GetFolder(CurrentFolder).Files
       
        FileVersion = Split(oFile.Name, "-Ver")(1)
        FileVersion = Val(Split(FileVersion, "_")(0))
       
        arr(Counter, 1) = oFile.Name
        arr(Counter, 2) = FileVersion
       
        If strFileName = "" Then strFileName = Split(oFile.Name, "_IFRS_")(0)
       
        Counter = Counter + 1
       
    Next oFile
   
    If arr(InitialCounter, 2) > arr(InitialCounter + 1, 2) Then
        ' First file is latest - arr(1,1)
        arr(InitialCounter, 3) = 1
        arr(InitialCounter + 1, 3) = 0
    Else
        ' Second file is latest - arr(2,1)
        arr(InitialCounter + 1, 3) = 1
        arr(InitialCounter, 3) = 0
    End If
   
    Dim wbSource As Workbook
   
    ' Iterate folders and copy to destination
    Dim i As Integer
   
    For i = InitialCounter To InitialCounter + 1
        ' Open source file and set to wb_Source
        Set wbSource = Workbooks.Open(fso.BuildPath(CurrentFolder, arr(i, 1)))
       
        ' Hide source file
        wbSource.Windows(1).Visible = False
       
        ' Copy and rename the activesheet of source file
        If arr(i, 3) = 1 Then
            ' file is latest
            wbSource.ActiveSheet.Copy after:=wbDestination.Sheets(wbDestination.Sheets.Count)
            wbDestination.ActiveSheet.Name = ReportType & "_Last_V"
           
        Else
            ' file is previous
            wbSource.ActiveSheet.Copy after:=wbDestination.Sheets(wbDestination.Sheets.Count)
            wbDestination.ActiveSheet.Name = ReportType & "_Prev_V"
        End If
       
        ' Close source file discarding any changes
        wbSource.Close SaveChanges:=False
       
    Next i
   
    ' Move HL_Last so it is always the first sheet
    wbDestination.Sheets(ReportType & "_Last_V").Move Before:=wbDestination.Sheets(ReportType & "_Prev_V")
   
    Set wbSource = Nothing
   
End Sub

Thanks a lot for this. I really am so grateful .

I will let you know if I have any issues but for now, it's exactly what I needed!

 

[kevdragon1]

Sorry for late reply. I fell asleep after dinner last night

I modified the code a bit and just created a workbook for this. You can download from my GDrive - VBA Shared.

View attachment 47030

View attachment 47031
Remarks:
- You don't have to create the Output folder, it will be auto-created given HL and SL folders exist (either at file location or selected folder).
- I prepended an underscore before "Output" so it is always the first (or last if sorting is reversed) folder in File Explorer.
- Added some checks for files (number of files, file type, Hl or SL)
- Added a check whether store names for all files are the same
- The sheet sorting (HL before SL, and Last before Prev) does work here so if it doesn't for you idk

Also pasting the code here as per forum rules, it won't just work if copy pasted to another workbook due to usage of buttons, named range and sheet codename.

Option Explicit
Option Private Module

Dim InputFolder As String
Dim OutputFolder As String
Dim CurrentFolder As String
Dim wbDestination As Workbook
Dim fso As Object
Dim strFileName As String

Public Sub Select_Folder()
   
    '' Code taken from wellsr.com - https://wellsr.com/vba/2016/excel/vba-select-folder-with-msoFileDialogFolderPicker/
   
    Dim sFolder As String
   
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
   
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
       
        Settings.Range("StoreFolder").Value = sFolder
       
    End If
   
End Sub

Public Sub Create_Report()
   
'    Debug.Print "-------------"
'    Debug.Print "create report"
   
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    InputFolder = IIf(Settings.Range("StoreFolder").Value = "", ThisWorkbook.Path, Settings.Range("StoreFolder").Value)
    OutputFolder = fso.BuildPath(InputFolder, "_Output")
    strFileName = ""
   
    ' Check whether HL and SL folders exist
    If Not (fso.FolderExists(fso.BuildPath(InputFolder, "HL")) And fso.FolderExists(fso.BuildPath(InputFolder, "SL"))) Then
        MsgBox "Either HL or SL, or both folders don't exist!", vbOKOnly, "Warning"
        GoTo endofsub
    End If
   
    ' Check whether Output folder exist - create if not
    If Not fso.FolderExists(OutputFolder) Then
        Debug.Print OutputFolder & " doesn't exist"
        fso.CreateFolder (OutputFolder)
    End If
   
    ' Check if 2 HL reports exist in the HL folder
    If Not Check_Folder("HL") Then GoTo endofsub
   
    ' Add a blank workbook, set to wb_destination and minimize it
    Set wbDestination = Workbooks.Add
    wbDestination.Windows(1).WindowState = xlMinimized
   
    ' Turn off stuff to speed up execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    ' Get the reports for HL
    Call Get_Reports("HL")
   
    ' Check if 2 SL reports exist in the SL folder, get the reports if so
    If Check_Folder("SL") Then Call Get_Reports("SL")
   
    ' Delete the default sheet1 and save the STORE workbook
    Application.DisplayAlerts = False
   
    wbDestination.Sheets(1).Delete
    wbDestination.Close Filename:=fso.BuildPath(OutputFolder, strFileName), SaveChanges:=True
   
    Application.DisplayAlerts = True
   
    ' Clean-up set variables
    Set wbDestination = Nothing
   
endofsub:
   
    Set fso = Nothing
   
    ' Turn stuff back on
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

Private Function Check_Folder(ByVal ReportType As String) As Boolean
   
    Dim oFile As Object
   
    CurrentFolder = fso.BuildPath(InputFolder, ReportType)
   
    Check_Folder = True
   
    If fso.GetFolder(CurrentFolder).Files.Count <> 2 Then
        MsgBox "The " & ReportType & " folder doesn't have 2 files.", vbOKOnly, "Warning"
        Check_Folder = False
    Else
        For Each oFile In fso.GetFolder(CurrentFolder).Files
            ' Check whether store names are same
            If strFileName = "" Then
                strFileName = Split(oFile.Name, "_IFRS_")(0)
            Else
                If strFileName <> Split(oFile.Name, "_IFRS_")(0) Then
                    MsgBox "Store name anomaly detected in folder " & ReportType, vbOKOnly, "Warning"
                    Check_Folder = False
                End If
            End If
           
            ' Check if file is an Excel file
            If InStr("xlsx", fso.GetExtensionName(oFile.Path)) = 0 Then
                MsgBox oFile.Name & " in " & ReportType & " folder is not an Excel file", vbOKOnly, "Warning"
                Check_Folder = False
            End If
           
            ' Check if file is a HL file
            If InStr(oFile.Name, "_" & ReportType & "-") = 0 Then
                MsgBox oFile.Name & " in " & ReportType & " folder is not an " & ReportType & " report", vbOKOnly, "Warning"
                Check_Folder = False
            End If
        Next oFile
    End If
   
End Function

Private Sub Get_Reports(ByVal ReportType As String)

    'Debug.Print ReportType
   
    Dim arr(1 To 4, 1 To 3) As Variant
   
    ' Populate the array
    ' arr(#,1) = Store Name
    ' arr(#,2) = Version Number
    ' arr(#,3) = 1 if latest, 0 if previous
   
    Dim oFile As Object
    Dim FileVersion As Variant
   
    Dim InitialCounter As Integer
    Dim Counter As Integer
   
    InitialCounter = IIf(ReportType = "HL", 1, 3)
    Counter = InitialCounter
   
    For Each oFile In fso.GetFolder(CurrentFolder).Files
       
        FileVersion = Split(oFile.Name, "-Ver")(1)
        FileVersion = Val(Split(FileVersion, "_")(0))
       
        arr(Counter, 1) = oFile.Name
        arr(Counter, 2) = FileVersion
       
        If strFileName = "" Then strFileName = Split(oFile.Name, "_IFRS_")(0)
       
        Counter = Counter + 1
       
    Next oFile
   
    If arr(InitialCounter, 2) > arr(InitialCounter + 1, 2) Then
        ' First file is latest - arr(1,1)
        arr(InitialCounter, 3) = 1
        arr(InitialCounter + 1, 3) = 0
    Else
        ' Second file is latest - arr(2,1)
        arr(InitialCounter + 1, 3) = 1
        arr(InitialCounter, 3) = 0
    End If
   
    Dim wbSource As Workbook
   
    ' Iterate folders and copy to destination
    Dim i As Integer
   
    For i = InitialCounter To InitialCounter + 1
        ' Open source file and set to wb_Source
        Set wbSource = Workbooks.Open(fso.BuildPath(CurrentFolder, arr(i, 1)))
       
        ' Hide source file
        wbSource.Windows(1).Visible = False
       
        ' Copy and rename the activesheet of source file
        If arr(i, 3) = 1 Then
            ' file is latest
            wbSource.ActiveSheet.Copy after:=wbDestination.Sheets(wbDestination.Sheets.Count)
            wbDestination.ActiveSheet.Name = ReportType & "_Last_V"
           
        Else
            ' file is previous
            wbSource.ActiveSheet.Copy after:=wbDestination.Sheets(wbDestination.Sheets.Count)
            wbDestination.ActiveSheet.Name = ReportType & "_Prev_V"
        End If
       
        ' Close source file discarding any changes
        wbSource.Close SaveChanges:=False
       
    Next i
   
    ' Move HL_Last so it is always the first sheet
    wbDestination.Sheets(ReportType & "_Last_V").Move Before:=wbDestination.Sheets(ReportType & "_Prev_V")
   
    Set wbSource = Nothing
   
End Sub

Hey Gokhan,
After all this code I want to add my own subroutines but I am getting the error : Invalid property Value when I run it. Do you know why?

I created a subroutine called Sub GetX (), then I am calling this sub after this line of the code :

 Get the reports for HL
    Call Get_Reports("HL")

Do you know why it's not working by chance?

 

[kevdragon1]

Please ignore everything is working