large document / options

[farfromapro]

okay this should be done in access...I know that...but we don't have access to access...so it must be done in excell....

Background:

My company manufacters parts, every time we make a new part we have to document the new part number. The part numbering system does have some inteligence, such that the first 3 numbers describe a "series" (which the excell spreadsheets name will be, each series will have a different file...unless someone changes my mind ) after the seris there is a component letter (basically A-Z where each letter stands for something), after the component letter is a variation letter (A-Z)...then finally "next in line numbers".....so for example 580aa051 This would be the 51st part for a 580 series comp A, variation A part...got it?? i hoped i described this okay

current system:

We have books with tables one book per series...so a 508, 123, 941 book...then the first page is AA...next is AB, AC......until ZZ..... on the AA page the first part is 001, follow by 002.... same system each page...so the first part on the ZZ page is 001......

possible solution as of now:

an excell spreadsheet named as the series....580.xls ...when you open it up there will be a cover page....and then worksheets AA, AB, up to ZZ....

any better way?

also this will take a while to go form AA to ZZ...any fast way to make 576 sheets?!?!?! can i tell excell that each sheet will follow my alpha system as opposed to "sheet 20", "sheet 21", "sheet ...." right now I would be renaming all 576 after I inserted them...must be a better way!!! If i have to do it this way i would like to create my cover sheet (which would have links to all of the sheets) and then my first AA sheet...and simply copy the format of AA and use it for AB - ZZ.....help!!!

any suggestions..i'm not starting this project for 2-3 weeks...any advice is appreciated....

There is a large amount of data and i believe seperating it by worksheets will help...

ideas???

[mrice]

The following code should generate your large worksheets with an index page

Sub Test()
Workbooks.Add
ActiveSheet.Name = "Cover"
For N = 65 To 90
For M = 65 To 90
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = Chr$(N) & Chr$(M)
Sheets("Cover").Hyperlinks.Add Anchor:=Sheets("Cover").Cells(((N - 65) * 26) + M - 64, 1), TextToDisplay:=Chr$(N) & Chr$(M), Address:="", SubAddress:=Chr$(N) & Chr$(M) & "!A1"
Next M
Next N
End Sub

[farfromapro]

THANK YOU THANK YOU THANK YOU!!! i wish i could write a program that would spit out thank you a million times!!! I appreciate it! i'm starting to wish I went to school for computers instead of mechanical engineering.

Now I have to figure out a way to make my format for page AA carry on to ZZ with out effecting the cover.....any tips?

again...THANK YOU!!! i am very impressed....THANK YOU!

I understand the adding of workbooks line...and I see where the adds the next sheet, and when it names it...i don't see where it gets the alphabet to be N and M.....mind discussing it a little??

[farfromapro]

it takes almost a full minute to save on fast computer....it's only 1.31 MB hhmmm....i'm still using it thought!! no doubt! Just need to work on implimenting a format for the AA-ZZ is that some simple code as well? worksheet.pasteformat (AA,all) ha ha I wish.....

I need to possibly limit the column...I will only use up to Z columns on the cover and maybe half of that on the other sheets...do empty columns take up space and slow down save times??

is excell doing anything in the back end like checking worksheets or columns that may be affecting this? thanks again!

[mrice]

The letters come from the N and M loops as you predicted. The upper case letters A-Z have ASCII codes 65 - 90 inclusive and the loops simply cycle through the combinations of the numbers and convert them to the two letter codes by concatenating the characters returned by the CHR$ function which converts the ASCII code back to the character that it represents. You can see the full set of ASCII codes by looking for this term in Excel help.

[farfromapro]

ascii....now I follow! thank you....do you think me attempting to subtract unused columns will have any effect on saving the file?

[mrice]

Not sure - worth an experiment....