Using FileExist to check name before saving file

[pvking]

We use a very sophisticated Excel Spreadsheet to generate Order Confirmations, Invoicing and Delivery Notes. It is full of Macros so everything is automated and it takes out the human error but the only thing we can't control is saving the file as the wrong number so looking for a workround.

Typically we save files as:

'OC6027.pdf', 'OC6028.pdf', 'OC6029.pdf'... or 'IN6027.pdf', 'IN6028.pdf', 'IN6029.pdf'...

but nothing stopping the user accidentally putting 'OC6020' in the first spreadsheet as the next sequential number and then the current macros will copy across the error to the rest of the paperwork and just overwrite an existing file.

Is there a way to look into a folder (we only use 1 folder on the entire network) to check the last sequential file name and return it's value +1?

The only other alternative would possibly be to use If FileExist in the current Macro before saving

Can anybody give any guidelines?

[PaulSP8]

Take a look at this thread, see you can work with it;

https://www.excelforum.com/excel-pro...ml#post5143545

Post back if you need more help

[dmcgov]

try these two procedures, that should work for you:

Please Login or Register  to view this content.

change what is in RED to meet your needs

[pvking]

Hi DMCGOV and PaulSP8 and thanks for the assistance although after a few hours of trying to get this to work, have decided that this might be too complicated for what we need.

After a bit of a re-think, all we need to do is ensure that the file is saved as what is in the Range("G3") which is working well and just ensure we don't overwrite another file so going down the MsgBox route.

So all we need is to check if the value Range("G3") does not exists as a file name in the folder and if so bring up a vbYesNo message box which lets the user know the file exists and give the option to overwrite or abort. This is where I am at the moment but getting a 'Compile Error' on the Range("G3") saying Sub or Function not defined even though it is working perfectly well later in the routine.

Please Login or Register  to view this content.

Have I got the FileExist wrong?

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. You are still quite new, so I will do it for you this time.)

[dmcgov]

can you post the 'FileExist' function? and maybe post the workbook too.

[RobVerheyden]

Wouldn't it be an option to rather block the user from changing the sequential number?
You could even just use a data validation on "Cell R[-1]C (row-1 on same column) + 1", that way you take out the error at the root rather then creating a complex code for what seems to be an easy problem...

Could be I get your problem wrong, but I guess the data validation could work, no?

Worst case you could even just put block the entire column and just add a formula that wil calculate the "invoice number" or whatever it is you need as the filename?

Regards,
Rob

[pvking]

Hi RobVerheyden that is an interesting option and didn't think of that. The files currently look like:

OC2213.pdf
OC2214.pdf
OC2215.pdf
OC2216.pdf

etc etc and generally the user looks up the last number in the folder and types in the next one. They should enter "OC2217" but have in the past types something like "OC2117" and overwritten files which we can't detect or retrieve.

The most ideal situation would be a macro button beside the which they press and it automatically works out the last file that exists and enters the value +1 then they don't even have to look in the folder.

Thanks for thinking outside the box

[pvking]

Hi again dmcgov

below if the full file FileExist that isn't working in red.

Please Login or Register  to view this content.

I am not sure if we have got the arguments correct or if pressing the "NO" button will jump to the end and miss out the file saving and printing.

at the moment we are using everything else

Thanks for your support

[pvking]

hi colours taken out and modified the title to be more relevant - sorry still understanding the etiquette

What are the code tags?

[AliGW]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. I will do it for you this time - next time, you must do this yourself, please.)

You really need to read our forum rules, please: https://www.excelforum.com/forum-rul...rum-rules.html

[pvking]

That's great help and done it

[AliGW]

I did it for you.

Please take a moment to read those rules - they aren't complicated.

[pvking]

Yes already had a scan through. Thanks again for your help

[dmcgov]

please post a desensitized workbook. there are issues with fileexist and variables, but i need to see what your seeing.

actually do this:

Please Login or Register  to view this content.

[bakerman2]

Based on the filenames you provided in Post#8.

This function will lookup last used number and add 1.

Please Login or Register  to view this content.

[pvking]

Hi dmcgov I am happy to share so find the file attached.

There are a lot of Vlookups referring to customer lists and price lists but have taken out any personal information and the turned off the cell protection

I have also had to delete a lot of the pages like Purchase Orders, Delivery Notes and Pro-Forma which are all similar but needed to get under the 1Mb

Hope it makes sense

[dmcgov]

so change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.