VBA Open Last Saved File in Group

[amandakay740]

My folder structure is set up like the below:

2020_05_Group1_v1
2020_05_Group1_v2
2020_05_Group1_v3
2020_05_Group1_v4
2020_06_Group1_v1
2020_06_Group1_v2
2020_05_Group2_v1
2020_05_Group2_v2
2020_05_Group2_v3
2020_06_Group2_v1
2020_06_Group2_v2
2020_06_Group1_v3

The amount of versions by group varies, I would like to have the Open Formula find the latest version in the group, for example:
In May, it would open V4 for Group 1 and V3 for Group 2
In June, it would open V2 for Group 1 and V3 for Group 2

I have 13 groups and am manually resaving them in a subfolder and using this formula to open them, it works but I know there is a better way that is beyond my current skillset. Again, the open code works but I currently use find and replace to change the date and am sub saving the workbooks into a non-version controlled naming convention.

Set x = Workbooks.Open("\\C:Desktop\2020\Group1_2020_05.xlsm", ReadOnly:=True)

 

[yky]

In your workbooks.open code, you have file path and name as "C:Desktop\2020\Group1_2020_05.xlsm". In the beginning of your post, you said you have folders like "2020_05_Group1_v4". So, the file doesn't reside in the folder? And where is the version number? What file are you opening anyway? If the file indeed resides in a folder, I'd expect to see something like this: "C:Desktop\2020\Group1_2020_05\2020_05_Group1_v4.xlsm", or better yet, "C:Desktop\2020\2020_05_Group1\2020_05_Group1_v4.xlsm", or maybe just "C:Desktop\2020\2020_05_Group1_v4.xlsm".

 

[amandakay740]

My apologies, I should have been more clear the file structure is:

"C:Desktop\2020"

Within that folder are 13 groups of files like this:

2020_05_Group1_v1
2020_05_Group1_v2
2020_05_Group1_v3
2020_05_Group1_v4
2020_06_Group1_v1
2020_06_Group1_v2
2020_05_Group2_v1
2020_05_Group2_v2
2020_05_Group2_v3
2020_06_Group2_v1
2020_06_Group2_v2
2020_06_Group1_v3

When I run monthly reporting I would like the code to aggregate data from the "last saved" file for each group. If my folder structure was "C:Desktop\2020\Group1", "C:Desktop\2020\Group2" this would be easier but unfortunately they all reside in one annual parent folder.

 

[yky]

Whatever the solution will be, you'll need to supply three parameters, namely the year, the month, and the group number. How do you plan to handle that? Using a user form or storing they in cells?

 

[amandakay740]

Honestly not sure - I'm really new to VBA and have a VERY novice skillset hence the workaround I am currently using by manually storing the latest version of each folder and using find and replace to correct the code, I know there is a smarter way but I'm struggling to google my way out of it. Most result just say "look at the last record" but that doesn't work for me because I need the last record applied to each group.

 

[amandakay740]

To add I was thinking that I might be able to accomplish this by using a wildcard where the version is in the file name combined with a look up for the last file saved but I’m not sure if that would work or how to go about it.

 

[yky]

To add I was thinking that I might be able to accomplish this by using a wildcard where the version is in the file name combined with a look up for the last file saved but I’m not sure if that would work or how to go about it.

Do you have a list of files in Excel? That would make things easier.

 

[yky]

I got it to work. It involves creating a function, a sub and a batch file. If you follow strictly the following steps, you should be able to do it.

Once you get them ready, enter the path in A1, month and group # in B1 and C1. It doesn't matter which one is in B1, which one is in C1 but the path must be in A1. Then, run the sub. The file with the highest number in the month and group will be opened.

Note: I have month in B1 and it's formatted as TEXT, not number.

Create a sub:

Sub get_output()
'this sub feeds the command line to the above function ShellRun
'to get the output from the command in a msgbox
'the sub gets three parameters from Sheet6 cells A1, B1, and C3
'you can change the sheet number to suit your need

Dim response As String
With ThisWorkbook

'set up the shell call
response = ShellRun("cmd.exe /c fh " & .Sheets("Sheet6").Range("A1") & " " & .Sheets("Sheet6").Range("B1") & " " & .Sheets("Sheet6").Range("C1"))
End With

'append path to file name
response = Sheets("Sheet6").Range("A1") & "\" & response
'open file
Workbooks.Open (response)
End Sub

Create the function, which I copied from this thread with little modification:

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine '& vbCrLf
    Wend

    ShellRun = s
    Debug.Print "a" & s & "a"

End Function

Create the batch file. Copy the code, paste it to notepad, and save it to C:\ as ph.bat:

REM this batch file takes three parameters, a directory, %1, and two filter words, %2 & %3
REM and filter the file list to give the filename with the highest number

@ for /f %%a in ('dir %1 /b ^| find "%2" /i ^|find "%3" /i ^|sort /r ^|findstr /r [0-9]') do @(
@ set "filename=%%a"
@ goto done
)
:done
@echo %filename%

@exit /b 0