t2t
10-22-2009, 01:43 PM
Hello guys,
congrats for this great forum, best I have seen ever!
My doubt is:
I have this model sheet (Bridge Graph 3.0.xls), which, by the way, started at 1.0 (of course), and it has many macros on it.
The problem is that users constantly copy the worksheet to other files, and then the macros stop working (specially when they move the sheet).
I found out that, the solution would be to place the macros on the PERSONAL.XLS file, and make the proper commands from there.
OK, but the regular users are not in the mood to import the macros :banghead: , I need to make a button to make the personal ready to work on diferent machines.
From my research I found out this article:
(can't post link, but it is in this site:
Instructions for use: Create the Personal.xls workbook and import files )
Which is great, but it deals with importing the macros from a BAS file, not from the original one.
I tried to adapt it, but I cant make the thing work!
So, here is the situation:
File: "Bridge Graph 3.0.xls"
Option Explicit
Option Compare Text
Sub AddPersonal()
Application.ScreenUpdating = False
Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, FileName
Dim blnPersonal As Boolean
Dim FSO As Object, Folder As Object, File As Object
Dim PersonalXLS As Workbook
'Create an instance of the FileSystemObject and obtain the
'excel startup folder
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(Application.StartupPath)
'See if Personal.xls already exists
For Each File In Folder.Files
If UCase(File.Name) = "PERSONAL.XLS" Then
If WorkbookIsOpen(File.Name) Then
Set PersonalXLS = Application.Workbooks(File.Name)
Else
Set PersonalXLS = Application.Workbooks.Open(File.Path)
End If
blnPersonal = True
Exit For
End If
Next
'Prompt user to continue if Personal.xls was found
If blnPersonal = True Then
If MsgBox("Personal.xls already exists." & vbCrLf & vbCrLf & _
"Would you like to add modules to it?", vbYesNo, _
"Personal.xls Exists") = vbNo Then GoTo ExitHere
End If
'If Personal.xls was not found, create Personal.xls workbook and hide it
If blnPersonal = False Then
Set PersonalXLS = Application.Workbooks.Add
PersonalXLS.SaveAs (Application.StartupPath & "\PERSONAL.xls")
Windows("PERSONAL.xls").Visible = False
Windows("Bridge Graph 3.0.xls").Activate
'Import the Files to the Personal.xls workbook
With ActiveSheet.VBProject.VBComponents.Select
.PersonalXLS.VBProject.VBComponents.Copy
End With
ExitHere:
'Clean up
PersonalXLS.Save
Set PersonalXLS = Nothing
Set FSO = Nothing
Set Folder = Nothing
Set File = Nothing
Application.ScreenUpdating = True
End Sub
I know I must be doing something stupid, but if you guys could help I would really appreciate it.
Best regards,
Thiago Scaldaferri
congrats for this great forum, best I have seen ever!
My doubt is:
I have this model sheet (Bridge Graph 3.0.xls), which, by the way, started at 1.0 (of course), and it has many macros on it.
The problem is that users constantly copy the worksheet to other files, and then the macros stop working (specially when they move the sheet).
I found out that, the solution would be to place the macros on the PERSONAL.XLS file, and make the proper commands from there.
OK, but the regular users are not in the mood to import the macros :banghead: , I need to make a button to make the personal ready to work on diferent machines.
From my research I found out this article:
(can't post link, but it is in this site:
Instructions for use: Create the Personal.xls workbook and import files )
Which is great, but it deals with importing the macros from a BAS file, not from the original one.
I tried to adapt it, but I cant make the thing work!
So, here is the situation:
File: "Bridge Graph 3.0.xls"
Option Explicit
Option Compare Text
Sub AddPersonal()
Application.ScreenUpdating = False
Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, FileName
Dim blnPersonal As Boolean
Dim FSO As Object, Folder As Object, File As Object
Dim PersonalXLS As Workbook
'Create an instance of the FileSystemObject and obtain the
'excel startup folder
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(Application.StartupPath)
'See if Personal.xls already exists
For Each File In Folder.Files
If UCase(File.Name) = "PERSONAL.XLS" Then
If WorkbookIsOpen(File.Name) Then
Set PersonalXLS = Application.Workbooks(File.Name)
Else
Set PersonalXLS = Application.Workbooks.Open(File.Path)
End If
blnPersonal = True
Exit For
End If
Next
'Prompt user to continue if Personal.xls was found
If blnPersonal = True Then
If MsgBox("Personal.xls already exists." & vbCrLf & vbCrLf & _
"Would you like to add modules to it?", vbYesNo, _
"Personal.xls Exists") = vbNo Then GoTo ExitHere
End If
'If Personal.xls was not found, create Personal.xls workbook and hide it
If blnPersonal = False Then
Set PersonalXLS = Application.Workbooks.Add
PersonalXLS.SaveAs (Application.StartupPath & "\PERSONAL.xls")
Windows("PERSONAL.xls").Visible = False
Windows("Bridge Graph 3.0.xls").Activate
'Import the Files to the Personal.xls workbook
With ActiveSheet.VBProject.VBComponents.Select
.PersonalXLS.VBProject.VBComponents.Copy
End With
ExitHere:
'Clean up
PersonalXLS.Save
Set PersonalXLS = Nothing
Set FSO = Nothing
Set Folder = Nothing
Set File = Nothing
Application.ScreenUpdating = True
End Sub
I know I must be doing something stupid, but if you guys could help I would really appreciate it.
Best regards,
Thiago Scaldaferri