Articles

Solved Protected WBs open and update with VBA but do not close

December 26, 2012 at 12:59:13
Specs: Windows XP

I have three wbs, Accounts, Review, Quality. All three share a common worksheet (Raw Data) where colum a is account name. The three workbooks are for the three teams (Accounts, Review, Quality) to do their part of the account information. When the account is opened, Accounts uses a2-r2 to fill in their fields. This info should be fed into the Review WB so when Reviews takes over, using s2-w2, they see what Accounts did and their (Review's) info needs to feed onto Account's sheet, too. Both of their info needs to show up on Quality so they can complete the process.

I got this script here from DerbyDad (sub Open_Protected_File()
Workbooks.Open Filename:= _
"\\MyPath\Users\UserName\My Documents\MyFile.xlsm", Password:="Secret"

Workbooks.Open Filename:= _
"\\MyPath\Users\UserName\My Documents\MyOtherFile.xlsm", Password:="Secret"
End Sub)

The script works when I run the macro and updates all three workbooks BUT, then it leaves the workbooks open. I think I'm missing something! Does anyone know of a way to close the workbooks after they update the other folders?


See More: Protected WBs open and update with VBA but do not close

Report •


✔ Best Answer
December 28, 2012 at 09:56:26

I created 4 workbooks, all stored on the same network drive.

qwz.xlsm contains the macro shown to open q.xlsx, w.xlsx and z.xlsx and then close them.

qwz.xlsm pulls data from the q, w and z workbooks with a simple formula:

e.g. ='\\users\User_Name\My Documents\[q.xlsx]Sheet1'!$A$1

The q, w and z workbooks pull data from each other in a similar fashion. All 4 books are password protected.

When I run the code below, the q,w, and z workbooks open, all 4 workbooks update and the q,w and z workbooks close.

Note: As I said before, I can't replicate your exact scenario, so I don't know exactly how you are performing your task. What I have found is that when I open the qwz workbook, it wants the passwords for the other 3 password protected workbooks because of the links to those password protected workbooks. I have to cancel the password request in order to test the macro.

If you are not getting those password requests as soon as you open the Quality workbook, you are obviously doing something differently than I am.

Sub Update_4_Workbooks()
    Workbooks.Open Filename:="\\users\User_Name\My Documents\z.xlsx", _
       Password:="Secret"
    Workbooks.Open Filename:="\\users\User_Name\My Documents\w.xlsx", _
       Password:="Secret"
    Workbooks.Open Filename:="\\users\User_Name\My Documents\q.xlsx", _
       Password:="Secret"
    Workbooks("q.xlsx").Close SaveChanges:=True
    Workbooks("w.xlsx").Close SaveChanges:=True
    Workbooks("z.xlsx").Close SaveChanges:=True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
December 26, 2012 at 13:39:08

Hi

You can try these commands after you've done what you need to do with the files.

Workbooks(3).Close SaveChanges:=False
Workbooks(2).Close SaveChanges:=False

or - if there are or will be more workbooks involved you can do this

NoWBs = Workbooks.Count

For i = 2 To NoWBs
    Workbooks(2).Close SaveChanges:=False
Next

Change the False to True if you have made changes in the files that you want to keep.

The order is important. If you've opened 2 WBs you would close 3 then 2. If you've opened 3 WBs you would close 4 then 3 then 2. (1 being the original workbook from which you launched the VBA script)

I haven't read your previous posts in detail but have you accounted for the possibility that the workbooks you open in the VBA script are already opened by someone else - in which case you would be prompted to open them Read Only. If that does happen you won't be able to make changes in those WBs.


Report •

#2
December 26, 2012 at 16:15:28

Before posting any more VBA code in this forum please click on the blue line at the end my posts and read the instructions found via that link.

Regarding your question, you can also close the workbooks by name, but you have to leave the path off:

Workbooks("MyOtherFile.xlsm").Close SaveChanges:=False

However, I am concerned with the timing of the overall process. If you simply add code to close the workbooks immediately after the code to open them, I don't know if all of the links will update fast enough. Since there is no way for me to replicate your exact environment, only you can test it to see if it works.

If it doesn't work, you may have to add a wait loop or even a separate macro to close the workbooks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
December 28, 2012 at 06:12:01

Thanks for taking the time to respond! And the issue continues....

When the Quality wb opens and I run the macro, the other two workbooks open, and all three of them get the updates. Then all three stay open with prompts that open to ask for passwords though the workbooks are already open.

Does this make sense? I don't understand why this is happening so maybe someone can explain?


Report •

Related Solutions

#4
December 28, 2012 at 09:56:26
✔ Best Answer

I created 4 workbooks, all stored on the same network drive.

qwz.xlsm contains the macro shown to open q.xlsx, w.xlsx and z.xlsx and then close them.

qwz.xlsm pulls data from the q, w and z workbooks with a simple formula:

e.g. ='\\users\User_Name\My Documents\[q.xlsx]Sheet1'!$A$1

The q, w and z workbooks pull data from each other in a similar fashion. All 4 books are password protected.

When I run the code below, the q,w, and z workbooks open, all 4 workbooks update and the q,w and z workbooks close.

Note: As I said before, I can't replicate your exact scenario, so I don't know exactly how you are performing your task. What I have found is that when I open the qwz workbook, it wants the passwords for the other 3 password protected workbooks because of the links to those password protected workbooks. I have to cancel the password request in order to test the macro.

If you are not getting those password requests as soon as you open the Quality workbook, you are obviously doing something differently than I am.

Sub Update_4_Workbooks()
    Workbooks.Open Filename:="\\users\User_Name\My Documents\z.xlsx", _
       Password:="Secret"
    Workbooks.Open Filename:="\\users\User_Name\My Documents\w.xlsx", _
       Password:="Secret"
    Workbooks.Open Filename:="\\users\User_Name\My Documents\q.xlsx", _
       Password:="Secret"
    Workbooks("q.xlsx").Close SaveChanges:=True
    Workbooks("w.xlsx").Close SaveChanges:=True
    Workbooks("z.xlsx").Close SaveChanges:=True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question