I have a collection of "utility" modules that I wish to be able to import
into a number of workbooks, and then run. Below is the code I wished to use
to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files", MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add",
MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named. The trouble I am
having is in retrieving the name of the module in order to run the macro in
it. The modules all contain one macro, named the same as the module name. For
instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath, "P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant - does
anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary and
those who don't.

hi, Geoff !

I would get the (*.bas) "clean" filename from the full-path returned by GetOpenFileName
- (i.e.) Dir(modName(j))

and then, "clear" the ".bas" EXTension from the filename
- len(... & instr(...
- replace(...
- other/s ?

hth,
hector.

__ OP __
I have a collection of "utility" modules that I wish to be able to import into a number of workbooks
and then run. Below is the code I wished to use to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files", MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add", MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named.
The trouble I am having is in retrieving the name of the module in order to run the macro in it.
The modules all contain one macro, named the same as the module name.
For instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath, "P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant
- does anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary and those who don't.

As an aside, having a module and macro name the same has been shown to give
problems, not a good practice.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Geoff" wrote in message
...
I have a collection of "utility" modules that I wish to be able to import
into a number of workbooks, and then run. Below is the code I wished to
use
to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add",
MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named. The trouble I
am
having is in retrieving the name of the module in order to run the macro
in
it. The modules all contain one macro, named the same as the module name.
For
instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath,
"P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant -
does
anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary
and
those who don't.

.....and even bigger problems trying to track the arising problem down!!

(first hand experience - don't do it!)

HTH

--

Regards,
Nigel




"Bob Phillips" wrote in message
...
As an aside, having a module and macro name the same has been shown to
give problems, not a good practice.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Geoff" wrote in message
...
I have a collection of "utility" modules that I wish to be able to import
into a number of workbooks, and then run. Below is the code I wished to
use
to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add",
MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named. The trouble I
am
having is in retrieving the name of the module in order to run the macro
in
it. The modules all contain one macro, named the same as the module name.
For
instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath,
"P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant -
does
anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary
and
those who don't.

I have never named a module and a procedure the same (aren't I good <vbg),
but I have heard the anecdotes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Nigel" wrote in message
...
....and even bigger problems trying to track the arising problem down!!

(first hand experience - don't do it!)

HTH

--

Regards,
Nigel




"Bob Phillips" wrote in message
...
As an aside, having a module and macro name the same has been shown to
give problems, not a good practice.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Geoff" wrote in message
...
I have a collection of "utility" modules that I wish to be able to import
into a number of workbooks, and then run. Below is the code I wished to
use
to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add",
MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named. The trouble I
am
having is in retrieving the name of the module in order to run the macro
in
it. The modules all contain one macro, named the same as the module
name. For
instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath,
"P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant -
does
anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary
and
those who don't.

;-) - I never do now!

All my module names a prefixed with 'm'



--

Regards,
Nigel




"Bob Phillips" wrote in message
...
I have never named a module and a procedure the same (aren't I good <vbg),
but I have heard the anecdotes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Nigel" wrote in message
...
....and even bigger problems trying to track the arising problem down!!

(first hand experience - don't do it!)

HTH

--

Regards,
Nigel




"Bob Phillips" wrote in message
...
As an aside, having a module and macro name the same has been shown to
give problems, not a good practice.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Geoff" wrote in message
...
I have a collection of "utility" modules that I wish to be able to
import
into a number of workbooks, and then run. Below is the code I wished to
use
to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add",
MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named. The trouble
I am
having is in retrieving the name of the module in order to run the
macro in
it. The modules all contain one macro, named the same as the module
name. For
instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath,
"P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant -
does
anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary
and
those who don't.

Hi Hector, muchas gracias - in the end I went with a combination of mid,
instrrev and len to get the clean filename as you suggested.
@Bob, Nigel - thanks guys, yes it's not great practice I know but I don't
intend to do it regularly. In fact having thought about it a quicker (or at
least easier) way to do this would have been to simply rewrite the utilities
to work on a supplied workbook variable rather than using ThisWorkbook.
However I am a little curious about the information available from a module
once it has been imported - for instance is it possible to access a list of
the macros contained within it? Or even to pick up the name of the module
itself? I guess this is the main motivation behind my post.

Thanks all.
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Héctor Miguel" wrote:

hi, Geoff !

I would get the (*.bas) "clean" filename from the full-path returned by GetOpenFileName
- (i.e.) Dir(modName(j))

and then, "clear" the ".bas" EXTension from the filename
- len(... & instr(...
- replace(...
- other/s ?

hth,
hector.

__ OP __
I have a collection of "utility" modules that I wish to be able to import into a number of workbooks
and then run. Below is the code I wished to use to do this:

Option Explicit

Sub Add_Modules()

Dim wbName
Dim wkBk As Workbook
Dim modName
Dim i As Integer
Dim j As Integer
Dim module As String

wbName = Application.GetOpenFilename(Title:="Select Files", MultiSelect:=True)
modName = Application.GetOpenFilename(Title:="Select Modules to Add", MultiSelect:=True)
For i = LBound(wbName) To UBound(wbName)
Set wkBk = Workbooks.Open(wbName(i))
For j = LBound(modName) To UBound(modName)
Call InsertVBComponent(wkBk, modName(j))
module = ? ' This is where I'm stuck...
Application.Run (wkBk.Name & "!" & module)
Next j
Next i

End Sub

InsertVBComponent is a sub which imports the module named.
The trouble I am having is in retrieving the name of the module in order to run the macro in it.
The modules all contain one macro, named the same as the module name.
For instance, Reset.bas is as follows:

Attribute VB_Name = "Reset"
Option Explicit

Sub Reset()

Dim linkArr
Dim i As Integer
Dim newLink As String
linkArr = ThisWorkbook.LinkSources
For i = 1 To UBound(linkArr)
newLink = Replace(linkArr(i), Application.StartupPath, "P:\CASE\MIPROJ")
ThisWorkbook.ChangeLink linkArr(i), newLink
Next i
End Sub

One approach I have considered taking is to use the VB_Name constant
- does anyone know how to retrieve this from the module?
--
There are 10 types of people in the world - those who understand binary and those who don't.