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

问题描述

我目前有一个生成客户端语句的excel文件。我需要跟踪谁跟踪谁的陈述。
目前，无论何时产生语句，我都有一个宏给我发送一个带有用户名的电子邮件。然而，在瘦客户端系统上运行的人员会得到提示;

'程序正在尝试发送一封电子邮件...'

有一些方法我可以摆脱这个提示，仍然发送电子邮件，或有人有任何其他想法如何跟踪使用。我通过Share Point共享文件。那么可能有一些功能？

谢谢

解决方案

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

从目前发送电子邮件的代码中调用这样的东西。

在VBA IDE中，进入工具菜单并选择参考。选择Microsoft脚本运行时

  Private Sub LogUsage（）
 
 Dim ts As TextStream 
 Dim fs As FileSystemObject 
 Dim strLogFile As String 
 
 strLogFile =\\servername\sharename\log\Usage.txt
 
'检查文件是否存在，如果它不存在，打开它，如果它不创建它
设置fs =新的FileSystemObject 
如果fs.FileExists（strLogFile）= True然后
设置ts = fs.OpenTextFile（strLogFile，ForAppending）
 Else 
设置ts = fs.CreateTextFile（strLogFile，True，False）
结束如果
 
'记录您的条目
 ts.WriteLine由& Environ $（用户名）& at&现在& 在电脑& Environ $（Computername）
 
'清理
 ts.Close：设置ts =没有
设置fs = Nothing 
 
 End Sub 
  

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...'

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?

Thank you

解决方案

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.

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 = "\\servername\sharename\log\Usage.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屋！