Personal.xls vs Add-in

[Guest]

I've written some macros at work that other people would like to use. Rather
than using an add-in, I was wondering if it would be better to write a macro
to copy the functions to the other users' "Personal.xls" workbook using Chip
Pearson's method.

I tried the code below, but I can't get it to work. Does this mean that
copying modules to "Personal.xls" is prohibited? In which case, it would be
better to use an Add-in. If I add more macros to the Add-in in the future,
will they automatically be loaded to other users' workbooks (once the add-in
is installed)?

----------------------------------------------------------------------------------------
Sub CopyModule()

On Error GoTo Errorhandler
Dim FName As String, fldr As String

' Obtain username
uname = InputBox("Enter Username", "Username")
fldr = "C:\Documents and Settings\" & uname & "\Application
Data\Microsoft\Excel\XLSTART"
personal = fldr & "\Personal.xls"

' Test to see if "Personal.xls" exists
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(personal) Then
Workbooks.Add
ActiveWorkbook.SaveAs (personal)
End If

' Copy this macro to "Personal.xls"
With ThisWorkbook
FName = .Path & "\code.txt"
.VBProject.VBComponents("sub_sumColor").Export FName
End With
Workbooks(personal).VBProject.VBComponents.Import FName
Kill FName

Exit Sub

Errorhandler:
msg = "There was a problem copying the macro. Please verify that " & _
"you have followed all the instructions. If you still need help, see the
Intern."
Title = "Fatal Error"
Style = vbOKOnly + vbCritical
response = MsgBox(msg, Style, Title)

End Sub

 

[Peter T]

I haven't tried this directly with my Personal but it should work if you
rename sPers.

Add a module to Thisworkbook named modCustom and put some test code in it.
In another module the following -

Sub test()
Dim sPath
Dim wb As Workbook
Dim vbComps As Object ' VBComponents
Dim vbComp As Object ' VBComponent
Const sPers As String = "PersonalTest.xls" ' change to Personal.xls after
testing

On Error Resume Next
Set wb = Workbooks(sPers)
On Error GoTo errH

If wb Is Nothing Then
Set wb = Workbooks.Add
wb.Windows(1).Visible = False
sPath = Application.StartupPath
If Right(sPath, 1) <> Application.PathSeparator Then
sPath = sPath & Application.PathSeparator
End If
wb.SaveAs sPath & sPers
ElseIf wb.ReadOnly Then
MsgBox "Can't add code", , "Multi xl instances"
Exit Sub
End If

Set vbComps = wb.VBProject.VBComponents

On Error Resume Next
Set vbComp = vbComps("modCustom")
On Error GoTo errH

If Not vbComp Is Nothing Then
If MsgBox("modCustom already exists, replace ?") Then
vbComps.Remove vbComp
Else
Exit Sub
End If
End If

ThisWorkbook.VBProject.VBComponents("modCustom").Export _
"C:\modCustom.bas"
vbComps.Import "C:\modCustom.bas"
Kill "C:\modCustom.bas"

wb.Save

Exit Sub
errH:
MsgBox Err.Description

End Sub

You'll want to delete or remove PersonalTest.xls from the startup file after
testing

Regards,
Peter T

 

[Guest]

Peter,

Thanks very much! The code works perfectly. I will be using it to have my
coworkers add macros to their code.

Thanks,
Pflugs