Macro save backup in specific folder with sequential numbers

[Vlad2112]

Hello, I found this macro;

Please Login or Register  to view this content.

Which works like a charm, but I need it to add a consecutive number to the file name if a file with the same name exists.
F.e. if file1 exists create file2, and so on. I cannot figure out for the life of me how to do it.

[HaHoBe]

Hi Vlad2112,

Iīm no friend of using sequentiel numbers to enhance the name of a workbook as I prefer to use a DateTimeStamp to do this to avoid finding the sequence of 1, 10, 11, 12, 2, etc.

Code goes into a standard module:

Please Login or Register  to view this content.

The code above will save the number to a custom document property in the workbook and augment it for every use.

What I would prefger is a code like

Please Login or Register  to view this content.

Ciao,
Holger

[Vlad2112]

Thank you very much for replying. The first piece of code gets the job done. Im new to coding and macros though so I'm struggling to change the directory where the macro saves the file. I'm guessing that I have to replace the "\" part of this piece of code

Please Login or Register  to view this content.

with this?

Please Login or Register  to view this content.

So it would look like this

Please Login or Register  to view this content.

Correct? Its giving me an error though, am I doing something wrong?

[HaHoBe]

Hi Hi Vlad2112,

Please Login or Register  to view this content.

is the place where the workbook with the code is stored. So you would need to substitute the place where to save to

Please Login or Register  to view this content.

for my coding.

And I finally realized what has got me stuick all the time as there is a command named SaveCopyAs which will do just as the command name says: save a copy of the workbook but keep the active workbook open.

So with that alteration the code may look like

Please Login or Register  to view this content.

Ciao,
Holger

[Vlad2112]

Greetings, apologies for reopening a solved thread, but I have run into a small issue.

The macro works like a charm, that said, what would be the simplest way of resetting the count back to 0? Also, is it possible to reset it back by a number or by a couple numbers?

Thanks in advance!

[HaHoBe]

Hi Vlad2112,

anybody who knows where the number is stored could cause trouble by changing the number as it may be changed by looking at it the workbook from the backstage view in Excel. Or it may be accessed by VBA like in the coide line

Please Login or Register  to view this content.

where the code would pass the next number to the property. You may either access that by itīs own like in this modified function where ht e results are displayed in the Immediate Window:

Please Login or Register  to view this content.

The code resets the number to 222 in the first line of test, adds one to this for the next and subtracts 15 from 223 in the third line. A lot of room to play with the code...

Ciao,
Holger

[HaHoBe]

Hi Vlad2112,,

another sample code to play with - this time using a Select Case:

Please Login or Register  to view this content.

Ciao,
Holger

[Vlad2112]

Greetings!

Is there an option to make several workbooks share the same running number? Like a global module for all workbooks so if I run the macro on a different book it continues saving with the consecutive number?
F.e. if this macro is ran in 2 different books, they each have their own number, is it possible to have a unique global one?

Thanks for the very helpful advice!

[HaHoBe]

Hi Vlad2112,

you must either have the workbook with the updated number open or open it in order to get the number. In this case I assumed that both workbooks are saved in the same folder:

Please Login or Register  to view this content.

But you may think about using a text file to hold the last used number as to get the next number out of this like

Please Login or Register  to view this content.

You may alter the number inside the textfile as you like so no additional code for that part luded by now.

HTH,
Holgers inc

[Vlad2112]

Hello,

The text file idea sounds brilliant. Im guessing this is how it should look when put together with savetolocation;

Please Login or Register  to view this content.

I think I'm doing something wrong though, In the macro selection window I now have 2 options, "savetolocation" and "test_fncrunningnumber", not sure if that's how its supposed to be, I probably put them together the wrong way. Either way I tried running savetolocation but the number was not increasing with each consecutive save.


Also having this weird thing where its adding +2 each time I run test_fncrunningnumber. (Unfortunately due to my level I cannot post images, basically the pop up window from test_fncrunningnumber seems to be adding +2 to each consecutive number. It goes like this; 1,2,4,6,8,10,12. Meanwhile the number in the txt goes like; 1,3,5,7,9.
Any ideas on what I'm doing wrong?

Thank you for your attention and help!

EDIT: Nevermind, had to update " lngNumber = RunningNumber" to "lngNumber = fncRunningNumber"
Works like a charm! Thank you very much!

[HaHoBe]

Hi Vlad2112,

glad you could sort it out by yourself.

Just a word on the part when saving your workbook. You use the command SaveAs which changes the ActiveWorkbook. There is another command which avoids this and keeps you within the original workbook (no need to close the newly saved workbook or reopen the original file): SaveCopyAs.

With the use of that command the last lines of your code could be changed to:

Please Login or Register  to view this content.

Ciao,
Holger

[Vlad2112]

Greetings, I've been trying to add an extra feature to this formula, though I feel like I've been given plenty enough help, if it proves to be too much work you could just set me on the right path and I'd try to figure it out myself!

So the way we're doing things is after those invoices are saved in the set folder with the _00# suffix, we have a separate excel that takes all of the info from the invoice and puts it in a row, to add the next client we drag the formulas down (its around 74) and use the replace tool to replace 001 for 002, this shows the info of the next client.

Would it be possible to make this macro do that too? Basically open that specific excel workbook and /Drag down & update running number in formula/ or /copy paste formulas from txt and update running number/ or something like that? Must admit I'm feeling clueless.

Thanks for all the invaluable help!

[HaHoBe]

Hi Vlad,

a couple of questions need to get sorted out prior to starting with the code (which should not be a great deal).

Please correct me but until now I believed that the file you work on and which is saved with the sequentiel number part holds all the data which is only saved as a copy, data is erased and template used again for the next client/invoice. If itīs like that data may be taken from the macro workbook and stored in the overview workbook. If data is altered in the copies you would need a different approach.

How mayn sheets are inside the workbooks holding data?

Do you need the information about the name of the workbook in the overview workbook as well?

Should data be stored row by row without blank rows? What about the files already created? Getting the data from them would be another macro

The layout for the macro would be like that (being added at the end of the originla macro):
- try to set an object on the overview workbook if it is already opened in your Excel using On Error Resume Next
- if there is an error clear the err and try to open the workbook from itīs location
- see if an object to the workbook has been set
- if not somebody else has the woirknbook opened and you should either be informed about it and/or just skip the part of writing information
- regarding on the process chosen get the next row to write information to (although a lot of blanks will be left Iīd go with the running number starting at the first row below the headings in overview. If you started with a modified number and do not wnat a lot of blank rows you may give a constant to subtract from that number)
- If you have given a name for the cells which need to be transferred you may loop through the range, otherwise you should build an array holding the addresses of the cells and loop thorugh the array while having a counter to get the appropriate column to write to)
- close overview after saving or using Close SaveChanges:=True
- release the object from memory
- clear the cells where necessray

Awaring yyour response
Holger

[Vlad2112]

Greetings, thanks for the reply,

The proccess works the following way; we take the template with the macro and put it in a separate folder dedicated to a specific client, then once the file is filled a copy is saved in that folder and another is saved in a specific folder which contains all of the invoices, these copies are identical to the original file stored in the client's folder and are not modified unless there is an error in it. Then, a separate excel (the one i mentioned) uses a formula like this one to extract the info;

Please Login or Register  to view this content.

I'll attach the workbook, any information shown in it is made up;
Attachment 731822
(hope I attached it the right way)

The reason we do it this way is because I couldn't find any simple way of making so this excel extracts info from the original excel files that we make, because these can be located almost anywhere, the folders where the original file is stored are named after the client + the type of service he requested and are stored within other folders which are sorted by date. A simple solution to this was the macro we made which saved a copy of the original file in a unique folder where this workbook can easily extract all of the info.

But I suppose this has a couple extra benefits like having a backup copy of the invoices and offering easy access to any of them at any moment.

After that main workbook is updated with all the new registered clients, a number is assigned to that client (by draggin down basically) and then other workbooks made to proccess the invoices and extract the info for other purposes can do these tasks in a matter of seconds by just writting in the number that was assigned to that client, then Vlookup does the rest and all of the info of that client is shown.

In case I attached the workbook the wrong way I'll answer the remaining questions;
Just one sheet; the name of the workbook isn't needed because they're all named the same, the only difference between them is the suffix running number, the file itself is just called ''invoice''; Information should be stored row by row with no blanks; about the files that were already created, we still haven't put this piece of art into our official proccesses as I'm trying to improve it to make it almost effortless for our coworkers to do all the proccesses after the initial invoice is registered. But either way even if we were using it, the formulas would already be in place and assigned manually to the invoices that we had made so nothing much would be needed (I believe).

Regarding this, "- if not somebody else has the woirknbook opened and you should either be informed about it and/or just skip the part of writing information"
The main workbook is shared so multiple people can use it at the same time without creating conflicting copies


Hopefully I answered every question,

Thank you for your efforts!

[HaHoBe]

Hi Vlad,

no attachment added.

Please try this slightly altered coode (no function for new number included but for testing number should be altered by hand.

Please Login or Register  to view this content.

In the attachment the workbook with the code ("Test Umschreiben Vlad.xlsm") showing some fake data as well as the workbook data should be copied to are included. Please make sure to alter the paths and names if you alter them accordingly fro testing purposes.

Ciao,
Holger

[Vlad2112]

Hello Holger,

For as far as I've tested, it does exactly what it should do, its likely that with this macro saving backups with running number wont be needed, since that was being used for a different approach, I suppose savetolocation can be changed to just regular save. Also, the name of the invoices wont be needed in column A, if its not needed for the rest of the code, how can I remove it?

I'll continue testing this new approach, thank you for the brilliant ideas!

[HaHoBe]

Hi Vlad,

concerning Column A in the overview. This is filled via

Please Login or Register  to view this content.

You might like to change that code line to

Please Login or Register  to view this content.

If you donīt want any information you can comment that code line but in that case you should set the variable for the starting column to 1

Please Login or Register  to view this content.

or alter the code line for getting the first free line to
[code]lngWriteToRow = wsTarget.Cells(Rows.Count, "B").End(xlUp).Row + 1{/code]
Ciao,
Holger

[HaHoBe]

I hate to see the edit window being empty once you discovered a mistake and want to correct it.

Last code should be displayed like

Please Login or Register  to view this content.

Holger