Using Excel Workbook in a shared Environment without sharing at all - PART - 1 ;)

Introduction

Nowadays I am getting more dependent upon Microsoft Excel not only because my job demands this but also I am fond of Excel and love to know more things about this wonderful software.

In India, BPO industry is growing very fast and this industry is literally dependent on Excel! However, using Excel in its best way is still a challanging thing to learn for most of the professionals.

Problem Statement
I received complains from different team members when they are trying to use a particular Excel file as shared and keeping the file in a shared directory, trying to update the same file from different locations. The file gets corrupted after some time and hundreds to thousands lines of data ended up as damaged or lost! In BPO industry this is a total loss because team lost the valuable input which helps them to build KPI and SLA for their client!

Example from Google : http://www.vbaexpress.com/forum/showthread.php?t=25401

My Method with the help of Microsoft Excel:

As Excel tends to get corrupted with simultaneous data entry on a particular time from various sources ... so it is better not to use Excel Shared!

However, we need a method which will allow my team to use a particular Excel Application which will ---
A. Allows data entry
B. Collates all the data
C. Prepares Dashboard
D. Ensures no data loss
E. Ensures proper back-up / archiving of data
F. Allows easy access to the data

Architecture
Now as we atleast have a basic idea of what we need to implement, kindly allow me to take you to the next step of this office automation.

A. Preparing the Excel Front End:

Guidance:
* Every individual user will have this file saved at their system.
* There will be dynamic reference for connecting to the back-end drive.
* The Front End will be easy for the End User to make the necessary changes without taking direct help from the developer.

Front-End Interface:

 
Understanding the Front End:

Kindly take some time to review the front-end. It is serving my team's purpose that does not mean you have to follow the same :)

Now, lets see the building blocks for this FE.

1. It has 3 command Buttons "Open", "Update" and "Clear" (will know the purpose of these 3 later).
2. It has 4 Named Ranges "Available Users" as lstList; "Process Activities" as rngList; "Action taken Expenses" as rngList2; "Query Sent" as rngList3.
3. It has 4 cell references "Select User" N20, "Update" N26, "Report" N29 and "Archive" N31.
4. It has a combo box under Select User.

That's It!!

Set right all the building bloks!
1. Set Select User: Set the ListFillRange Property of the combo box as lstList.
 
2. Create a folder say SLA in the shared drive and put the folder path at N26 and N29. These two will have the same path.

3. Create a folder say SLA Arch at your system and pur the folder path at N31.

See some validations
In any multi user environment you have to be sure that data for each individual keeps safe and should apply some basic validation which will prompt the user to ensure the same.

So you can write the below code behind the Open button

Private Sub cmdOpen_Click()
                      'On Error Resume Next
                      Application.ScreenUpdating = True
                      Application.DisplayAlerts = False
                      Application.EnableCancelKey = xlDisabled
                      If cboSelect.Value = "" Then
                      MsgBox "Please choose User", , "Nordic TE SLA Warning :: Choose User"
                      ElseIf ThisWorkbook.Sheets("Control Panel").Range("N20").Value <> ThisWorkbook.Sheets("Control Panel").Range("J15").Value Then
                      MsgBox "Please choose Your Name!", , "Nordic TE SLA Warning :: Choose User"
                      Cancel = True
                      Else
                      frmDataInput.Show
                      End If
                      Application.ScreenUpdating = True
                      Application.DisplayAlerts = True
                      Application.EnableCancelKey = xlInterrupt
                      End Sub

Select all Open in new window

Creating the User Form

 

Creating a form for data entry is best to perform all the validations you want to perform in user level as well as ensuring right data entry.

This data entry form is entirely depending upon you and your team requirement ..create this as you want!

 
Other Important Functions:

Combo Box Code:

Private Sub cboSelect_Click()
                      Worksheets("Control Panel").Range("J15") = Me.cboSelect.Value
                      End Sub

Select all Open in new window

Update Button Code:

Private Sub cmdUp_Click()
                      On Error Resume Next
                      Application.ScreenUpdating = False
                      Application.DisplayAlerts = False
                      Application.EnableCancelKey = xlDisabled
                      
                      Dim wb2 As Workbook, ws As Worksheet, str6 As String, str7 As String, str4 As String, Report_SLA As String, strName As String, str5 As String, strFile As String
                      strName = "SLA_" & ThisWorkbook.Sheets("Control Panel").Range("N20").Value
                      ThisWorkbook.Sheets("Control Panel").Range("N27") = strName
                      str6 = ThisWorkbook.Sheets("Control Panel").Range("N26").Value
                      str7 = ThisWorkbook.Sheets("Control Panel").Range("N27").Value
                      str4 = ".xls"
                      str5 = ".xlsx"
                      
                      Dim strMsg As String
                          strMsg = "Do You want to update the Data file? Proceed only if you have the back up file."
                          strMsg = strMsg & "Click Yes to Continue or No to Discard."
                          If MsgBox(strMsg, vbQuestion + vbYesNo, "Update Data? :: OS TE SLA Information!") = vbYes Then
                      
                      ThisWorkbook.Sheets("Data").Visible = True
                      ThisWorkbook.Sheets("Data").Select
                      ActiveSheet.Range("A2:AZ65536").Delete Shift:=xlUp
                      
                      Report_SLA = (str6 & "\" & str7 & str4)
                      'Set wb2 = Workbooks.Open(Report_SLA)
                      'wb2.Activate
                      With Application.FileDialog(msoFileDialogFilePicker)
                          .AllowMultiSelect = False
                          .InitialFileName = strName
                          .Filters.Add "*.xls files", "*.xls", 1
                          .Filters.Add "*.xlsx files", "*.xlsx", 2
                          If .Show = -1 Then
                              strFile = .SelectedItems(1)
                              Set wb2 = Workbooks.Open(strFile)
                      Set ws = ActiveWorkbook.Sheets("Data")
                      ActiveSheet.Range("A2:AZ65536").Copy
                      ThisWorkbook.Activate
                      frmDataInput.Hide
                      Application.ScreenUpdating = False
                      ThisWorkbook.Sheets("Data").Visible = True
                      ThisWorkbook.Sheets("Data").Select
                      
                      ThisWorkbook.Sheets("Data").Activate
                      
                      ThisWorkbook.Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                              xlNone, SkipBlanks:=False, Transpose:=False
                              Application.CutCopyMode = False
                      ThisWorkbook.Sheets("Data").Range("A1:AZ1").Interior.ColorIndex = 10
                      End If
                      End With
                          Else
                          Exit Sub
                          End If
                      wb2.Activate
                      wb2.Close (False)
                      
                      ThisWorkbook.Activate
                      ThisWorkbook.Sheets("Control Panel").Select
                      frmDataInput.Hide
                      Range("N26").Select
                      ThisWorkbook.Sheets("Data").Visible = xlVeryHidden
                      
                      Application.ScreenUpdating = True
                      Application.DisplayAlerts = True
                      Application.EnableCancelKey = xlInterrupt
                      
                      End Sub

Select all Open in new window

Clear button Code:

Private Sub cmdClear_Click()
                      On Error Resume Next
                      Application.ScreenUpdating = False
                      Application.DisplayAlerts = False
                      Application.EnableCancelKey = xlDisabled
                      Dim strMsg As String
                          strMsg = "Clear all the Data you have?"
                          strMsg = strMsg & " Click Yes to Continue or No to Discard."
                          If MsgBox(strMsg, vbQuestion + vbYesNo, "Clear Report? :: Nordic TE SLA Warning!") = vbYes Then
                      Call Delete_Data
                      ThisWorkbook.Sheets("Control Panel").Select
                      Range("N20").Select
                      ThisWorkbook.Sheets("Data").Visible = xlVeryHidden
                          Else
                          Exit Sub
                          End If
                      Application.ScreenUpdating = True
                      Application.DisplayAlerts = True
                      Application.EnableCancelKey = xlInterrupt
                      End Sub

Select all Open in new window

Behind the Workbook Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
                      On Error Resume Next
                      Application.ScreenUpdating = False
                      Application.DisplayAlerts = False
                      Application.EnableCancelKey = xlDisabled
                      
                      If ThisWorkbook.Sheets("Control Panel").Range("J15").Value = "" Then
                      MsgBox "Please choose User", , "TE SLA Warning :: Choose User"
                      Cancel = True
                      ElseIf ThisWorkbook.Sheets("Control Panel").Range("N20").Value <> ThisWorkbook.Sheets("Control Panel").Range("J15").Value Then
                      MsgBox "Please choose Your Name!", , "TE SLA Warning :: Choose User"
                      Cancel = True
                      Else
                      Dim NM As String
                      NM = Worksheets("Control Panel").Range("J15")
                      Dim objFSO As Object
                      Dim strPath As String
                      Dim strName As String
                      strName = "SLA" & "_" & NM
                      ThisWorkbook.Sheets("Control Panel").Range("N27") = strName
                      Set objFSO = CreateObject("Scripting.FileSystemObject")
                          strPath = Worksheets("Control Panel").Range("N29")
                          If Not objFSO.FolderExists(strPath) Then objFSO.CreateFolder (strPath)
                          ThisWorkbook.Sheets("Data").Visible = True
                          Sheets(Array("Data")).Copy
                          With ActiveWorkbook
                              .SaveAs strPath & "\" & strName
                              .Close False
                          End With
                      Set objFSO = Nothing
                      
                      Dim NM2 As String
                      NM2 = Worksheets("Control Panel").Range("J15")
                      Dim objFSO2 As Object
                      Dim strPath2 As String
                      Dim strName2 As String
                      strName2 = "SLA" & "_" & NM2 & "_" & Format((Date), "dd-mmm-yy")
                      Set objFSO2 = CreateObject("Scripting.FileSystemObject")
                          strPath2 = Worksheets("Control Panel").Range("N31")
                          If Not objFSO2.FolderExists(strPath2) Then objFSO2.CreateFolder (strPath2)
                          ThisWorkbook.Sheets("Data").Visible = True
                          Sheets(Array("Data")).Copy
                          With ActiveWorkbook
                              .SaveAs strPath2 & "\" & strName2
                              .Close False
                          End With
                      Set objFSO2 = Nothing
                      End If
                      ActiveWorkbook.Save
                      Application.ScreenUpdating = True
                      Application.DisplayAlerts = True
                      Application.EnableCancelKey = xlInterrupt
                      End Sub
                      
                      Private Sub Workbook_Open()
                      On Error Resume Next
                      ThisWorkbook.Sheets("Control Panel").Select
                      Worksheets("Control Panel").OLEObjects("cmdOpen").Enabled = True
                      Worksheets("Control Panel").Range("N17").Value = Format((Date), "dd-mmm-yyyy")
                      Worksheets("Control Panel").Range("O17").Value = Format((Now), "hh:mm:ss AM/PM")
                      Worksheets("Control Panel").Range("N20").Select
                      Worksheets("Control Panel").OLEObjects("cboSelect").Object.Value = ""
                      Worksheets("Control Panel").Range("J15") = ""
                      ThisWorkbook.Sheets("Control Panel").Range("isFormActive").Value = ""
                      ThisWorkbook.Sheets("Data").Visible = xlVeryHidden
                      End Sub
                      Private Sub Workbook_Activate()
                      On Error Resume Next
                      If ThisWorkbook.Sheets("Control Panel").Range("isFormActive").Value = True Then
                          frmDataInput.Show
                        End If
                      End Sub
                      Private Sub Workbook_Deactivate()
                      On Error Resume Next
                      If frmDataInput.Visible Then
                          ThisWorkbook.Sheets("Control Panel").Range("isFormActive").Value = True
                          frmDataInput.Hide
                        Else
                          ThisWorkbook.Sheets("Control Panel").Range("isFormActive").Value = False
                        End If
                      End Sub

Select all Open in new window

Get rid of the User Form Trouble!!!
If a user form is open from an excel file you may get complain from the users that they are not able to open other Excel files, they are getting trouble when navigating to other excel files... to get rid of these inconveniences you need to use a cell or named range say "isFormActive". Workbook will understand when and where u need the form to be opened or not ;)

Conclusion:

Now if you are able to follow till now, I think you are now able to create the front end application in Excel which will be providing you the followings:

1. Facility for entering data to the individual sheet.
2. User Level security to ensure right person is entering the right data.
3. when user is saving the workbook a copy of data is getting automatically saved at the users system with current date and a copy of the same data is getting updated in the shared drive folder with the user name.
4. Now as a controller you will be getting seperate file for each user which you can easily acceess from the Sahred folder.
5. You now have the provision to use these individual files to collate data and prepare dashboard.

The job is left now for us is to prepare a Controllers copy which will be collating the data from the shared drive and will be preparing Dashboard!

I decided to publish only the Part-1 of this article as of now as it is getting too much lengthy :(
I am very new to this article writing so kindly forgive me for this poor presentation.

However, I really like to finish the Part-2 where we will see how to prepare the Controller's Copy and get data from various sources to prepare desired Dashboard. Hope you may find this article useful.

Thanks for reading :)