从文本文件导入并运行宏 [英] Import and Run Macro from text file

问题描述

我正在尝试简单地将测试宏导入到我当前的Excel工作簿中.然后运行宏.我使用的是Excel2007.出现错误:

I'm trying to simply import a test macro into my current Excel Workbook. And then run the macro. I'm on Excel 2007. I'm getting the error:

运行时错误'1004':

Run-time error '1004':

无法运行宏"DoKbTest".宏可能 在此工作簿中不可用，或者可能禁用了所有宏.

Cannot run the macro 'DoKbTest'. The macro may not be available in this workbook or all macros may be disabled.

如果我将一行代码更改为:Set oBook = oExcel.Workbooks.Add，它将正常工作.为什么当我参考ThisWorkbook时，它会失败?

If I change one line of code to say: Set oBook = oExcel.Workbooks.Add it works fine. Why does it fail when I refer to ThisWorkbook?

代码如下:

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

      Dim oXL As Excel.Application
      Dim oBook As Excel.Workbook
      Dim oSheet As Excel.Worksheet
      Dim i As Integer, j As Integer
      Dim sMsg As String

    ' Create a new instance of Excel and make it visible.
      Set oXL = GetObject(, "Excel.Application")
      oXL.Visible = True

    ' Add a new workbook and set a reference to Sheet1.
      Set oBook = ThisWorkbook
      oBook.Activate

      Set oSheet = Sheets("Overview")
      oSheet.Activate

      sMsg = "Fill the sheet from in-process"
      MsgBox sMsg, vbInformation Or vbMsgBoxSetForeground

    ' The Import method lets you add modules to VBA at
    ' run time. Change the file path to match the location
    ' of the text file you created in step 3.
      oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Users\jstockdale\Desktop\KbTest.bas"

    ' Now run the macro, passing oSheet as the first parameter
      oXL.Run "DoKbTest", oSheet

    ' You're done with the second test
      MsgBox "Done.", vbMsgBoxSetForeground

    ' Turn instance of Excel over to end user and release
    ' any outstanding object references.
      oXL.UserControl = True
      Set oSheet = Nothing
      Set oBook = Nothing
      Set oXL = Nothing

End Sub

和.bas文件

Attribute VB_Name = "KbTest"

   ' Your Microsoft Visual Basic for Applications macro function takes 1 
   ' parameter, the sheet object that you are going to fill.

   Public Sub DoKbTest(oSheetToFill As Object)
      Dim i As Integer, j As Integer
      Dim sMsg As String
      For i = 1 To 100
         For j = 1 To 10

            sMsg = "Cell(" & Str(i) & "," & Str(j) & ")"
            oSheetToFill.Cells(i, j).Value = sMsg
         Next j
      Next i
   End Sub

推荐答案

在调用BeforeSave事件时，VBA会收集有关环境的所有数据以运行其子项.当您通过在事件中添加代码来更改环境时，VBA不会返回并轮询环境以查看更改，而是使用已经知道的信息.

When the BeforeSave event is called, VBA collects all the data about the environment to run it's subs. When you change the environment by adding code within the event, VBA doesn't go back and poll the environment to see what's changed, it uses what it already knows.

当您将其放在其他工作簿中时，它会起作用，因为另一个工作簿中的代码不会影响您工作簿中的代码(除非可能已引用它们).

When you put it in a different workbook, it works because code in another workbook doesn't affect the code in your workbook (unless they're referenced probably).

您可以尝试使用OnTime来使VBA重置自身.将代码放入标准模块中并使用

You could try to use OnTime to get VBA to reset itself. Put the code in a standard module and use

oXL.OnTime Now, "DoKbTest"

那将使该宏排队以便尽快运行.退出BeforeSave时，DoKbTest将运行，但是它将是独立的，因此VBA将重新初始化所有内容.

That will queue up that macro to run as soon as possible. When the BeforeSave quits, DoKbTest will run, but it will be separate so VBA will reinitialize everything.

这篇关于从文本文件导入并运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！