VBA 跟踪文件使用情况 [英] VBA Track file usage

问题描述

我目前有一个生成客户端语句的 excel 文件.我需要跟踪谁运行了他们的语句.目前，每当生成语句时，我都会有一个宏向我发送一封包含其用户名的电子邮件.但是，在瘦客户端系统上运行的人会收到提示；

I currently have an excel file that produces client statements. I need to track who has run their statements. Currently, whenever statements are produced I have a macro that send me an email with their user name. However people running on a Thin Client system they get a prompt;

'一个程序正试图代表您发送电子邮件...'

'A program is trying to send an email message on your behalf...'

有什么办法可以摆脱这个提示并仍然发送电子邮件，或者有人对如何跟踪使用情况有任何其他想法.我通过 Share Point 共享文件.那么这可能有一些功能吗?

IS there some way I can get rid of this prompt and still send the email, or has anyone got any other ideas on how to track usage. I share the file through Share Point. So that might have some capabilities?

谢谢

推荐答案

如果您的网络上有每个人都可以访问的位置，您可以编写一个日志文件.很可能是 Sharepoint 服务器上的一个位置.

If there is a location on your network that everyone has access to you can write a log file. Most likely a spot on the Sharepoint server.

从当前发送电子邮件的代码中调用类似的东西.

Something like this called from the code that is currently sending out the email.

在您的 VBA IDE 中，转到工具菜单并选择引用.选择Microsoft 脚本运行时"

In you VBA IDE go to the tools menu and select references. Select "Microsoft scripting runtime"

Private Sub LogUsage()

    Dim ts As TextStream
    Dim fs As FileSystemObject
    Dim strLogFile As String

    strLogFile = "\servernamesharenamelogUsage.txt"

    'Check if the file exists, if it does, open it, if it doesn't create it
    Set fs = New FileSystemObject
    If fs.FileExists(strLogFile) = True Then
        Set ts = fs.OpenTextFile(strLogFile, ForAppending)
    Else
        Set ts = fs.CreateTextFile(strLogFile, True, False)
    End If

    'Log your entry
    ts.WriteLine "Used by " & Environ$("Username") & " at " & Now & " on computer " & Environ$("Computername")

     'Clean up
     ts.Close: Set ts = Nothing
     Set fs = Nothing

 End Sub

这篇关于VBA 跟踪文件使用情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！