excel vba - multiple users

C

chief

Is there a way to set up a workbook so that more than one user ca
access the workbook at the same time. For instance, I have set up a
invoicing program. When the user clicks on the shortcut to open th
program, it automatically generates a sequential invoice number.
have to go in and out of the program periodically throughout the da
for certain purposes, and this is causing errors because whoever trie
to go in second has to open the program read only. Is there some wa
that user 1 can go into the program, and then have user 2 go into th
same program without causing any read only situations or problems.
Furthermore, is there a way to set it up so that when user 1 opens u
an invoice, the invoice # becomes automatically generated to 5 for ex.
and then if user 2 goes in while user 1 is still in the program it wil
recognize that user 1 is using invoice #5 and jump to 6. It sound
tricky, and I would settle for the first problem solving if both canno
be achieved.

Thank
 
D

Dave Peterson

You could use another workbook that's opened momentarily, updated, saved and
closed.

Or you could use a simple text file that keeps track of the number:

Option Explicit
Sub testme01()

Dim myNumber As Long
Dim myLine As String
Dim myFileName As String
Dim FileNum As Long

myFileName = "C:\my documents\excel\text.txt"
FileNum = FreeFile

'read previous number:
If Dir(myFileName) = "" Then 'not found
MsgBox "File is missing!"
Exit Sub
Else
Close FileNum
Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
Line Input #FileNum, myLine
myNumber = Val(myLine)
Loop
Close FileNum
End If

myNumber = myNumber + 1

Open myFileName For Output As FileNum
Print #FileNum, myNumber
Close FileNum

End Sub

But the bad news is when you get the last invoice number and when do you save
the current invoice number.

If you do it too early, the user may not really want it--they could be just
snooping.
 
A

Arvi Laanemets

Hi

I haven't worked with latest versions of Excel much, but I don't think
there are some main changes in the way, the workbook sharing is solved. So
I'm afraid no way for this.

I myself give in such cases a separate workbook for every user. When needed,
you can consolidate data into special workbook, using links (I have
consolidated data from 25 - 30 workbooks easily).

About invoice numbers - the easiest way is to have for every user own
numbering system. But when you want to use common invoice numbers, then you
must have in every workbook a table, where latest invoice numbers for every
user are retrieved through links. When a new invoice is created, a max value
in this table is searched for, and then incremented.

Another way - a special workbook with predesigned invoice numbers in it.
When a new invoice is created, the first unused invoice number is searched
for, and marked through VBA as used (p.e. user identificator is added into
adjacent column).


Arvi Laanemets
 
Top