Passwords on files used in calculation

[LauriS]

Here's the situation - user has 15+ files that have unique passwords. They
also have one main file that they use to sum up the individual files.

When they open the main file it asks for the password for every file used in
the calc's. They want to avoid that.

My thought was to set up code to open all the individual files, using the
passwords, when the main file is opened. But my initial testing is showing
that the main file wants to update the linked info (the calc's) before it
runs the code in the Workbook_Open event.

Is there a better way to do this?

If not, can someone help me with the code. I have this in the Workbook_Open
sub of the main file and it gives me an Object Required error:

Private Sub Workbook_Open()
Dim wbk As Workbook

Set wbk =
Workbook.Open(Filename:="M:\XLDATA\Herb\Testing\Test1\test1.xls",
password:="test1")

End Sub

Thanks!!

Lauri

 

[Jim Cone]

Workbook.Open...
- should be-
Workbooks.Open...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"LauriS"
wrote in message
Here's the situation - user has 15+ files that have unique passwords. They
also have one main file that they use to sum up the individual files.

When they open the main file it asks for the password for every file used in
the calc's. They want to avoid that.

My thought was to set up code to open all the individual files, using the
passwords, when the main file is opened. But my initial testing is showing
that the main file wants to update the linked info (the calc's) before it
runs the code in the Workbook_Open event.

Is there a better way to do this?

If not, can someone help me with the code. I have this in the Workbook_Open
sub of the main file and it gives me an Object Required error:

Private Sub Workbook_Open()
Dim wbk As Workbook

Set wbk =
Workbook.Open(Filename:="M:\XLDATA\Herb\Testing\Test1\test1.xls",
password:="test1")

End Sub

Thanks!!

Lauri

 

[LauriS]

Jim,

Thanks - that fixed the error msg I was getting.

But it doesn't work as I had hoped. The formulas try to update BEFORE the
Workbook_Open event happens.

At this point I'm thinking that I have to open all the individual files THEN
open the main file.

Does anyone know of another way??

Lauri

 

[Jim Cone]

Lauri,
Some ideas if you want to experiment...
Use this code line first: "ThisWorkbook.UpdateRemoteReferences = False"
Or this line: Application.Calculation = False
-or maybe-
Use a dummy workbook that has calculation turned off to open your workbook.
-or maybe-
Put your code in an Auto_Open sub (in a standard module, not in the
ThisWorkbook module) and see if you get different results.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"LauriS"
wrote in message
Jim,
Thanks - that fixed the error msg I was getting.
But it doesn't work as I had hoped. The formulas try to update BEFORE the
Workbook_Open event happens.
At this point I'm thinking that I have to open all the individual files THEN
open the main file.
Does anyone know of another way??
Lauri

 

[Jim Cone]

One more,

Use a dummy workbook to...
open the fifteen files
open the master workbook.
close itself.

Jim Cone



"Jim Cone"
wrote in message
Lauri,
Some ideas if you want to experiment...
Use this code line first: "ThisWorkbook.UpdateRemoteReferences = False"
Or this line: Application.Calculation = False
-or maybe-
Use a dummy workbook that has calculation turned off to open your workbook.
-or maybe-
Put your code in an Auto_Open sub (in a standard module, not in the
ThisWorkbook module) and see if you get different results.