Vb-how To Moniter Changes Made In Excel By Using Vb 6.0 Software

[smugcool]

HI all,

I wanted to monitor changes made in any excel file of each and every cell by various users in networking system.

kindly provide some guidelines how to create the software.

Regards
Anup kumar

[Killer42]

I wanted to monitor changes made in any excel file of each and every cell by various users in networking system.

kindly provide some guidelines how to create the software.

It might be simplest to put something in the Worksheet_Change event of the worksheet(s) in question. For example, you could append an entry to a log (text) file each time.

[smugcool]

It might be simplest to put something in the Worksheet_Change event of the worksheet(s) in question. For example, you could append an entry to a log (text) file each time.

I didn't get dear.

Plz explain this thing in a bit more broadway. As i am very much new to Vb.

[Killer42]

I didn't get dear.

Plz explain this thing in a bit more broadway. As i am very much new to Vb.

Ok, well in this case we'll actually be talking about VBA. That's "Visual Basic for Applications", which is a version of VB built into MS Office applications to do scripting (automating tasks) by writing "macro" code.

Let's have a go at throwing together a simple little example macro for Excel which will append the details of each change made to a worksheet into a text file.

To create this, follow the following steps. (Hopefully they won't vary too much on your system, but we'll see).

• open a new worksheet in Excel.
• Pull down the Tools menu, expand the [b]Macro submenu and select Visual Basic Editor.
• In the Project Explorer window (the one probably at top-left which lists what's in the project) double-click on ThisWorkbook.
• You should get a blank code window on the right.
• At the top of the code window, pull down the left-hand listbox and select WorkBook.
• Pull down the right-hand listbox and select SheetChange.
• You are now in the code window for the workbook's SheetChange event procedure. Any code you enter here will be executed each time a change is made on any of the worksheets in this workbook.
• Paste in the following code. This is a very simple example, but will provide you with a starting point. You can then take this code and play with it.
  

  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim W As Worksheet
    Set W = Sh
    Dim C As Range
    Dim Fnum As Long
    Fnum = FreeFile
    Open "C:\Temp\ChangeLog.txt" For Append Access Read Write Lock Write As #Fnum
    For Each C In Target.Cells
      With C
        'Debug.Print , W.Name, .Column, .Row, .Value, .Value2
        Print #Fnum, W.Name; vbTab; .Column; vbTab; .Row; vbTab; .Formula; vbTab; .Value
      End With
    Next
    Set W = Nothing
    Set C = Nothing
    Close #Fnum
  End Sub
   

[smugcool]

Ok, well in this case we'll actually be talking about VBA. That's "Visual Basic for Applications", which is a version of VB built into MS Office applications to do scripting (automating tasks) by writing "macro" code.

Let's have a go at throwing together a simple little example macro for Excel which will append the details of each change made to a worksheet into a text file.

To create this, follow the following steps. (Hopefully they won't vary too much on your system, but we'll see).

• open a new worksheet in Excel.
• Pull down the Tools menu, expand the [b]Macro submenu and select Visual Basic Editor.
• In the Project Explorer window (the one probably at top-left which lists what's in the project) double-click on ThisWorkbook.
• You should get a blank code window on the right.
• At the top of the code window, pull down the left-hand listbox and select WorkBook.
• Pull down the right-hand listbox and select SheetChange.
• You are now in the code window for the workbook's SheetChange event procedure. Any code you enter here will be executed each time a change is made on any of the worksheets in this workbook.
• Paste in the following code. This is a very simple example, but will provide you with a starting point. You can then take this code and play with it.
  

  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim W As Worksheet
    Set W = Sh
    Dim C As Range
    Dim Fnum As Long
    Fnum = FreeFile
    Open "C:\Temp\ChangeLog.txt" For Append Access Read Write Lock Write As #Fnum
    For Each C In Target.Cells
      With C
        'Debug.Print , W.Name, .Column, .Row, .Value, .Value2
        Print #Fnum, W.Name; vbTab; .Column; vbTab; .Row; vbTab; .Formula; vbTab; .Value
      End With
    Next
    Set W = Nothing
    Set C = Nothing
    Close #Fnum
  End Sub
   

Ya thanx dear its working. But there is one problem, since this file is in networking system, i wanted the name of the User who will modify these changes. Is It possible?

[Killer42]

Ya thanx dear its working. But there is one problem, since this file is in networking system, i wanted the name of the User who will modify these changes. Is It possible?

Probably. Have a look through the properties that are available. Um... Application.UserName might be what you want.

[smugcool]

Probably. Have a look through the properties that are available. Um... Application.UserName might be what you want.

Thanx a lot Dude.Gr8 mind.It has worked.One more favour if you can.I also want the Date and Time of changes made.Plzzzzzzzzzz help.

[Killer42]

Thanx a lot Dude.Gr8 mind.It has worked.One more favour if you can.I also want the Date and Time of changes made.Plzzzzzzzzzz help.

Check out the Now() function. And you may need to use Format() on it.

Um... Now is from VB6. In Excel, you might have to use something else. Check the doco for system date and time.

[smugcool]

Check out the Now() function. And you may need to use Format() on it.

Um... Now is from VB6. In Excel, you might have to use something else. Check the doco for system date and time.

many many thanx to you.Its working.See you later. Bye bye

[smugcool]

many many thanx to you.Its working.See you later. Bye bye

Hi,
Can u tell me now how can i get the "logfile" datewise.I mean i had specified a path of a text file.But is it not possible that certain VBA command can generate the text file automatcally datewise format.

Regards
Anup

[Killer42]

Can u tell me now how can i get the "logfile" datewise.I mean i had specified a path of a text file.But is it not possible that certain VBA command can generate the text file automatcally datewise format.

I don't understand what you mean by "generating a file datewise".

[smugcool]

I don't understand what you mean by "generating a file datewise".

As when i type the below lines in the VBA In return it gives the changes made in the excel in changeLog.txt file.
Open "C:\Temp\ChangeLog.txt" For Append Access Read Write Lock Write As #Fnum

I want this file to automicatlly generate as "changelog" along with system date.
e.g. changelog2007.05.25 for a perticular day. Hope now you would have got what i want to say.

[smugcool]

As when i type the below lines in the VBA In return it gives the changes made in the excel in changeLog.txt file.
Open "C:\Temp\ChangeLog.txt" For Append Access Read Write Lock Write As #Fnum

I want this file to automicatlly generate as "changelog" along with system date.
e.g. changelog2007.05.25 for a perticular day. Hope now you would have got what i want to say.

whats up.Was my question not understandable???Plz help me to get the things done.

[Killer42]

whats up.Was my question not understandable???Plz help me to get the things done.

Sorry, been very busy. Don't forget you're dealing with people in different timezones around the world who are volunteering their own time to help. So sometimes you need to be a bit patient.

All you need do is include the date in the string which names the file. For example...

Dim strFileName As String
strFileName = "C:\Temp\ChangeLog" & Format(Now, "yyyy-mm-dd" & ".Txt"
Open strFileName For Append Access Read Write Lock Write As #Fnum

You need to watch your terminology. It's just confusing things to say that the file is "automatically generating". The file is not doing anything. You want your code to generate a name for the file which includes the current date.

[smugcool]

Sorry, been very busy. Don't forget you're dealing with people in different timezones around the world who are volunteering their own time to help. So sometimes you need to be a bit patient.

All you need do is include the date in the string which names the file. For example...

Dim strFileName As String
strFileName = "C:\Temp\ChangeLog" & Format(Now, "yyyy-mm-dd" & ".Txt"
Open strFileName For Append Access Read Write Lock Write As #Fnum

You need to watch your terminology. It's just confusing things to say that the file is "automatically generating". The file is not doing anything. You want your code to generate a name for the file which includes the current date.

Hi,
I got your point. But its not happening:
Look below the codes it seems fine to me.But it doesnot work at all.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim W As Worksheet

Set W = Sh

Dim C As Range

Dim Fnum As Long
Dim strFileName As String
strFileName = "C:\Documents and Settings\Anup\Desktop\book1.txt" & Format(Now(), "yyyy-mm-dd" & ".Txt")
Fnum = FreeFile
Open strFileName For Append Access Read Write Lock Write As #Fnum
For Each C In Target.Cells
With C
'Debug.Print , W.Name, .Column, .Row, .Value, .Value2,.Application.UserName
Print #Fnum, W.Name; vbTab; .Column; vbTab; .Row; vbTab; .Value; vbTab; Application.UserName; vbTab; Now(); vbTab
End With
Next

Set W = Nothing

Set C = Nothing

Close #Fnum

End Sub