找出什么工作簿称为宏 [英] Find out what workbook called a macro

问题描述

我在工作簿ABC中有一个宏。我想知道其他工作簿称之为宏，因为我们将要替换它的功能。有没有办法告诉什么工作簿在执行时调用宏？或者Application.Run是否从被叫宏隐藏？

解决方案

我不知道如何获取工作簿名称。您可以登录工作站和用户，然后转到工作站并启动Excel，然后转到文件 - >最近查看计算机上使用的最近的工作簿。

您可以将日志文件写入到包含宏的工作簿的位置。

从宏调用这样的东西。

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

  Private Sub LogUsage（）
 
 Dim ts As TextStream 
 Dim fs As FileSystemObject 
 Dim strLogFile As String 
 
 strLogFile = Application.ActiveWorkbook.Path& \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：Set ts = Nothing 
设置fs = Nothing 
 
 End Sub 
  

I have a macro in workbook ABC. I want to find out what other workbooks are calling this macro, since we are going to be replacing its functionality. Is there a way to tell what workbook is calling the macro when it executes? Or does Application.Run hide that from the called macro?

解决方案

I'm not sure how to get the workbook name. You could log the workstation and user, then go to the workstation and start Excel and go to File -> Recent to see the recent workbooks used on the computer.

You can write a log file to the location where the workbook is that contains the macro.

Something like this called from the macro.

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 = Application.ActiveWorkbook.Path & "\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

这篇关于找出什么工作簿称为宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！