Showing PERSONAL.XLS
 

Private Sub CommandButton1_Click()
Dim VBComp As VBComponent
Dim FName As String

With Workbooks("Custom Button.xls")
FName = .Path & "\codes.txt"
End With
Workbooks("PERSONAL.xls").VBProject.VBComponents.I mport
FName
End Sub


The above code adds a module to the PERSONAL.XLS
successfully only when PERSONAL.XLS is showing in the VBE,
however if PERSONAL.XLS is NOT showing in the VBE, I get
the debug error "Subscript out of Range" and it highlights
the below part of the code.

How do I show PERSONAL.XLS in the VBE so my code will work?

Workbooks("PERSONAL.xls").VBProject.VBComponents.I mport
FName


Thank you

Todd Huttenstine

Showing PERSONAL.XLS
 

Todd,

This code example worked no matter what was showing in the VBE:

Sub AddModuleByExportImport2()
Dim myBook As Workbook
Dim myFile As String

myFile = "C:\Excel\myVBAFile2.bas"
Set myBook = Workbooks("Personal.xls")
myBook.VBProject.VBComponents.Import (myFile)
End Sub

HTH,
Bernie
MS Excel MVP

"Todd Huttenstine" wrote in
message ...
Private Sub CommandButton1_Click()
Dim VBComp As VBComponent
Dim FName As String

With Workbooks("Custom Button.xls")
FName = .Path & "\codes.txt"
End With
Workbooks("PERSONAL.xls").VBProject.VBComponents.I mport
FName
End Sub


The above code adds a module to the PERSONAL.XLS
successfully only when PERSONAL.XLS is showing in the VBE,
however if PERSONAL.XLS is NOT showing in the VBE, I get
the debug error "Subscript out of Range" and it highlights
the below part of the code.

How do I show PERSONAL.XLS in the VBE so my code will work?

Workbooks("PERSONAL.xls").VBProject.VBComponents.I mport
FName


Thank you

Todd Huttenstine