Users prompted for external Workbook password even though it is supplied via VBA

[Abominator]

Hi guys, hope you can help me as I have an urgent problem.

A simple "stats submitter" workbook that I designed to be used by 1 small team is now being used by 200+ people in my office due to the current staffing situation.

Our computers and network are so slow that people are essentially bumping heads while trying to send their stats to an external workbook.

The data they are inputting is a only a few Kb in size, so it should just be a case of checking if the workbook is available, opening it, doing a 2nd check to make sure the file is not Read Only due to another user being in at the same time, then adding the new data and saving and closing the file. Pretty straightforward, or so you'd think... here's the code I've been using, which is a bit ugly, but has worked successfully up until now (it makes 10 attempts to access the data bank file then gives up):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This worked fine when there were less staff using the system, but now that there are 200+ the users are frequently getting prompted for a password when the Data Bank workbook is being opened, despite the password already being supplied in the vba code! I'd rather it just failed entirely at this point rather than prompt for a password, as this is confusing the users (who are not at all computer savvy, which is why we need a silly system like this in the first place).

I am open to hearing any suggestions anyone has, no matter how bizarre! I don't want to have to resort to giving every user their own "data bank" and then cobbling all that data together, but I fear that might end up being the only viable option (I have already split the "data bank" into 10 chunks to try and ease the pressure, essentially dividing the staff evenly over all 10 banks, but even this has only helped slightly).

Here are a few notes:

• I can't give the users the password as all the other users' data within the workbook is sensitive (it all gets updated with ScreenUpdating off etc.)
• We have tried using shared workbooks in the past but these always end up corrupting.
• We don't have Office 365 or SharePoint so I can't try "co-authoring" or anything like that, which I'm not at all familiar with anyway.
• I know that having so many people write to the same workbooks is not at all ideal, but I can't use Access to store any of the data as it is not available on our systems.

Thanks a lot for reading all this anyway, any help would be much appreciated.

[Haluk]

.....
...
..

Here are a few notes:
.....
..
I know that having so many people write to the same workbooks is not at all ideal, but I can't use Access to store any of the data as it is not available on our systems.

I have not checked the code you've supplied but, there is one point I wanted to clarify...

You don't need "MS Access" to be installed on the computers to read, write or update fields in the *.MDB or *.Accdb files, using Excel VBA.


.

[Abominator]

You don't need "MS Access" to be installed on the computers to use a *.MDB or *.Accdb file to read, write or update fields in the Access file via Excel VBA.

Thanks Haluk, I will look into this for the future, my database skills are super rusty though! However I really need a quick solution in the short term.

[Haluk]

May be your co-workers can input their own data into their own Excel file placed in a shared folder and your VBA code can get the related data from each of the file without opening them (by using ADO) and dump into your main workbook.