Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Excel files in cascade through VBa
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Excel files in cascade through VBa
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? -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO CASCADE TWO WORKSHEET IN EXCEL | Excel Worksheet Functions | |||
show most recent files first when opening excel files | Excel Discussion (Misc queries) | |||
Opening Quattro Pro for Windows files (*.WB1 Files) using Excel 20 | Excel Discussion (Misc queries) | |||
Excel auto cascade Info | New Users to Excel | |||
Excel needs to be able to cascade in multiple windows like word! | Excel Worksheet Functions |