Automatically Import a .BAS file/module

[phdbd]

Hi,
I'm using the ParseOutNames Function, which I lifted from CPearson, and it
works excellent.

The problem is the function will not work if the Module is in Personal.xls,
I have to manually Import my .bas file to the Active Workbook in the Visual
Basic Editor: File-->Import File-->Open.

Which would be okay, but I need to automate it so I can hand off a solution
to someone who is not an excel user.

I would like to create a macro that will go to the file ie (C:\Program
Files\Microsoft Office\Office10\XLStart\ParseOutNames.bas) and import it
automatically to the active workbook. And the macro record feature doesn't
work when navigating around in the Visual Basic Editor.

If someone has the code for this it would be much appreciated, I've spent
hours researching this and can't find a solution

Thanks
James

 

[Dave Peterson]

Don't do it.

Instead start a new workbook and call it JamesUtils.xls
Put all the nice macros (subs and functions) in that workbook.

Then distribute that single workbook to each of your users.

Then they can open their files, then open this jamesutils.xls file and use:

=jamesutils.xls!ParseOutNames(a1)

The bad news is that if they share the workbook with the names with others,
they'll have to either share a copy of JamesUtils.xls with them (too).

Or they can convert those formula cells to values (edit|copy, edit|paste
special|values) before sharing the workbook.

If you put this kind of utility in each workbook that uses them, you'll never
know where they are when you have to update them (and you'll find that you'll
want more functions or enhancements!).

ps.

Tell the user to put your file in a dedicated folder:
C:\excelutils\jamesutils.xls

And it'll make life easier when files are shared between users who have your
utility.

=====
In fact, if your users have access to a common network drive, you can store a
single copy on that drive (mark it readonly) and tell them to open the file when
they need it.

The fewer files that have the same code, the better.

 

[Dave Peterson]

I would put the .bas file somewhere easy to type and safe -- and away from the
XLStart folder.

But if you want

Option Explicit
Sub testme()

Dim VBProj As Object 'VBIDE.VBProject
Dim myFileName As String

myFileName = "C:\My Basic Modules\ParseOutNames.bas"

Set VBProj = Nothing
On Error Resume Next
Set VBProj = ActiveWorkbook.VBProject
On Error GoTo 0

If VBProj Is Nothing Then
MsgBox "Can't continue--I'm not trusted!"
Exit Sub
End If

VBProj.vbcomponents.Import myFileName

End Sub

======
But I've found that if I add the code to 10 workbooks, then those 10 workbooks
are used as the basis for 20 more (each) and the number just grows and grows and
grows.

And when I find a mistake, I can't fix them all.