Vb-how To Insert Header In Logfile

[smugcool]

Hi all,

Below are the codes which i wrote to monitor changes in excel. Now i wanted to add Header for the text file which will be generated. LIke suppose if change is happend in the row. i wanted to give name of the hader as "ROW". In the text file.

if changes are happening in the column i wanted to Name it as column.Kindly help me??

below are the codes :-

 
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
 

[danp129]

I'm not sure I understand. Every cell that's edited is going to have a Row and a Column that it exists in, so how do you plan to determine if it's a row edit instead of a column edit?

[smugcool]

go thru the below example:-

this is the content of the file which is actually generated after change is made

AUDIT_PERSONALIZATION 1 104 5/21/2007 b 5/22/2007 5:50:01 PM
AUDIT_PERSONALIZATION 2 104 1 b 5/22/2007 5:50:01 PM

now i want this file to come up with header e.g

sheet name row column value date username
AUDIT_PERSONALIZATION 1 104 5/21/2007 b 5/22/2007 5:50:01 PM
AUDIT_PERSONALIZATION 2 104 1 b 5/22/2007 5:50:01 PM

Is there anyway??

[Killer42]

Why not just use Notepad or something, and create the file with your headers there already. Then your code can just append to the file.

Or if your code creates the file, it can simply write out the header line before writing the first line of "log info".

If you need to check whether there's anything in the file yet, after you open it you can use the Lof() function to get the current length (zero if it's empty).

[smugcool]

Why not just use Notepad or something, and create the file with your headers there already. Then your code can just append to the file.

Or if your code creates the file, it can simply write out the header line before writing the first line of "log info".

If you need to check whether there's anything in the file yet, after you open it you can use the Lof() function to get the current length (zero if it's empty).

I can't use a format of file since this reports are generated on daily basis.

i think your second idea is correct. Can u plz guide me the code for writing the header line.As i am not aware of the code.

[Killer42]

Well, I don't know your circumstances very well, but it should be pretty simple, something like...

' After opening the file for Append...
If Lof(FileNum) = 0 Then
  Print #FileNum, "sheet name row column value date username"
End If