locked
logs for vba code RRS feed

  • Question

  • Hi ,

    I have written a vba code wherein one can change the database value from excel itself. I have given access rights to specific users also . My requirement now is to capture logs of user activity . Which user has updated the value and where user clicked.



    Megh_ 123

    Friday, December 1, 2017 9:25 AM

Answers

  • Hi Megh_123,

    >>Can I create this log file at server

    You could create a log file in shared drive, and then configure the file path in code.

    You could replace the file path in the file which I shared in your thread below:

    #logs for userform

    https://social.msdn.microsoft.com/Forums/office/en-US/c5725b32-ef4c-49cb-b3b3-1ed2183c5d85/logs-for-userform?forum=exceldev

    If you have any issue with the file, please feel free to let me know.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Megh_ 123 Wednesday, January 3, 2018 7:21 AM
    Friday, December 8, 2017 8:08 AM

All replies

  • Hello Megh_123,

    What do you mean one can change the database value from excel itself? In your previous thread, you are using an VBA macro to update value in sql server database. I think you could use code to record use info and the sql info after connection execute the sql string. You could try to add a log string to a text file or other format you want.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 4, 2017 6:23 AM
  • Thanks Terry. How can I add a log string to a text file ?What code should I add in my existing code to that user activity will get traced in  that log sheet.

    Megh_ 123

    Monday, December 4, 2017 10:31 AM
  • Hi Megh_123,

    You could use FileSystemObject Object to create/open a text file and then write log string to it. 

    Here is the simply code.

    Cn.Execute SQLSTR, AffectedRows
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    logFileName = "C:\Users\Administrator\Desktop\" & Format(Now, "yyyy_mm_dd_") & "log.txt"
    If Dir(logFileName) <> "" Then
    Set txt = fso.OpenTextFile(logFileName, ForAppending)
    Else
    Set txt = fso.CreateTextFile(logFileName, False)
    End If
    
    If AffectedRows > 0 Then
    txt.WriteLine "Time:" & Now & " OperationSQL:" & SQLSTR & " States:" & "Succeed"
    Else
    txt.WriteLine "Time:" & Now & " OperationSQL:" & SQLSTR & " States:" & "Fail"
    End If
    txt.Close
    Cn.Close
    Set Cn = Nothing

    By the way, what's current state of your previous thread Excel VBA? If your issue has been resolved, I would suggest you mark helpful answer to close that thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, December 5, 2017 8:06 AM
  • Well, in the previous thread the value is updating but the updated value is not coming in msgbox . 

    I am gettings errors in login form also , I have asked question on that also.

    The code you provided above I wanted to add this in my previous code where I am updating value from database. where should I write this code in between previous code. should I write it after update query?


    cheryl_ 123

    Tuesday, December 5, 2017 1:21 PM
  • Hi , Can I create this log file at server ....so that everyone from my team can check it . I dont want it to be on my system only .

    cheryl_ 123

    Tuesday, December 5, 2017 1:31 PM
  • Hi ,

    I got error in above as "Argument not optional" I have created a folder in c drive and provided the correct path . In that folder I created a txt document . name of this text document I have kept in set txt=fsc.createTextFile(Log,False).

    in the log document is it possible to get username also . I will provide username from login page.



    megh_123


    • Edited by Megh_ 123 Tuesday, December 5, 2017 1:53 PM
    Tuesday, December 5, 2017 1:52 PM
  • Hello,

    You could create a function to write log, once you want to log a string, you could call the function with the string you want to log as parameter.

    Here is the example function.

    Function WriteLog(textForLogging As String)
    logFileName = "C:\Users\Administrator\Desktop\" & Format(Now, "yyyy_mm_dd_") & "log.txt"
    If Dir(logFileName) <> "" Then
    Set txt = fso.OpenTextFile(logFileName, ForAppending)
    Else
    Set txt = fso.CreateTextFile(logFileName, False)
    End If
    txt.WriteLine textForLogging
    txt.Close
    End Function
    

    What's value of "Log"? Did you set its value? It seems that you shared some code but I failed to see. Could you try to share your code again?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 6, 2017 7:55 AM
  • What's value of "Log"? Did you set its value? ....What value should I pass ...sorry I am not getting this 

    cheryl_ 123

    Wednesday, December 6, 2017 10:52 AM
  • Hi Megh_123,

    You said you get error at "txt=fsc.createTextFile(Log,False)", right?

    Here "Log" show be full file name of a txt file, you could use code like

    txt=fsc.createTextFile("C:\test.txt",False)

    or

    Log="C:\test.txt"
    txt=fsc.createTextFile(Log,False)

    Did you ever set like this?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 8:05 AM
  • Hi , 

    Yes I tried this also.Do I need to write anything in below command? I have placed this code in module and function is called in main code of userform. 

    If Dir(logFileName) <> "" Then"

    cheryl_ 123

    Thursday, December 7, 2017 9:18 AM
  • Hello Megh_123,

    I think you do not need write anything in the code. I used Dir() to check if the log file exist. If the file exist, it will return the file name, otherwise it will return "".

    Changing code like this may make it clearer.

    Function WriteLog(textForLogging As String)
    logFileName = "C:\Users\Administrator\Desktop\" & Format(Now, "yyyy_mm_dd_") & "log.txt"
    If Dir(logFileName)= "" Then
    'the log file does not exist, create a new log file
    Set txt = fso.CreateTextFile(logFileName, False)
    Else
    'the log file exist, open it for logging
    Set txt = fso.OpenTextFile(logFileName, ForAppending)
    End If
    txt.WriteLine textForLogging
    txt.Close
    End Function

    You could call the function from user form module.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 7, 2017 9:29 AM
  • Hi Megh_123,

    >>Can I create this log file at server

    You could create a log file in shared drive, and then configure the file path in code.

    You could replace the file path in the file which I shared in your thread below:

    #logs for userform

    https://social.msdn.microsoft.com/Forums/office/en-US/c5725b32-ef4c-49cb-b3b3-1ed2183c5d85/logs-for-userform?forum=exceldev

    If you have any issue with the file, please feel free to let me know.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Megh_ 123 Wednesday, January 3, 2018 7:21 AM
    Friday, December 8, 2017 8:08 AM