Opening Excel files in cascade through VBa

[Patrick M.]

I have developped a Planification Tool that allows to
- record people in our organization
- plan absences and tasks for those people
- produce headcount reporting

The system is based upon:
- a main staff database, an excel file containing all
necessary information about our people. This file is
potected by two passwords to open it and to read it

- a number of Planning files (Excel) to plan absences and
tasks. Those files have no passwords, an event
Workbooks_Open captures the user-id of the person opening
the file, then the event opens the main staff database and
check the authority of this person.

- a separate Excel file allowing the production of
reports, which, in some cases, needs to open the Planning
files.

My problem:
Although the event Workbooks_Open works perfectly when a
user wants to open a Planning file, the command in that
event to open the main staff database seems to be ignored
when the event is activated through the production of one
of the reports. It makes me think that cascade file
opening through macros is not allowed with Excel.

Does anybody already encountered this situation and/or
does anybody could help me to fix this?

We are using Excel 97 on a Windows NT LAN.

Here is the code of the Workbooks_Open event of a Planning
file:


Private Sub Workbook_Open()

Application.DisplayFormulaBar = False
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

' Identify the user-id
Dim lpBuff As String * 25
Ret = GetUserName(lpBuff, 25)
Username = Strings.Left(lpBuff, InStr(lpBuff,
Strings.Chr(0)) - 1)

' Look in resource management the status of the user for
the current file
If Username <> "ESECPM" Then
Worksheets("Wait").Visible = True
Worksheets("Wait").Activate
Cells(1, 1).Select
Application.DisplayAlerts = False
Application.Screenupdating = False
V_Path = ActiveWorkbook.Path & "\"
V_File = ActiveWorkbook.Name
V_Length = Len(V_File)
V_File = Strings.Mid(V_File, 1, V_Length - 4)
Workbooks.Open FileName:=V_Path & "Resource
management.xls", ReadOnly:=True, password:="ICANITO"
Workbooks("Resource management.xls").Activate
Workbooks("Resource management.xls").Worksheets
("Staff").Activate
Workbooks("Resource management.xls").Worksheets
("Staff").Unprotect password:="Connexion"
Workbooks("Resource management.xls").Worksheets
("Staff").Cells(2, 1).CurrentRegion.Sort key1:=Range
("Dateout"), order1:=xlAscending, key2:=Range("ID_1"),
order2:=xlAscending, header:=xlYes
Cells(2, Range("ID_1").Column).Select
Do Until ActiveCell = Username
ActiveCell.Offset(1).Select
Loop

If Cells(ActiveCell.Row, Range("Use").Column) <>
V_File And Cells(ActiveCell.Row, Range("Use").Column)
<> "Division" And (Cells(ActiveCell.Row, Range
("Main_Group").Column)) <> "137" Then
MsgBox prompt:="You are not authorized to use
this file. The file will close", Buttons:=vbCritical,
Title:="Security violation"
ActiveWorkbook.Close savechanges:=False
ActiveWorkbook.Close savechanges:=False
ElseIf Cells(ActiveCell.Row, Range
("Levels_Staff").Column) = "" Then
ActiveWorkbook.Close savechanges:=False
ActiveWorkbook.ChangeFileAccess
Mode:=xlReadOnly
Else
ActiveWorkbook.Close savechanges:=False
' ActiveWorkbook.ChangeFileAccess
Mode:=xlReadWrite
End If
Application.DisplayAlerts = True
Call P_Sheet_Hide
End If

End Sub

The command that sems to be ignored is:
Workbooks.Open FileName:=V_Path & "Resource
management.xls", ReadOnly:=True, password:="ICANITO"

 

[Bob Phillips]

Patrick,

A cursory look at the code suggests that it look okay. There is certainly
nothing wrong in the principle of opening other workbooks ( I just setup a
test and File A opened File B which opened File C which opened File D, all
worked okay)

Are you getting an error, or is it just not opening? If you are, what is the
error?

Have you stepped through the code, is the user returned as the code tests?

Where is the GetUserName API declared?