incorporating a macro import within another sub

[Reddog94]

The following code (blue font) finds specific worksheets in an active workbook, and copies them into a new workbook then names the new workbook the same name of the worksheet. It all works perfectly.

The second code below (red font) imports some vb into the new workbook. It works fine if I manually open the new workbook and run it in the immediate window. However, I would like to incorporate the red code into the blue code so it all runs as one. I've tried inserting the red line after the "ws.copy after" line and after the ".saveas" line, but no dice.

I think it is because I'm not making the new book active (but that is an uneducated guess). Does anyone know what I'm doing wrong?


For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
With NewBook
.Title = ws.Name
ws.Copy After:=NewBook.Worksheets("Sheet3")

For Each ws2 In NewBook.Worksheets
If ws2.Name <> ws.Name Then
ws2.Delete
End If
Next
.SaveAs Filename:="C:\Users\lmcginle\Desktop\" & ws.Name
.Close SaveChanges:=True
End With
End If
Next

wb.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Application.VBE.ActiveVBProject.VBComponents.Import ("T:\Finance Mgmt\Reporting\MacroExport.bas")

 

[AlphaFrog]

Did you set a reference to the VBA Extensibililty library?

Extensibility: Programming The VBA Editor

 

[Reddog94]

Can you be more specific in "setting a reference"? I followed the directions in the link you sent (making sure both the proper options to allow the import were enabled), but it still isn't working. Do I need to actually reference something in the code that I'm not understanding?

Thanks for the response!

 

[AlphaFrog]

Can you be more specific in "setting a reference"?

From the link...
"First, you need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error."


Can you better explain what "but it still isn't working" means?. Does it not compile. Do you get an error? Does it copy the code to the wrong workbook?

 

[Reddog94]

Ok yes, I set the reference you descripe above. By "not working" I mean that nothing changed from my original post. I can get the import if I open the new workbook (after the creation code is done running) and put the import code in the immediate window. However, when the import code is added into the creation code, the import never happens.

 

[AlphaFrog]

Try this...

    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] wb.Worksheets
        [color=darkblue]If[/color] UCase(Left(ws.Name, 2)) = "CC" [color=darkblue]Then[/color]
            ws.Copy [color=green]'This copies the one worksheet to a new workbook.[/color]
            Application.VBE.ActiveVBProject.VBComponents.Import "T:\Finance Mgmt\Reporting\MacroExport.bas"
            ActiveWorkbook.SaveAs Filename:="C:\Users\lmcginle\Desktop\" & ws.Name
            ActiveWorkbook.Close SaveChanges:=[color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color]

    wb.Activate

    Application.DisplayAlerts = [color=darkblue]True[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]

Macro to Add a Macro to New Workbooks
http://www.mrexcel.com/articles/copy-vba-module.php

 

[Reddog94]

That didn't work either. So, I tried this since the error is that an Object is needed. But even with the below I still get the Object error. I feel like this should be so easy and I'm just missing something really stupid.


Sub Import()

On Error GoTo ErrorHandler
Dim VBProj As Object
Dim VBComp As Object
Dim VBImp As String

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProject.VBComponents("module4")
Set VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

Application.VBE.VBProj.VBComp.Import VBImp

ErrorHandler:
MsgBox Error.Description

End Sub

 

[AlphaFrog]

This should work...

[color=darkblue]Dim[/color] VBProj [color=darkblue]As[/color] [color=darkblue]Object[/color]
[color=darkblue]Dim[/color] VBImp [color=darkblue]As[/color] [color=darkblue]String[/color]

[color=darkblue]Set[/color] VBProj = ActiveWorkbook.VBProject
VBImp = "T:\Finance Mgmt\Reporting\MacroExport.bas"

VBProj.VBComponents.Import VBImp

 

[Reddog94]

It still saying Error 424 Object Required. I'm stumped, because clearly the object is defined. Any other thoughts?

 

[AlphaFrog]

Did you replace this...
Application.VBE.VBProj.VBComp.Import VBImp

With this...
VBProj.VBComponents.Import VBImp


It worked for me. The only difference being the path and name of of the .bas file.