PDA

View Full Version : Waiting Function, How?



atzdgreat
07-05-2017, 08:40 PM
Hi guyz I have created a project that 2 or more computers can access my excel database.. now my problem is I tried 2 computer saving data at the same time to my database, 1 of the computer will successfully save but the other 1 appearing Password Prompt. so far here's my code... maybe anyone of you can help me about this :(



i = 0
bFound = False
While i < 20

If FileInUse(DBLoc & NewFileName) Then
'If IsFileOpen(DBLoc & NewFileName) = True Then
Application.Wait (Now + TimeValue("0:00:01"))
i = i + 1
bFound = False
Else
i = 20
bFound = True
'Exit Sub
End If
Wend

If bFound = True Then
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Else
msg = MsgBox("Cannot Proceed to Task! Database already opened by " & GetFileOwner(DBLoc & NewFileName) & ".", vbOKOnly + vbExclamation, "ERTS Guide!")
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Set src = Workbooks.Open(DBLoc & NewFileName, Password:="2177", WriteResPassword:="2177", ReadOnly:=False)
src.Worksheets("Done").Unprotect Password:="2177"
Application.AskToUpdateLinks = True

Logit
07-06-2017, 06:52 PM
.
Excel is generally not designed to allow more than one user to access the file at one time.

This resource explains about SHARING so more than one user can : https://support.office.com/en-us/article/Use-a-shared-workbook-to-collaborate-49b833c0-873b-48d8-8bf2-c1c59a628534

Another idea others have used is to have a MASTER file that periodically checks each individual users file for changes. The MASTER file is not accessed by the users, it simply updates itself with any
changes made to each individual user's file.

Lastly, Microsoft has a CLOUD version of EXCEL that can be used for more than one person to access at the same time.