+ Reply to Thread
Results 1 to 8 of 8

Open Workbook if closed, keep open if open, insert password

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    12

    Open Workbook if closed, keep open if open, insert password

    Hey all,

    Currently I have 1 workbook pooling from 6 other workbooks, all of which are password protected. So i was able to find some code that will open those worbooks, enter the password, and then close them in order to update the cells in the main summary workbook. However this presents a scenario where someone could already have a workbook open, and could have it closed on them and thus lose their data. Here is my code as it stands now:

    Please Login or Register  to view this content.

    So for one workbook the code would be:

    Please Login or Register  to view this content.
    All of the workbooks are hosted on a sharepoint and accessed by multiple people. So it would be something like:

    Please Login or Register  to view this content.
    I realize the details of that list bit of code are probably terrible but hopefully it helps explain what i want. I'm really new to VBA so please bear with me.

    Thanks,
    Jwahl

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Open Workbook if closed, keep open if open, insert password

    However this presents a scenario where someone could already have a workbook open, and could have it closed on them and thus lose their data.

    No, that's not going to happen. If someone else has the workbook open full access, you will get a prompt that the workbook is reserved, do you want to be notified or open a read only copy of the file. If you open a read only copy it will not affect the "live" copy which is being updated.

    You'd be better off opening the files read only and not being notified if they are in use.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Open Workbook if closed, keep open if open, insert password

    Ok so that doesn't seem to be the case. I opened and checked out Test1.xlsx and made some changes without saving. Then I checked out and opened Test Summary (which pulls data from Test1-6). I clicked 'don't update links' and then it ran the VBA script. This closed the Test1 workbook but kept it checked out to me. I lost my changes when i opened it again. I was not prompted to open a read-only version.

    I aprecitate the help,
    Jwahl

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Open Workbook if closed, keep open if open, insert password

    My apologies. I missed the reference to Sharepoint.

    However, I assume that you can still open a read only copy of the file, get the data that you want and close it.

    Again, sorry if that is untrue ... I have no familiarity with the nuances of Sharepoint folders and file.


    Regards, TMS

  5. #5
    Registered User
    Join Date
    06-27-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Open Workbook if closed, keep open if open, insert password

    Well if the file is already checked out to me, I have permissions to write to it, and the file when opened will automatically be full access. I don't see a way to open a read-only copy when i have it checked out, unless there is a command in VBA that would do this.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Open Workbook if closed, keep open if open, insert password

    As I said, I know nothing about Sharepoint. If you only want to read a file, do you have to check it out?

  7. #7
    Registered User
    Join Date
    06-27-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Open Workbook if closed, keep open if open, insert password

    No you do not. But in my case, the person already has it checked out because they are updating the information. They then check out the summary file to work on both at the same time, which would close the first file (due to the vba script). This mean that the other 5 files that get opened and closed never get checked out. And it's ok, I'm thankful for all the help i can get.

  8. #8
    Registered User
    Join Date
    06-27-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Open Workbook if closed, keep open if open, insert password

    So back to this topic. Is there a solution if I am using a Share Drive instead of sharepoint? I don't want people to have their work closed if they open the summary workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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