Copy A Module From One Project To Another

[BlueWolverine]

Hello
MS Access/Excel 2003 on XP Pro.

I am attempting to copy a vb code module from an access to an excel.
I found this sub online "Function CopyModule" at
(http://www.cpearson.com/excel/vbe.aspx).

I am using the m_test sub to gather the vbprojects and variables that are
requred for the CopyModule function.

str_file is the excel filename string.

Public Sub m_test(str_file As String)
Dim str_module As String
Dim VBAEditor As VBIDE.VBE
Dim vbp_access, vbp_excel As VBIDE.VBProject

str_module = "CodeModule"

Set VBAEditor = Application.VBE
Set vbp_access = VBAEditor.ActiveVBProject
MsgBox (vbp_access.Name) 'To verify correct assignment
Set app_xls = Excel.Application
Set vbp_excel = app_xls.Workbooks(str_file).VBProject
MsgBox (vbp_excel.Name) 'To verify correct assignment
MsgBox CopyModule(str_module, vbp_access, vbp_excel, True) 'Execute Copy
End Sub

I get the following errors...

When the last line is:
MsgBox CopyModule(str_module, vbp_access, vbp_excel, True) 'Execute Copy
Compile Error: ByRef arguement type mismatch.

When the last line is:
MsgBox CopyModule(str_module, (vbp_access), vbp_excel, True) 'Execute Copy
RunTimeError: 438 - Object doesn't support this property or method

The 'CopyModule' function is expecting String, VBIDE.VBProject,
VBIDE.VBProject, Boolean input variables.

 

[Dale Fye]

Have you tried the export and import file options that are available in the
VB Editor when you right click in the Project Explorer window? If you right
click on the module you want to export, and the select the Export File
option, Access will export the module to a .bas file. Then, go to Excel and
import it using the same technique.

 

[BlueWolverine]

The goal though is to have this happen automatically when the EXPORT function
is run.

OVERALL SCHEME.

1. Use Access to refine data into a packet
2. Export packet to excel
3. Format packet within excel
4. Export VBA Module to excel
5. let end user execute code out of excel as necessary.

1-4 are being designed as automatic steps of the export process. We are
trying to wholesale automate a manual function and we don't want our "noob"
users messing with the VBA module.



Does anyone know how to fix the errors indicated at the bottom so this code
will execute clean?