#-Wildcard application

[athielem]

Hi guys!

I am interested in finding out the exact version that the loop is coming across in the array. In this case it is v1 for i, though v5 is also possible. For my particular task it is important that I only retrieve the newest one (i.e. v5). In some rare cases there is also no version specification at all.

I've already defined the beginning of the code, which checks whether there is a combination of "_v" and any integer:

Files(i) = "lol_v1.xlsx"
If Files(i) Like "*_v#.xlsx" Then

To continue, I would like to firstly know which exact version I am dealing with, i.e. what integer is "#" actually? However, I have absolutely no clue of how to tackle that.

I would be very grateful if someone could give me a hint! For instance, whether you can specify the position of the # found with the "Like"-Operator?

Kind regards
Andreas

 

[jasonb75]

As long as it is only ever a single digit you should be able to get it with something like

myFileVersion = CLng(Left(Right(Files(i), 6), 1))

There could likely be a simpler way based on the rest of your code, but you didn't show us that (please use vba tags, not rich tags to post your code )

 

[athielem]

Ok, before I had this:

namenderDateien(1) = "bst_pf_bestand_2020Q1_v1"
namenderDateien(2) = "bst_pk_zzr_2020Q1_v1"
namenderDateien(3) = "ev_kon1_2020Q1_v1"
namenderDateien(4) = "bst_pk_zzr_2020Q1_v2"

count1=4

For i = 1 To count1
    pos = InStr(namenderDateien(i), "v1")
pos1 = InStr(namenderDateien(i), "v2")

If pos = 0 And pos1 = 0 Then
            NeueListe(i) = namenderDateien(i)
        Else

If pos <> 0 Then
DateiKurz = Split(namenderDateien(i), "_v1")
Range("A1").Value = DateiKurz

For j = 1 To count1

                    pos2 = InStr(namenderDateien(j), Range("A1").Value)
                        If pos2 <> 0 Then

NeueListe(i) = namenderDateien(j)

Else
NeueListe(i) = namenderDateien(i)
End If

Next j

Else

End If

End If
           
Next i

However, it turned out that there could also be more than two versions. And in that case, the code wouldn't work properly. As I don't think that creating if-conditions for, let's say 6 versions, is very profesional, I wanted to generalize it (but yes, the number of versions would never exceed the single digit 9, otherwise the #-method wouldn't also work properly). My goal is to obtain an updated array with the newest files from a particular folder. Unfortunately, the file names are all different from each, for which reason I cannot assume that the #-sign can be found at the 6th position...

Nevertheless, thank you for your super-fast response!

 

[jasonb75]

Unfortunately, the file names are all different from each, for which reason I cannot assume that the #-sign can be found at the 6th position...

As far as I can see, the digit is always the last before the file extension, i.e. before .xlsx, the line that I suggested cuts of the last 6 characters of the name #.xlsx then cuts off the first character (the digit) from those 6.

What requirements do you have based on the version # that is found? i.e. what should be done different with version 2 than with version 1, with version 3 than version 2, etc?

 

[athielem]

Oh. That's a clever idea! Sorry, I didn't Interpret the code correctly… ?‍

In my above example, I have two versions of one file:

namenderDateien(2) = "bst_pk_zzr_2020Q1_v1"
namenderDateien(4) = "bst_pk_zzr_2020Q1_v2"

The actual task involves roughly 40 files and is supposed to copy-paste the content of a specific worksheet into a single worksheet of a new file, which is like a summary (the code for copy-pasting is working, luckily). The only part missing is the correct 'filtering', which means that only data from the newest versions are supposed to be retrieved (v2 takes precedence over v1, and v3 over v2 and v1, …).

What I have in mind is to figure out which version the code is looping through, and then to run another loop over all entries in the array to check what (if at all) other version(s) exist. With the final goal of setting up an array consisting of the newest files, while ignoring the other older versions in the folder...

Do you think that my theoretical approach is recommendable? Or is there another way? As I am rather new to this, my tool box is rather constrained lol.

With kind regards from Munich ?

 

[Rick Rothstein]

please use vba tags, not rich tags to post your code

Why?

 

[Rick Rothstein]

Another way to get the version number...

Dim VersionNumber As Long, Arr() As String
'.....
'.....
Arr = Split(Files(i), "_v", , vbTextCompare)
VersionNumber = Val(Arr(UBound(Arr)))

Note: This will retrieve a version number no matter how many digits it has (in case you want to modify your Like expression to test for one or two digits).

 

[jasonb75]

Why?

Colour coding makes it easier to read in the browser, rich is only needed for custom formatting.

 

[Rick Rothstein]

Colour coding makes it easier to read in the browser, rich is only needed for custom formatting.

Ah, okay, fair enough. Believe it or not, I like it better with without any (or at least minimum) coloring (probably because that is how I have been reading code from paper print-outs for some 39 years now).

 

[jasonb75]

I think that is the benefit of experience, Rick. I still have daily brainfarts on basic stuff