Copy Module

[Pestomania]

Hello, I am trying to use VBA to Export the Module from a Personal Macro Workbook and Import that file into the Active Workbook. I have seen a few that work when it is workbook to workbook but I cannot get them to work from PMW. I am just needing some help. Thank you

I didn't put any code because I don't have any right now

 

[alansidman]

Look at this one:

https://stackoverflow.com/questions/38694849/export-all-modules-from-personal-xlsb


http://www.snb-vba.eu/VBA_Excel_VBproject_en.html

 

[Pestomania]

Look at this one:

https://stackoverflow.com/questions/38694849/export-all-modules-from-personal-xlsb


http://www.snb-vba.eu/VBA_Excel_VBproject_en.html

I used this one:

Sub Macromodule_export()
With ActiveWorkbook.VBProject.VBComponents("AllMacros2")
.export"J:"&.Name & ".bas"
End With
End Sub

But it is stating: "Method 'VBPROJECT' of object'_Workbook' failed

I want it to Export from the personal macro workbook though and to work each time. Thank you

 

[alansidman]

Did you load the reference before building the actual macro to export.

 

[Pestomania]

Did you load the reference before building the actual macro to export.

Which reference are you speaking of?

 

[alansidman]

copied from the reference material I suggested:

Before applying code it's best to load the referenc to the VBA extensiblility library:
- manually : open the VBE Editor: Alt-F11/Menu Bar/ Extra / references/ check 'Microsoft Visual Basic for Application Extensibility 5.3'
- using VBA

Method 'AddFromGuid'

[COLOR=#00008B][FONT=Tahoma][COLOR=blue]Sub load_reference_1()
ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
end sub[/COLOR]
[/FONT][/COLOR]

Method 'AddFromFile'

[COLOR=#00008B][FONT=Tahoma][COLOR=blue]Sub Load_reference_2()
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
end sub[/COLOR]
[/FONT][/COLOR]