SERIALIZE Excel spreadsheets

  • Thread starter Thread starter mitchelliii
  • Start date Start date
M

mitchelliii

Help. If anyone could point me in the right direction to have Excel
append a serial number based on every time I open a file (i.e. a quote
sheet so each time I open it I get a DIFFERENT serial number) I would
be eternally greatful.

Best,

Scott
 
Scott,

Take a look at my response to a similar question in the .Misc newsgroup,
entitled 'Excel N umber Formulae'.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
What I do with word files is keeping track of a counter in an seperate
textfile using Visual Basic. I open the textfile, read the number, put it
where I want to, add 1 to the number, and write it back to the textfile.

Would be something like this

Dim FilePath As String
FilePath = ThisDocument.Path + "\worksheetnr.txt"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FilePath) Then
Open FilePath For Input As #1 .
Do While Not EOF(1)
Input #1, MyValue
Loop
Close #1
Else
MyValue = "1"
End if
Val (MyValue) 'Make Sure you only got the number
'Prepare counter for the next query
Open FilePath For Output As #1
WriteCounter = MyValue + 1
Write #1, WriteCounter
Close #1 '


'Create a string with leading zero's, just in case your looking for that
also
MyStrValue = Str(MyValue) ' Make it a string again so you're able to add
leading zero's
If MyValue > 99 Then RefNr.Text = MyStrValue
If MyValue < 100 Then RefNr.Text = "0" & MyValue
If MyValue < 10 Then RefNr.Text = "00" & MyValue

Couple of notes:

1) This is a quick word hack, some things could be different for excel
2) It Could be I have 'deleted'a line or two (I don;t want to ore you with
the compleete routine)
3) If you are in a multi user enviroment, you should consider to change the
extention of the textfile. (I like the dll extention as nobody dears to mess
with it... ;-D )
4) Not usable if you want to run it from a CD. (No write access)
5) I'not sure for excel, but in Word ther are some auto start routines, like
Document_New()

MK
 
Back
Top