Vba macro for opening/updating password protected excel workbook

NEWTOMACROS2

New Member
Joined
Jun 4, 2018
Messages
8
Hello members,

I'm new to using macros and have searched this forum and several other forums for a way to update a password protected master workbook (named "WKLY") linked to 10 other workbooks in the same folder. I tried various VBA codes and due to my inexperience with VBA, I was not able to edit them for my purpose. I simply recorded what I wanted done, copied/pasted the task over and I got the macro to run when I opened my workbook. The master workbook updates after all linked workbooks are opened and then they are automatically closed. However, when my supervisor tries to open the master workbook from her desk, she gets the following error: Run time error: '1004' "WKLY.XLSX" could not be found. But the file is in the same folder so I dont understand why I am getting this error. I dont want to start over and relink everything to a new workbook, so can someone look over my code and see what I am doing wrong? (see below)

My non macro enabled master workbook is named WKLY but my macro enabled workbook is named WKLY 2. Is this an issue? Can I use the same name for my macro enabled workbook as my non macro enabled workbook ( WKLY)? How does the macro enabled workbook change the non macro enabled workbook? Do I need the non enabled macro workbook? Can I delete it or is it still being referenced when I run my macro?

Thanks for your help...

Here is the macro:

Code:
[I]Private Sub Workbook_Open()[/I]

[I]  Application.DisplayAlerts = False[/I]
[I]  Application.ScreenUpdating = False[/I]
[I]  Application.Calculation = xlCalculationManual[/I]

[I]    Workbooks.Open Filename:="WKLY.xlsx", ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 1.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 2.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 3.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 4.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 5.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 6.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 7.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 8.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 9.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]
[I]    Workbooks.Open Filename:=" 10.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"[/I]


[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]
[I]    ActiveWindow.Close SaveChanges:=False[/I]


[I] Application.ScreenUpdating = True[/I]
[I] Application.Calculation = xlCalculationAutomatic[/I]


[I]End Su[/I]b
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,919
Office Version
  1. 365
Platform
  1. Windows
Re: Vba macro for opening/updating password protected excel workbook help???

If the files are in the same folder as the master, try
Code:
Private Sub Workbook_Open()
   [COLOR=#0000ff]Dim Pth As String[/COLOR]
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
  [COLOR=#0000ff] Pth = ThisWorkbook.Path[/COLOR]
   Workbooks.Open FileName:=[COLOR=#0000ff]Pth & "\[/COLOR]WKLY.xlsx", ReadOnly:=True, Password:="XXX"
   Workbooks.Open FileName:=[COLOR=#0000ff]Pth & "\ [/COLOR]1.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"
Updating the other files in the same way
 

NEWTOMACROS2

New Member
Joined
Jun 4, 2018
Messages
8
Re: Vba macro for opening/updating password protected excel workbook help???

If the files are in the same folder as the master, try
Code:
Private Sub Workbook_Open()
   [COLOR=#0000ff]Dim Pth As String[/COLOR]
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
  [COLOR=#0000ff] Pth = ThisWorkbook.Path[/COLOR]
   Workbooks.Open FileName:=[COLOR=#0000ff]Pth & "\[/COLOR]WKLY.xlsx", ReadOnly:=True, Password:="XXX"
   Workbooks.Open FileName:=[COLOR=#0000ff]Pth & "\ [/COLOR]1.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="XXX"
Updating the other files in the same way

Thanks for the quick response Fluff... but I'm not sure how to use the code you provided.

I added it verbatim and got another error: MS cannot access the file 'Z:\WKLY Z:\WKLY\MASTER SHEET.XLSX.

The 1st WKLY is the folder the master workbook is in N the 2nd WKLY is the full path.

Let me know.

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,919
Office Version
  1. 365
Platform
  1. Windows
Re: Vba macro for opening/updating password protected excel workbook help???

Is the code you supplied all the code, or have you removed some of it?
 

NEWTOMACROS2

New Member
Joined
Jun 4, 2018
Messages
8

ADVERTISEMENT

Re: Vba macro for opening/updating password protected excel workbook help???

Is the code you supplied all the code, or have you removed some of it?

Hey Fluff, that's all the code. I just omitted file names. Here it is:


Private Sub Workbook_Open()


Dim Pth As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Pth = ThisWorkbook.Path


Workbooks.Open Filename:=Pth & "Z:\PMG WKLY TRACKER\MASTER SHEET.xlsx", ReadOnly:=True, Password:="Password1"


Workbooks.Open Filename:=Pth & "Z:\PMG WKLY TRACKER\PMG TRACKER 1.xlsx", UpdateLinks:=3, ReadOnly:=True, Password:="Password2"
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,919
Office Version
  1. 365
Platform
  1. Windows
Re: Vba macro for opening/updating password protected excel workbook help???

As you have the full file path in the code, I cannot see any reason why you would get the error.
 

NEWTOMACROS2

New Member
Joined
Jun 4, 2018
Messages
8

ADVERTISEMENT

Re: Vba macro for opening/updating password protected excel workbook help???

As you have the full file path in the code, I cannot see any reason why you would get the error.

Code still doesn't work. Anything else I can try?
 

NEWTOMACROS2

New Member
Joined
Jun 4, 2018
Messages
8
Re: Vba macro for opening/updating password protected excel workbook help???

Hey Fluff, I just noticed my super network drive was mapped differently. It should work now. Thanks again for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,919
Office Version
  1. 365
Platform
  1. Windows
Re: Vba macro for opening/updating password protected excel workbook help???

Glad you got it sorted & thanks for letting us know.
 

NEWTOMACROS2

New Member
Joined
Jun 4, 2018
Messages
8
Re: Vba macro for opening/updating password protected excel workbook help???

I have another task I need my code to do.... I linked my slave wb to my master wb in such a way that if I change the title of a cell on the master wb it is supposed to change that cell in the slave wb.

When I open the master wb, it gets updated with the data my super needs but the slave wb wont update any changes made on the master wb. Can someone help me figure this part out? Both the master/slave wb are password protected and every slave wb has a different password.
 

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top