+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    10-31-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    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.

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    852

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

    Quote Originally Posted by Abominator View Post
    .....
    ...
    ..

    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.


    .
    Last edited by Haluk; 05-07-2020 at 09:37 AM.

  3. #3
    Registered User
    Join Date
    10-31-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    Quote Originally Posted by Haluk View Post
    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.

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    852

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Opening Password-protected workbook, but still being prompted for password
    By dpmcgonigle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2015, 12:34 AM
  2. Reserved workbook ignores macros supplied password
    By ashokunbi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2013, 04:31 PM
  3. Why am I prompted for the workbook password for each worksheet?
    By Leon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  4. [SOLVED] Why am I prompted for the workbook password for each worksheet?
    By Leon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] Why am I prompted for the workbook password for each worksheet?
    By Leon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  6. [SOLVED] Why am I prompted for the workbook password for each worksheet?
    By Leon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] Why am I prompted for the workbook password for each worksheet?
    By Leon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-30-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1