Vba macro for opening/updating password protected excel workbook

[NEWTOMACROS2]

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:

[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

 

[Fluff]

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

If the files are in the same folder as the master, try

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]

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

If the files are in the same folder as the master, try

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]

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]

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"

 

[Fluff]

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]

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]

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]

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

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

 

[NEWTOMACROS2]

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.