Opening Filename with variable numeric ending

[PFDave]

Hi all,

I'm looking for some help resolving an issue I have with opening a filename which has 'version control' so the filename's numeric value at the end is forever changing. This numeric value also operates with a decimal, so for example we're on 1.1 currently.

Below is the code I have but I need some advice on what I can do about the changing number, ultimately the code to open the filename OPEN THIS ONE *

The text for the filename required is unique in the folder I'm searching as the others are archived, it's just the version number that changes but there will only ever be 1 version in the folder. Potentially human error could mean two are in there so potentially an ON ERROR piece might be needed, which again I'm open to suggestions for how best to work that in.

Please Login or Register  to view this content.

Thanks in advance, and please do ask if you need any further clarification.

Dave

[Richard Buttrey]

Hi,

The easiest way would be to create a cell named say "version" that contains the version numbet and read this value into the macro. e.g.

Please Login or Register  to view this content.

[PFDave]

Hi Richard,

Thanks for your suggestion. My issue with this is that someone would have to update the "version" cell each time they changed the file and the version cell would be located in another file.

Taking on board what you've said, is there a way I can use VBA to take the last three characters each time the file is saved and update the "version" cell?

Thanks again

[Richard Buttrey]

Yes you could use the workbook Save event and automatically update the version number, but you may not want to if you can't guarantee that the user won't periodically save the workbook several times during the day. Each save would update the version.

You could use the Workbook Close event to update the version number and have the macro save the workbook but the same problem might arise if the workbook is re-opened during the day.

....a thought has just struck me! You could probably get round the problem above by writing the current date to a cell. When the workbook is saved and before updating the version number the macro could check whether the current date is the same as the date in the cell and if it is don't update the version number. You would also need a workbook open event that does the same thing. i.e. check whether the current date is the same as the cell date. If it is don't do anything. If it's not, implying the workbook is opening on the next day, delete the date from the cell so that the first save on the day would repopulate it.

[PFDave]

Can we use the workbook Save event to make the filename = "filename" & CStr(Range("version")) with "version" still a named range for a specific Cell?

Then we could add code to copy and paste the version value into the other sheet where this file is retrieved from

I need to allow the user to be able to select version control to ensure it tallies up with when significant amendments are made which are not on a daily basis.

Thanks for your help thus far, certainly helps to talk this through, I feel like we're getting somewhere

[Richard Buttrey]

Hi,

I'd missed the comment in post#3 where you said that another file would be involved and contain the version number. That wasn't what I was suggesting in #2. I can't see any reason why the version number can't be contained in the workbook itself - or am I missing something. I also meant that the macro would update the number.

Can we rewind?

Are you saying that you want the user to be able to open the latest version of the file, but because of human error (your #1) there may be more than one.

The question then is would the latest version of the file also carry a date which is later than any previous versions?
If so you could have a master workbook that contained just one sheet with one button ("Open Latest version") which runs a macro that inspects every file in a given path\folder that contains the fixed text of the filename in question and opens the latest dated file.

Is that something which would work?

[PFDave]

The question then is would the latest version of the file also carry a date which is later than any previous versions?
If so you could have a master workbook that contained just one sheet with one button ("Open Latest version") which runs a macro that inspects every file in a given path\folder that contains the fixed text of the filename in question and opens the latest dated file.

Is that something which would work?

Yes I believe that would do the job!

Are you able to help with the code for this please? So the master workbook would contain the button "Open Latest version" which runs the macros (the macro being the one I need your help writing)

Thanks again Richard

[Richard Buttrey]

Hi,

Here's one based on something I created for another client a couple of years ago. As far as I'm aware they've been using it without problems since.

Put the LoadFile procedure and NewestFile function in the new Master Workbook. Change the "C:\Temp" string in the LoadFile procedure to your folder name and attach the LoadFile macro to a button in the Master Workbook which the user will click to run the process. The Master workbook will automatically close when the macro has run.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[PFDave]

Awesome Richard! This works perfectly.

To future proof this;

If there any way to and a condition that the file must contain certain text? at present there are multiple files in this folder, any of which could be updated and become the most recent version

*Edit* I will add that I've already proposed a change to their process in terms of organisation into it's own folder

[Richard Buttrey]

Hi,

Use this modified Function

Please Login or Register  to view this content.

[PFDave]

Thanks Richard, however when I amend "testfile" to the start of the filename (and the number 8 to 7) I need it doesn't open that it opens something else. Weird. I need it to look for "ADH WAR" so could you advise?

Regards

[Richard Buttrey]

Hi,

This bit of the code

If LCase(Left(FileName, 8)) = "testfile"

says convert the first 8 character in the file name to lower case and test against the word "testfile". (LCase is the syntax to convert a string to lower case). It's always advisable to do it this way since provided the "testfile" string is in the code in lower case, even if someone changes the filename to upper or mixed case when the macro uses the filename it converts it to lower case.

In your case therefore you need to test against "adh war" , i.e. lower case. So try

If LCase(Left(FileName, 7)) = "adh war"

[PFDave]

Hi Richard,

Thanks for that, simple logic which I probably would have overlooked for hours!!

Works like a dream, thanks