Copy VBA MODULE from ACCESS to EXCEL

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

Anyone got some help for the Errors?

 

[Chip Pearson]

I don't believe you can copy from one instance of a VBE to another.
You need to export the module out of Access/VBE and then import it
into Excel/VBE.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Patrick Molloy]

drag the module from the Access IDE to the Excel IDE

then DEBUG/COMPILE

you'll probably see
Option Compare Database


delete this line