#-Wildcard application

athielem

New Member
Joined
Apr 22, 2020
Messages
9
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:

Rich (BB code):
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
As long as it is only ever a single digit you should be able to get it with something like
VBA Code:
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 :))
 
Upvote 0
Ok, before I had this:

VBA Code:
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! (y)
 
Upvote 0
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?
 
Upvote 0
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:

VBA Code:
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 ?
 
Upvote 0
Another way to get the version number...
VBA Code:
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).
 
Upvote 0
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).
 
Upvote 0
I think that is the benefit of experience, Rick. I still have daily brainfarts on basic stuff :oops:
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top