Showing PERSONAL.XLS

[Todd Huttenstine]

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.Import
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.Import
FName


Thank you

Todd Huttenstine

 

[Bernie Deitrick]

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