<

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

Published on
10,298 Points
3,598 Views
2 Endorsements
Last Modified:
Approved
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

ArchitecturePic1
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:

 Pic2
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.
  Pic3
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
Pic4In 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

Open in new window


Creating the User Form

 Pic5
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!

 Pic6
Other Important Functions:

Combo Box Code:

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

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

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

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

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 :)
2
Comment
Author:suvmitra
  • 2
3 Comments
 
LVL 9

Author Comment

by:suvmitra
Thank you Sir! Please let me know if anything else is required from my end :)
0
 
LVL 9

Author Comment

by:suvmitra
Bad News are also good for me as I am just trying to share my ideas for the first time and got this platform in EE. Yes, I will be more careful about the contents and presentation parts and also the jargons. Thank you for advising :)
0
 
LVL 15

Expert Comment

by:Eric AKA Netminder
suvmitra,

Congratulations! Your article has been published.

ericpete
Page Editor
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month