Find if filename exists and update variable within the filename structure

Hi again everyone.

I have searched many different forums and found a number "IF FILE EXISTS" codes, but cannot get them to work in my particular situation.

I have a userform macro that populates info from a number of public declarations. When I attempt pre-fill my variation invoices, I would like the code to check if a file already exists and update the variable contained withthin the file name.

The filename structure consists of a job number, then "v" then variation number (the variable) and the other details.

It ends up looking like this "3000v1 - job address", with the "1" being the variable if that variation # already exists.

Below is what I have come up with so far but cannot get it to update the value of "k" when it places information on the invoice.

Range "I5" on the invoice should reflect the updated (part) filename.

Cheers in advance for taking the time to look at this.

Martin

Please Login or Register  to view this content.

Martin

If v should go in I5 perhaps you should change variname to v here.

Please Login or Register  to view this content.

Martin

Have you tried stepping through the code with F8?

That should give you an idea of what's happening.

mmm... That only highlights (in yellow) every line of code one after the other.

Please Login or Register  to view this content.

variName = pwjnum & "v" & k

pwjnum = 3000
v="v"

1(k) = variable depending on previous variation invoice numbers


Other find file functions that I have in this workbook, work fine using :

Please Login or Register  to view this content.

But if the file DOES exist, then the code should

Please Login or Register  to view this content.

This is where (I think) the problem occurs.If the file DOES exist then then code doesn't do:

Please Login or Register  to view this content.

.

Which would make k=2 if the file 3000v1 existed. and there fore the new string in I5 would be 3000v2.
If 3000v2 existed, then k=3 and I5 = 3000v3, etc.

Cheers

Martin

Please Login or Register  to view this content.

Martin

It also shows you how the code is working, for example what's happening at the If statements.

You can also monitor the values of the various variables in the code.

For example you could check the value of variName as the code is being executed.

By the way which sheet is I5 on?

I'm asking that because there's no worksheet reference here, so it will point to whatever sheet is active when this line of code is executed.

Please Login or Register  to view this content.

That could mean that variName is going into I5, but not I5 on the correct worksheet.

Martin

In the code variName is never updated.

So it will always stay the same value as it's given here.

Please Login or Register  to view this content.

Norie, Thanks for your time on this.

I think we might be getting somewhere.

variName, in the first instance (if file does not exist), k=1. Agreed?!

When the code in one of the previous posts, finds an existing file (3000v1) and then goes to :

Please Login or Register  to view this content.

I thought that the new instance of variName would automatically update the value of k and become 3000v2, because variName is made up of pwjnum & "v" & k

Do I need to re-declare variName @ "Do Until"....

How do we update the value of k in variName?

Cheers

Martin

Martin

It doesn't work that way.

There is no 'instance' of variName, it's a variable and the value won't change unless you explicitly change it.

Try repeating the line that sets variName after you've updated v.

Norie,

"v" doesn't get updated it's just a letter used as an identifier.

The value of k is what gets updated.

Please Login or Register  to view this content.

I have tried repeating the variName setting in different (relevant) locations in the code but it makes no difference.

I've had a go at stepping through the code and it shows that the value of k = 1 throughout the code.

When stepping with F8, it skips over the highlighted section of code below.

Please Login or Register  to view this content.

The line of code :

Please Login or Register  to view this content.

Works fine in other macros in the userform. To say that it finds the file and produces a message box "file found/exists, please try again"

I am wondering why this part of the procedure:

Please Login or Register  to view this content.

either, doesn't execute or doesn't find the existing file, and update the value of k accordingly.

Am I using the wrong kind of function for "find file" in this example?

Cheers

Martin

Sorry duplicate post. stupid slow internet.

v, k whatever, I knew it was something like that.

The important thing was that variName was never being updated.

I've not had a proper look at the working code but if it works then it works.

Had a proper look at the code, perhaps you could tidy it up like this.

Please Login or Register  to view this content.