Auto-numbering workbooks ? 1

[Novexx]

Is there any option/add-on/trick which would allow Excel 2003 to auto-number each workbook opened or saved? Preferably something which I could set format & start number for, which would be visible within a worksheet of the workbook, so that it could be shown when the worksheet is printed?

Thanks.

 

[GlennUK]

Please give the reasons behind your request. You request says "so that it could be shown when the worksheet is printed" ... which is easily achieved and I don't see the connection with the first part of the request.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Novexx]

I want to have newly opened or saved documents numbered in sequence, and need this sequential number on the worksheet/document so that is is printed with the document.

Sort of like what Sage accounts or the like would do with Invoice numbers, but its for job recording.


Thanks

 

[SkipVought]

Can you explain in more detail the functional process and reason for this requirement? It will help in directing possible answers.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Novexx]

OK, its for use in a workshop environment where each job has to be sequentialy numbered on the document & file name. Management are to tight to splash the cash on ony sort of software for the job.

At the moment I have a read only job template in Excel, which I fill in with relevant details (inc document number)& then save as 25936 (or whatever the next in sequence is). These numbered jobs are saved to one of a number of folders (to start, pending, on hold, completed....) dependant on their status. because there are multiple folders to look in for the last job number, its to easy to re-use the same file name again, which causes problems as there is a degree of traceability involved. The file name is used on the document, which requires signature at various stages through the way, so back-tracking & making changes is not really an option.

So I am trying to find some way of opening or saving workbooks which are automatically numbered in sequence, both within the document & for the file name.

Tall order I appreciate, Thanks.

 

[SkipVought]

In yout PERSONAL.XLS workbook, add a sheet to hold the LastSeqn.

You will have to code your own SaveAs routine instead of using the menues and toolbar events.

Workbooks("PERSONAL.XLS").Sheets("MySequenceNumber").[A1] = Workbooks("PERSONAL.XLS").Sheets("MySequenceNumber").[A1] + 1
Activeworkbook.SaveAs Filename:=fName & Workbooks("PERSONAL.XLS").Sheets("MySequenceNumber").[A1] & ".xls"

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[onedtent]

can you not use a save as routine and just date & time stamp into file name? that way it will have unique name and numbers

 

[SkipVought]

The OP wants a sequential number.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Novexx]

Wow,

Thanks Skip, I didn't think this would be a possibility! Sorry to be a pain, but where might there usefull resources on the web for someone who knows not much about coding & routines?

thanks again

 

[SkipVought]

For further help on your project, coding in Excel VBA, please post in Forum707.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}