How to grab time stamp for all the data cells in Excel 2007 as source file and generating a mail for that data using SSIS 2005

Question

Hi,

    I have a Excel 2007 file which consists 7 tabs and this file accessed by 10 users per day.Only 1 user can modify this file at a time.

Here is the my probem:
1) We have to capture Time stamp and date when the user was accced this file ( Resource accessed with time stamp)
2) We have to capture Time stamp and date for at which column user was modified (modified column or cell neame with time stamp)

we have to take all of the above information in excel(including time stamp) data as a source file in SSIS 2005 and need to send an email to all the users when the SSIS  package was ran.

Mail should have this information(example)

S.No     Resource  Accessedtime             Changed Data
1           john        11/5/09 2:30 PM         Training
2           Kim         11/6/09 5:30 PM         Leaves

here accessed time is last modified time and changed data is last modified column/cell

How to implement above task using sourcefile as Excel 2007 using Sqlserver integration services 2005.
Please provide me guidelines to above process.

Thanks for your help in advance.
 
mahi

---

mahi

Answer 1

Open the file in Excel, on the review tab, click Track Changes

In the Highlight Changes dialog box, check Track changes while editing

Check When, and then select All
Check Who, and then select Everyone

Check List changes on a new sheet

This will track changes to the workbook, by user, and store them on a new worksheet in the workbook. You can then use SSIS to access the History worksheet.

Answer 2

Not sure if OP has implemented Pendleton's suggestion but voting as helpful.
Thanks Pendleton, I did not know this.
 

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 3

This is an excellent ( and I wasn't aware of it!!). However, this approach will need the Track Changes option to be manually enabled by someone (atleast teh first time around). Why not write a VB macro in the excel to populate a hidden sheet with "User Name", "Changed Date" and the "Column Name" on the CellChange event?? You can then read this sheet just like the History sheet maintained by Excel 2007 as described above. Just another possible solution to the original problem. :) Cheers!! M.

Answer 4

Hi Pendleton,

                     I got the idea how to generate the History work sheet. i am not aware of how to access history workshhet (excel 2007) as a source by using SSIS 2005.

                     Here while saving history workshhet its automatically closed. so how we can access this History workshhet (excel 2007) as a source by using SSIS 2005.

                    Please help me on this

---

mahi

Answer 5

Hi Mahendra,
Read more here.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 6

I'm sorry guys, I forgot to post the code...

Once you have "Track Changes" enabled, create a new Excel Workbook, call it Change History.xls (or whatever you want).

Click on Developer

Click on Visual Basic

In the Visual Basic designer, click Insert, then Module

Paste the following code into the editor:

Sub Save_History_Worksheet()

    Dim ws As Worksheet
    Dim wb As Workbook
    
    Set wb = ActiveWorkbook
    
    With wb
    
        If .Name = ThisWorkbook.Name Then
            
            MsgBox Title:="Error", Prompt:="This macro will not run in the same workbook that contains the macro."
            Exit Sub
            
        End If
        
        If .KeepChangeHistory = False Then
        
            MsgBox Title:="Error", Prompt:="Track Changes feature must be turned on for active Workbook."
            Exit Sub
            
        End If
         
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
         
        .Save
        
        .HighlightChangesOptions When:=xlAllChanges, Who:="Everyone"
        
        .ListChangesOnNewSheet = True
        .HighlightChangesOnScreen = True
        ' You can modify what columns are selected here.            
        .Worksheets(.Worksheets.Count).Columns("A:I").Copy
        
    End With
     
    ThisWorkbook.Activate
    ThisWorkbook.Worksheets.Add
        
    Set ws = ActiveSheet
    ws.Range("A1").PasteSpecial
    
    ThisWorkbook.Save
    
    Application.CutCopyMode = False
    
    wb.Activate
    
    wb.ListChangesOnNewSheet = False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    Exit Sub

End Sub

Save the .xls file but do not close it.

Open the Workbook that you have enabled Track Changes for and run the macro Save_History_Worksheet

Both Workbooks must be open at the same time, and you cannot run the macro from the Change History Workbook.

This will save the History worksheet in the Change History Workbook which you can then load into SQL Server. You probably could automate this with a little more work.

Regards,

dp

Answer 7

Hi Pendleton,

                   Your code useful to me.i have another tricky part. i ur need help on this..........

 Excel Sheet1 contains like below data

   A     B        C      D       E       F      G

 1  SRNO    SCR     SM     AS      JM      MP    Total

 2   1     TASK1      4      2        5       6        17
 3   2     TASK2      3      7        8       5        23
 4   3     TASK3      8      5        6       2        21
 5   4     TASK4      2      4        4       5        15
 6   5     TASK5      6      6        2       8        22

 A to G are default excel rows names
 Verticular 1 to 6 are default excel column names
 COlumn SR NO: Serial Number
 COlumn SCR  : Diffrent task names
 COlumns from SM to Mp are diffrent resource names
 COlumn Total: calculate the hours of diffrent resources based on particular tasks like above

 BELOW  VB code generate history of my excel sheet. that sheet o/p like below

 USERNAME   DATE_CHANGE     TIME_CHANGE         CELL_CHANGED       OLD_VALUE          NEW_VALUE

 SM            11/11/2009          10:03:04 AM                     $c$4                      3                     8
 AS            11/11/2009          10:04:22 AM                     $D$5                      6                     4
 JM            11/11/2009           10:05:26 AM                    $f$3                        5                    5
 MP            11/11/2009          10:07:00 AM                     $c$6                       7                    6
 
 Oldvalue & new vlues are u can understand by name.

 i had above implementation.as per above ur referece code. i had modified like below to my requirement.

Code used:
-------------------

  Dim vOldVal

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrng As Range
    
    ' Get the Used Range from Weekly Individual Billing Sheet
    Set myrng = Sheet1.UsedRange 'Range(Sheets("Sheet1").[A1], Sheets("Sheet1").[A10])
   
    ' Stop the Screen Flickering effect
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
           
    ' Report Sheet and Counter variable
    Dim wsReport As New Worksheet
    Dim lCount As Integer
   
    ' Loop through the workbook and fetch the report sheet if available
    For lCount = 1 To Sheets.Count
        If UCase(Sheets(lCount).Name) Like UCase("Report History") Then
            Set wsReport = Worksheets(Worksheets.Count)
            Exit For
        End If
    Next lCount
    'lCount = lCount - 1
         
    ' Create a new sheet - Report Sheet
    If lCount <> ActiveWorkbook.Worksheets.Count Then
        'Insert a new worksheet after the last worksheet in the active workbook.
        ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
        ActiveWorkbook.Sheets(Worksheets.Count).Name = "Report History"
        Set wsReport = Worksheets(Worksheets.Count)
        wsReport.Protect Password:="Secret"
    End If
    
    ' Check for Headings
    If wsReport.Range("A1") = vbNullString Then
        wsReport.Unprotect Password:="Secret"
        wsReport.Range("A1:F1") = Array("USER NAME", "DATE OF CHANGE", "TIME OF CHANGE", "CELL CHANGED", "OLD VALUE", "NEW VALUE")
    End If
   
    ' Copy the Data
    If Not Intersect(myrng, Target) Is Nothing Then
        wsReport.Unprotect Password:="Secret"
      wsReport.Cells(65536, 1).End(xlUp).Offset(1, 0) = Application.UserName
      wsReport.Cells(65536, 1).End(xlUp).Offset(0, 1) = Date
      wsReport.Cells(65536, 1).End(xlUp).Offset(0, 2) = Time
      wsReport.Cells(65536, 1).End(xlUp).Offset(0, 3) = Target.Address
      wsReport.Cells(65536, 1).End(xlUp).Offset(0, 4) = vOldVal
      wsReport.Cells(65536, 1).End(xlUp).Offset(0, 5) = Target.Value
    End If
   
    wsReport.Cells.Columns.AutoFit
    wsReport.Protect Password:="Secret"
   
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
   
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    vOldVal = Target
End Sub

 

 now i need to add Cell_CHANGED column with respect to SCR column. here i need to change above result like below

 

 USERNAME   DATE_CHANGE     TIME_CHANGE         CELL_CHANGED       OLD_VALUE          NEW_VALUE

 SM            11/11/2009          10:03:04 AM                   TASK3                     3                     8
 AS            11/11/2009          10:04:22 AM                   TASK4                     6                      4
 JM            11/11/2009           10:05:26 AM                   TASK2                    5                      5
 MP            11/11/2009          10:07:00 AM                   TASK5                     7                      6
 

 in 1 line to say that i need to capture Particular tasks(SCR column) based on corresponding cell changed.

like resource SM enetered data at cell  $c$4  its displaying 1st o/p data(1st o/p) but i need  that when ever data entered at any CELL in SHEET 1 i need to capture particular task name isted of cell like abobe o/p

 i tried with diffrent codes but not worked to me.

 plz help on VB/MACRO on  this

                  

 

 

---

mahi

Answer 8

Hi Mahendra.

I'll take a look, but I'm probably as good at programming Excel as you are...

Have you tried the Excel forums, this kind of stuff is easy for them.

Regards,

D.