CODEWITHSUNDEEP
vbaexcelexcel-2007
user2574072
user2574072

Reputation: 11

Import and Run Macro from text file

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:

Run-time error '1004':

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

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?

Here's the code:

  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

And the .bas file

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

Upvotes: 1

Views: 6111

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33165

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

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"

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.

Upvotes: 0

Geoff
Geoff

Reputation: 8860

I'm using Excel 2013, and with your code I actually get a different error, and the error is consistent regardless of whether I add your Set oBook = oExcel.Workbooks.Add line:

Run-time error '1004': Programmatic access to Visual Basic Project is not trusted

This is fixed by enabling the 'Trust access to the VBA project object model' setting. This is under (again, in 2013):

File 
 - Options 
  -  Trust Center 
   -   Trust Center Settings
    -    Macro Settings.

From the documentation:

This security option makes it more difficult for unauthorized programs to build "self-replicating" code that can harm end-user systems

In other words, the setting is designed to prevent worms adding code to workbooks without the user's knowledge.

Also verify that the document is in a trusted location; see here for details.

Upvotes: -1

d-stroyer
d-stroyer

Reputation: 2706

Your code will work if you run it independently from the "beforesave" event - for example as a stand-alone public sub.

Upvotes: 0

Related Questions