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]

When you have names that can change go for
1) parts that don't (in this case underscores, "Store" and "Ver")
2) patterns that exist in the names. Using Mid() for fixed length names, or RegEx for more complex stuff.

Some questions:
How fluent are you in VBA? Can you find the answer given the method or need help actually constructing the macro?
Do all files for all stores be in the same folder?
Do the store and version numbers have any prevailing zeros?
Do store names change or just named Store1, Store2, etc.?
What is the extension of files?


InStrRev for the underscore and Right(filename,len(filename)-InStrRev(filename,"_")+1) would give the number portion of the version.
Another method would be to Split with "_Ver_" which will result in an array with the store name and the version numbers (number would be a string at this moment).
Then for any store, if VAL(ver_no1) > VAL(ver_no2), it means ver_no1 is the latest.

Hope it makes sense. We can actually write the code when you provide more information.

 

[kevdragon1]

When you have names that can change go for
1) parts that don't (in this case underscores, "Store" and "Ver")
2) patterns that exist in the names. Using Mid() for fixed length names, or RegEx for more complex stuff.

Some questions:
How fluent are you in VBA? Can you find the answer given the method or need help actually constructing the macro?
Do all files for all stores be in the same folder?
Do the store and version numbers have any prevailing zeros?
Do store names change or just named Store1, Store2, etc.?
What is the extension of files?


InStrRev for the underscore and Right(filename,len(filename)-InStrRev(filename,"_")+1) would give the number portion of the version.
Another method would be to Split with "_Ver_" which will result in an array with the store name and the version numbers (number would be a string at this moment).
Then for any store, if VAL(ver_no1) > VAL(ver_no2), it means ver_no1 is the latest.

Hope it makes sense. We can actually write the code when you provide more information.

Thanks for your reply.

I would say I am a beginner-intermediate at VBA, started 1 year ago checking youtube videos.

I will put 2 files at a time in a folder, for each store.

The exact formatting is something like this : AXXA_TB_STORE1_IFRS_HL-Ver1_210913074750.xlsx.... The -Ver1_ is what I use for the version, nothing else.

The store names actually do change.

Extension is : .xlsx

What I currently have is pretty basic. I have a blank file that I use and a defined path. What I currently do is that I download the 2 reports and call them "last" and "prev" and then use this amateur macro I created:

    Windows("blank.xlsm").Activate
    Sheets("HL_last_v").Select
    Range("A1").Select
    Workbooks.Open "C:\XXX\HL\last.xlsx"
    Windows("last.xlsx").Activate
    Cells.Select
    Selection.Copy
    Windows("blank.xlsm").Activate
    Sheets("HL_last_v").Select
    ActiveSheet.Paste
    Windows("last.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close
    
    Windows("blank.xlsm").Activate
    Sheets("HL_prev_v").Select
    Range("A1").Select
    Workbooks.Open "C:\XXX\HL\prev.xlsx"
    Windows("prev.xlsx").Activate
    Cells.Select
    Selection.Copy
    Windows("blank.xlsm").Activate
    Sheets("HL_prev_v").Select
    ActiveSheet.Paste
    Windows("prev.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close

I am tired of changing the name of the files manually and wanted to know if there was a better way to do it with the version number.

 

[Gokhan Aycan]

Are the sheet names actually come as "HL_last_v" and "HL_prev_v" or do you change them as well? Are there any other sheets in the generated workbooks?

Edit: The sequence of numbers after the last underscore,do they mean anything?

 

[Gokhan Aycan]

And another question would be what are the default sheet names in the reports?

 

[Gokhan Aycan]

Don't mind my first question in post #4, mis-read the code.

This works on my test folder. Will work if there are only 1 sheet in the source files or if they are already the active sheet otherwise.

Change strFolder and strOutputFolder according to your setup. Output folder can be inside input folder.

Option Explicit

Public Sub Get_Files()
   
    ' Declare variables
    Dim fso As Object
    Dim oFolder As Object
    Dim oFile As Object
   
    Dim arr(1 To 2, 1 To 3) As Variant
    Dim FileVersion As Variant
   
    Dim strFolder As String
    Dim strOutputFolder As String
    Dim strFileName As String
   
    ' Assign folders
    strFolder = "D:\Test"
    strOutputFolder = "D:\Test\Output"
    strFileName = ""
   
    ' Create the File System Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(strFolder)
   
    ' Check if 2 files exist in the folder, goto endofsub if not
    If oFolder.Files.Count <> 2 Then GoTo endofsub
   
    ' Populate the array
    ' arr(#,1) = Store Name
    ' arr(#,2) = Version Number
    ' arr(#,3) = 1 if latest, 0 if previous
   
    Dim Counter As Integer
   
    Counter = 1
   
    For Each oFile In oFolder.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(1, 2) > arr(2, 2) Then
        ' First file is latest - arr(1,1)
        arr(1, 3) = 1
        arr(2, 3) = 0
    Else
        ' Second file is latest - arr(2,1)
        arr(2, 3) = 1
        arr(1, 3) = 0
    End If
   
    ' Declare the workbook variables
    Dim wb_Source As Workbook
    Dim wb_Destination As Workbook
   
    ' Add a blank workbook, set to wb_destination and minimize it
    Set wb_Destination = Workbooks.Add
    wb_Destination.Windows(1).WindowState = xlMinimized
   
    ' Turn off stuff to speed up execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    ' Iterate source files and copy to destination
    Dim i As Integer
   
    For i = 1 To 2
        ' Open source file and set to wb_Source
        Set wb_Source = Workbooks.Open(fso.BuildPath(oFolder, arr(i, 1)))
       
        ' Hide source file
        wb_Source.Windows(1).Visible = False
       
        ' Copy and rename the activesheet of source file
        If arr(i, 3) = 1 Then
            ' file is latest
            wb_Source.ActiveSheet.Copy after:=wb_Destination.Sheets(wb_Destination.Sheets.Count)
            wb_Destination.ActiveSheet.Name = "HL_Last_V"
           
        Else
            ' file is previous
            wb_Source.ActiveSheet.Copy after:=wb_Destination.Sheets(wb_Destination.Sheets.Count)
            wb_Destination.ActiveSheet.Name = "HL_Prev_V"
        End If
       
        ' Close source file discarding any changes
        wb_Source.Close SaveChanges:=False
       
    Next i
   
    ' Move HL_Last so it is always the first sheet
    wb_Destination.Sheets("HL_Last_V").Move Before:=wb_Destination.Sheets("HL_Prev_V")
   
    ' Delete the default sheet1 and save the STORE workbook
    Application.DisplayAlerts = False
   
    wb_Destination.Sheets(1).Delete
    wb_Destination.Close Filename:=fso.BuildPath(strOutputFolder, strFileName), SaveChanges:=True
   
    Application.DisplayAlerts = True
   
    ' Clean-up set variables
    Set wb_Source = Nothing
    Set wb_Destination = Nothing
   
endofsub:
   
    Set oFolder = Nothing
    Set fso = Nothing
   
    ' Turn stuff back on
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

 

[Marc L]

Hi, a VBA demonstration for starters to paste to a standard module :​

Sub Demo1()
  Const P = "D:\SourceFolder\"
    Dim F$, S%, Ws As Worksheet
        F = Dir$(P & "*.xlsx")
        Application.ScreenUpdating = False
    While F > "" And S < 2
        S = S + 1
    With Workbooks.Open(P & F, False)
        If S = 1 Then
           .ActiveSheet.Copy
            Set Ws = ActiveSheet
                Ws.Name = "Previous"
        Else
           .ActiveSheet.Copy , Ws
            ActiveSheet.Name = "Latest"
        End If
           .Close False
    End With
        F = Dir$
    Wend
        Application.ScreenUpdating = True
        Set Ws = Nothing
End Sub

 

[kevdragon1]

Don't mind my first question in post #4, mis-read the code.

This works on my test folder. Will work if there are only 1 sheet in the source files or if they are already the active sheet otherwise.

Change strFolder and strOutputFolder according to your setup. Output folder can be inside input folder.

Option Explicit

Public Sub Get_Files()
  
    ' Declare variables
    Dim fso As Object
    Dim oFolder As Object
    Dim oFile As Object
  
    Dim arr(1 To 2, 1 To 3) As Variant
    Dim FileVersion As Variant
  
    Dim strFolder As String
    Dim strOutputFolder As String
    Dim strFileName As String
  
    ' Assign folders
    strFolder = "D:\Test"
    strOutputFolder = "D:\Test\Output"
    strFileName = ""
  
    ' Create the File System Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(strFolder)
  
    ' Check if 2 files exist in the folder, goto endofsub if not
    If oFolder.Files.Count <> 2 Then GoTo endofsub
  
    ' Populate the array
    ' arr(#,1) = Store Name
    ' arr(#,2) = Version Number
    ' arr(#,3) = 1 if latest, 0 if previous
  
    Dim Counter As Integer
  
    Counter = 1
  
    For Each oFile In oFolder.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(1, 2) > arr(2, 2) Then
        ' First file is latest - arr(1,1)
        arr(1, 3) = 1
        arr(2, 3) = 0
    Else
        ' Second file is latest - arr(2,1)
        arr(2, 3) = 1
        arr(1, 3) = 0
    End If
  
    ' Declare the workbook variables
    Dim wb_Source As Workbook
    Dim wb_Destination As Workbook
  
    ' Add a blank workbook, set to wb_destination and minimize it
    Set wb_Destination = Workbooks.Add
    wb_Destination.Windows(1).WindowState = xlMinimized
  
    ' Turn off stuff to speed up execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
  
    ' Iterate source files and copy to destination
    Dim i As Integer
  
    For i = 1 To 2
        ' Open source file and set to wb_Source
        Set wb_Source = Workbooks.Open(fso.BuildPath(oFolder, arr(i, 1)))
      
        ' Hide source file
        wb_Source.Windows(1).Visible = False
      
        ' Copy and rename the activesheet of source file
        If arr(i, 3) = 1 Then
            ' file is latest
            wb_Source.ActiveSheet.Copy after:=wb_Destination.Sheets(wb_Destination.Sheets.Count)
            wb_Destination.ActiveSheet.Name = "HL_Last_V"
          
        Else
            ' file is previous
            wb_Source.ActiveSheet.Copy after:=wb_Destination.Sheets(wb_Destination.Sheets.Count)
            wb_Destination.ActiveSheet.Name = "HL_Prev_V"
        End If
      
        ' Close source file discarding any changes
        wb_Source.Close SaveChanges:=False
      
    Next i
  
    ' Move HL_Last so it is always the first sheet
    wb_Destination.Sheets("HL_Last_V").Move Before:=wb_Destination.Sheets("HL_Prev_V")
  
    ' Delete the default sheet1 and save the STORE workbook
    Application.DisplayAlerts = False
  
    wb_Destination.Sheets(1).Delete
    wb_Destination.Close Filename:=fso.BuildPath(strOutputFolder, strFileName), SaveChanges:=True
  
    Application.DisplayAlerts = True
  
    ' Clean-up set variables
    Set wb_Source = Nothing
    Set wb_Destination = Nothing
  
endofsub:
  
    Set oFolder = Nothing
    Set fso = Nothing
  
    ' Turn stuff back on
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  
End Sub

Hi, thank you so much for this. This is exactly what I needed. I learned so much from this. Arrays, split function, Scripting.FileSystemObject....I can't thank you enough. I struggled at the start because I did not have the Scripting.FileSystemObject activated in my reference lol so the code did not work. But now it does.

I have 2 questions :

1. In reality, I have 2 sets of 2 files that I need to compare, can I use your code twice for 2 different folder paths? I haven't tried it but I am pretty sure it will work. Let me explain myself: every store has a HL and a SL (head lease and sublease). So in reality something like this would happen:
   1. AXXA_TB_STORE1_IFRS_HL-Ver5_210913074750.xlsx
   2. AXXA_TB_STORE1_IFRS_HL-Ver3_210913074750.xlsx
   3. AXXA_TB_STORE1_IFRS_SL-Ver6_210913074750.xlsx
   4. AXXA_TB_STORE1_IFRS_SL-Ver2_210913074750.xlsx
      
      I would put the HL in the same folder and the SL in a different folder. Is there anything I need to watch out for in this case when changing your code?
      

2. ' Move HL_Last so it is always the first sheet
       wb_Destination.Sheets("HL_Last_V").Move Before:=wb_Destination.Sheets("HL_Prev_V")

   From my testing, this line is not working, the HL_prev_V is at the beginning of my file. Do you know why?

Thank you x 100 for this you helped me so much I hope I can become good like you one day

 

[Gokhan Aycan]

Hey,

If you add the reference to "Microsoft Scripting Runtime" then you can use "early binding" (another topic to research for you ), then you can get Intellisense while writing the code. The above is "late binding". Late binding is usually preferred if you share the workbooks with others. The variable declarations change accordingly. Best practice is to use early binding development and change to late binding before release.

1. Yes, that is possible. Best is to have another sub call this one with some arguments. I can modify it for such a task. However, would you prefer to have all files in 1 folder or seperate folders? Seperate folders is easier to implement at this moment but since you will use the macro many times we can modify once now.

How do you get the reports in the first place? Are they being dumped to 1 folder or to each to its own folder?
Would it be okay to have 1 output file per store with all sheets included?

2. I have tested changing the Version numbers and works here, so can't say exactly. If it was a sheet name issue, we should have gotten runtime error. Maybe someone else can spot something.

 

[kevdragon1]

Hey,

If you add the reference to "Microsoft Scripting Runtime" then you can use "early binding" (another topic to research for you ), then you can get Intellisense while writing the code. The above is "late binding". Late binding is usually preferred if you share the workbooks with others. The variable declarations change accordingly. Best practice is to use early binding development and change to late binding before release.

1. Yes, that is possible. Best is to have another sub call this one with some arguments. I can modify it for such a task. However, would you prefer to have all files in 1 folder or seperate folders? Seperate folders is easier to implement at this moment but since you will use the macro many times we can modify once now.

How do you get the reports in the first place? Are they being dumped to 1 folder or to each to its own folder?
Would it be okay to have 1 output file per store with all sheets included?

2. I have tested changing the Version numbers and works here, so can't say exactly. If it was a sheet name issue, we should have gotten runtime error. Maybe someone else can spot something.

I download the report manually there is no other way arround that part and they go in my download folder. I then will copy and paste them into 2 different input folders. Separate folders would be nice because it's easier as you said and it's more visual.

1 output file is the goal so the end result is the 4 sheets in 1 workbook HL prev, HL last, SL prev, SL last.

I forgot to add that sometimes a store won't have a SL. But every store has a HL. Idk if you can help with that also? So is there a way to make the SL optional so the macro will work even if there is nothing in the SL input file?