Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Opening a network excel file in VBA takes so long it prompts for password

    Morning All,

    I am opening 6 files in a share drive and then copying the contents onto 1 master book to continue my code. I have embeded the password into the code but as it takes so long to open it seems to skip past that part of the code.

    The problem I have is the first 1-2 files appear to open the file ok but when it gets to the 3-4 file it prompts me for the password, although I have coded this into the report.

    Is there a way around this type of issue:

    ''''''Open the data workbook
    Path = "\\NETWORKDRIVE\INT$\SHARED\Jan P And L - UK-Europe.XLSX"
    Workbooks.Open Filename:=Path, Password:="Monday"
    '''''End of Open Workbook


    Regards,

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Dean

    The way I like to consider this is to think of vba as a speed demon that wants to get to the end of the routine as fast as it can and it doesn't care what is going on in the background.
    So, to make vba 'take a breather and let Excel catch up with it', you us the DoEvents command.
    DoEvents passes control to the operating system. Control is then returned after the operating system has finished processing the events in its queue.
    So, use something like this:
    ''''''Open the data workbook
    Path = "\\NETWORKDRIVE\INT$\SHARED\Jan P And L - UK-Europe.XLSX"
    Workbooks.Open Filename:=Path, Password:="Monday"
    DoEvents
    '''''End of Open Workbook

    This will ensure that the network file has been opened before continuing with the rest of the vba procedure.
    Try it.

    Another thing you can do is use Excel's binary file format .xlsb rather than .xlsx or .xlsm
    The binary file format is much more compact, meaning smaller file sizes therefore faster retrieval and less network traffic.
    The binary file .xlsb format also allows macros to be included in the file too.

    zeddy

  3. #3
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for the reply, i thought as much, excel just likes to get the the end as quick as possible.

    I've tried the DoEvents command but no joy.

    I cant use the XLSB command as these are files that are on a shared drive and i'm not the "master" of them.

    Back to the drawing board for me i think.

    Thanks for your help.

  4. #4
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 406 Times in 378 Posts
    Sounds like you have lost some needed access to something, and that's what is delaying things -- it is trying to connect but can't. And it tries for a couple of minutes before giving up on it. The tipoff is that it finally asks for a password.

    There may be a server down somewhere, or a connection may be broken. Or maybe the needed password has changed, but your Excel file remembers the previous password.

    Microsoft tends to bundle the password with whatever you are doing network-wise, probably to make things easier for you. This is fine until the password changes, because the item you were working on doesn't always get updated with the new password. When that happens, you will see the kind of behavior you are seeing.

    For instance, on our network, users are required to change their passwords once per month or more often. When they log in, they can click on their name (and then enter their username and password), or they can click on "Other User" (and then enter their username and password). If they have just changed their password, and then they click on their name, and then type in their username and password, SOMETIMES IT REJECTS THE NEW PASSWORD! If they click on "Other User", it works every time. My guess is that when they click on their own name, Microsoft bundles their last known password with their login information, rather than simply using whatever the user logged in with. But if they select "Other User", no assumptions are made; it uses whatever the user types in at that moment.

    So much of Microsoft's networking is client-centric rather than server-centric. In other words, many aspects of Microsoft networking aren't managed centrally.
    Last edited by mrjimphelps; 2013-05-17 at 10:31.

  5. #5
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    I dont think iwas clear in my issue so apolgies, but thasnks for replying.

    The network drive is in the US and we are based in London, just opening a file takes while due to the connection time but when operning in a macro it opens but then request for me to put ther password in manually, i.e. it has skipped the bit of code about password as the file took so ling to open the macro passes this peice.

    I wondered if there was any cleaver way to say, wait until the password promt box is visable before trying to enter the password.

    Thanks

  6. #6
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 406 Times in 378 Posts
    Quote Originally Posted by stimpsond1 View Post
    Hi,

    I dont think iwas clear in my issue so apolgies, but thasnks for replying.

    The network drive is in the US and we are based in London, just opening a file takes while due to the connection time but when operning in a macro it opens but then request for me to put ther password in manually, i.e. it has skipped the bit of code about password as the file took so ling to open the macro passes this peice.

    I wondered if there was any cleaver way to say, wait until the password promt box is visable before trying to enter the password.

    Thanks
    My guess is that the drive is unavailable and therefore you get all of the delays while Windows is going through the process of opening it.

    Suppose you open the drive first in My Computer before opening your Excel spreadsheet. This would make sure the drive is open and ready when you need it.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Perhaps it would be quicker and more efficient to use Excel vba to Copy the files from the remote drive to a local drive (e.g. C:\xxxx ), and then perform the merge from the local drive.

    zeddy

Posting Permissions

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