Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving workbook with VBA between computers
Hi all,
I have an Excel workbook with VBA macros. If I give my workbook to someone else to use on a different PC, I get compile errors. The only way out is ctrl-alt-del. Bringing up with macros disabled, I see three references that are missing: funcres, atpvbaen and solver (I am using the solver in the VBA). I can resolve the missing references on one PC, but then if I transfer it back to the other PC the references are missing again. So I tried some auto-open code to re-create all of the references. This doesn't work apparently because the code can't compile without these references. Kind of a Catch-22? Is there anything I can do here to allow multiple users on different PC's to use my workbook???? The code I run on open (but doesn't) is: Private Sub Workbook_Open() Application.Windows.Arrange xlArrangeStyleTiled Dim wb As Workbook Dim ReferencePath As String On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With AddIns("Solver Add-In") .Installed = False .Installed = True End With With AddIns("Analysis ToolPak") .Installed = False .Installed = True End With With AddIns("Analysis ToolPak - VBA") .Installed = False .Installed = True End With ReferencePath = Application.LibraryPath & "\SOLVER\SOLVER.XLA" wb.VBProject.References.AddFromFile ReferencePath ReferencePath = Application.LibraryPath & "\Analysis\funcres.XLA" wb.VBProject.References.AddFromFile ReferencePath ReferencePath = Application.LibraryPath & "\Analysis\atpvbaen.XLA" wb.VBProject.References.AddFromFile ReferencePath End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving workbook with VBA between computers
Are you all using the same version of Excel? I've noticed some VB variables
are not defined in earlier versions of Excel. "Revolvr" wrote: Hi all, I have an Excel workbook with VBA macros. If I give my workbook to someone else to use on a different PC, I get compile errors. The only way out is ctrl-alt-del. Bringing up with macros disabled, I see three references that are missing: funcres, atpvbaen and solver (I am using the solver in the VBA). I can resolve the missing references on one PC, but then if I transfer it back to the other PC the references are missing again. So I tried some auto-open code to re-create all of the references. This doesn't work apparently because the code can't compile without these references. Kind of a Catch-22? Is there anything I can do here to allow multiple users on different PC's to use my workbook???? The code I run on open (but doesn't) is: Private Sub Workbook_Open() Application.Windows.Arrange xlArrangeStyleTiled Dim wb As Workbook Dim ReferencePath As String On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With AddIns("Solver Add-In") .Installed = False .Installed = True End With With AddIns("Analysis ToolPak") .Installed = False .Installed = True End With With AddIns("Analysis ToolPak - VBA") .Installed = False .Installed = True End With ReferencePath = Application.LibraryPath & "\SOLVER\SOLVER.XLA" wb.VBProject.References.AddFromFile ReferencePath ReferencePath = Application.LibraryPath & "\Analysis\funcres.XLA" wb.VBProject.References.AddFromFile ReferencePath ReferencePath = Application.LibraryPath & "\Analysis\atpvbaen.XLA" wb.VBProject.References.AddFromFile ReferencePath End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving workbook with VBA between computers
All PC's run Office 2003 except for one which runs Office 2000. The OS is
either Win2000 or WinXP. What seems to be happening is the paths to the Analysis and Library files are different with different installations. VBA apparently cannot find the paths dynamically. "stacyjhaskins" wrote in message ... Are you all using the same version of Excel? I've noticed some VB variables are not defined in earlier versions of Excel. "Revolvr" wrote: Hi all, I have an Excel workbook with VBA macros. If I give my workbook to someone else to use on a different PC, I get compile errors. The only way out is ctrl-alt-del. Bringing up with macros disabled, I see three references that are missing: funcres, atpvbaen and solver (I am using the solver in the VBA). I can resolve the missing references on one PC, but then if I transfer it back to the other PC the references are missing again. So I tried some auto-open code to re-create all of the references. This doesn't work apparently because the code can't compile without these references. Kind of a Catch-22? Is there anything I can do here to allow multiple users on different PC's to use my workbook???? The code I run on open (but doesn't) is: Private Sub Workbook_Open() Application.Windows.Arrange xlArrangeStyleTiled Dim wb As Workbook Dim ReferencePath As String On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With AddIns("Solver Add-In") .Installed = False .Installed = True End With With AddIns("Analysis ToolPak") .Installed = False .Installed = True End With With AddIns("Analysis ToolPak - VBA") .Installed = False .Installed = True End With ReferencePath = Application.LibraryPath & "\SOLVER\SOLVER.XLA" wb.VBProject.References.AddFromFile ReferencePath ReferencePath = Application.LibraryPath & "\Analysis\funcres.XLA" wb.VBProject.References.AddFromFile ReferencePath ReferencePath = Application.LibraryPath & "\Analysis\atpvbaen.XLA" wb.VBProject.References.AddFromFile ReferencePath End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook page setup changing on different computers | Excel Discussion (Misc queries) | |||
saving a shared workbook is too slow on other 2 computers | Excel Worksheet Functions | |||
problems with Add-In event procedures when moving WB files between computers | Excel Programming | |||
How can I distribute Workbook with VBA code and library references to other computers | Excel Programming | |||
AddIn Default Path - Moving Spreadsheed between Users/Computers | Excel Programming |