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???

As this is a completely different question, can you please start a new thread.

 

[JoeMo]

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

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"

I'd be surprised if that works as it appears it's missing a path separator. ThisWorkbook.Path does not include a path separator. That would be a backslash on a PC and something else on a Mac.

To be safe try using:

Pth = ThisWorkbook.Path & Application.PathSeparator

 

[NEWTOMACROS2]

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

Ok. I'll start a new thread.

Thanks Fluff and JoeMo!!