counter on external file

[fddekker]

I have the following scenario:
<o> </o>
STEP 1
Users open a template, complete the template, save the file as an .xls file and then e-mail it to me.
<o> </o>
STEP 2
When I receive the e-mail, I open the e-mailed file, check the entries and then run a macro from an add-in I wrote on the file that digitally signs the file and locks all cells. I then e-mail the file to a processing department. (In my absence, a co-worker with access to the add-in validates the coding)
<o> </o>
Needed.
The processing department would like to have each validated file numbered with a sequential number, let’s say starting with 10010001 (next number 10010002 etc.)
Is there a way that I can have the macro access a third file, pick up the last number, increase it by one and paste it in let’s say cell D3 of the file?
(Since I would like only validated files numbered, I don’t want to save the counter with the template).
<o> </o>
Any help would be appreciated.

 

[Kenneth Hobson]

Saving and getting from the registry is one method.

Here is a txt file method.

Sub Test()
 Dim s As String, count As Long
 s = ThisWorkbook.Path & "\Counter.txt"
 count = ReadCounter(s)
 MsgBox "Current count=" & count
 IncCounter (s)
 count = ReadCounter(s)
 MsgBox "LastCount+1=" & count
End Sub

Sub IncCounter(toFile As String)
  Dim iHandle As Integer, counter As Long
  On Error Resume Next
  counter = ReadCounter(toFile)
  iHandle = FreeFile
  Open toFile For Output Access Write As #iHandle
  Print #iHandle, counter + 1
  Close #iHandle
End Sub

Function ReadCounter(filePath As String) As Long
  Dim str As String, hFile As Integer
  On Error Resume Next
  hFile = FreeFile
  Open filePath For Binary Access Read As #hFile
  str = Input(LOF(hFile), hFile)
  ReadCounter = str
  Close hFile
End Function

 

[erik.van.geit]

Hi,

You can update the number on a sheet within the addin itself.

kind regards,
Erik