drag the module from the Access IDE to the Excel IDE

then DEBUG/COMPILE

you'll probably see
Option Compare Database


delete this line

"BlueWolverine" wrote:

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?



--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!