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!

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)



On Mon, 2 Nov 2009 12:20:01 -0800, 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?

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!