Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls vs Add-in
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 ----------------------------------------------------------------------------------- Thanks, Pflugs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls vs Add-in
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").E xport _ "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 "Pflugs" wrote in message ... 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 -------------------------------------------------------------------------- --------- Thanks, Pflugs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls vs Add-in
Peter,
Thanks very much! The code works perfectly. I will be using it to have my coworkers add macros to their code. Thanks, Pflugs "Peter T" wrote: 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").E xport _ "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 "Pflugs" wrote in message ... 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 -------------------------------------------------------------------------- --------- Thanks, Pflugs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Personal macro workbook and personal.xls | Excel Discussion (Misc queries) | |||
personal.htm & personal.xls in Macro | Excel Programming | |||
Personal.xls | Excel Programming | |||
Personal.xls | Excel Programming |