Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save a Macro
I need to save 2 functions and a macro into multiple files (100 files in total). I am unable to use the personel.xls function because I will not be the only person needed to use the macro. Just to be clear, everyone who opens the file (from the database) must be able to run the macro. If no solution can be found, I will be forced to copy and paste the macro into the other 100 files. I can use all the help avialable. -- ducttape ------------------------------------------------------------------------ ducttape's Profile: http://www.excelforum.com/member.php...o&userid=30416 View this thread: http://www.excelforum.com/showthread...hreadid=510186 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save a Macro
Assign a toolbar button to the macro Make available to all Open workbooks. "ducttape" wrote: I need to save 2 functions and a macro into multiple files (100 files in total). I am unable to use the personel.xls function because I will not be the only person needed to use the macro. Just to be clear, everyone who opens the file (from the database) must be able to run the macro. If no solution can be found, I will be forced to copy and paste the macro into the other 100 files. I can use all the help avialable. -- ducttape ------------------------------------------------------------------------ ducttape's Profile: http://www.excelforum.com/member.php...o&userid=30416 View this thread: http://www.excelforum.com/showthread...hreadid=510186 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save a Macro
That does not do quite what I need I want to be able have the source code of the macro transfered to al the files. My macro is an Auto_Open which needs to be in all of the other files on the DB. -- ducttape ------------------------------------------------------------------------ ducttape's Profile: http://www.excelforum.com/member.php...o&userid=30416 View this thread: http://www.excelforum.com/showthread...hreadid=510186 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save a Macro
I fugured out what I needed to do. Every '.nvs' file in the folder 'MyPath' will have the module called 'Tracker' added to it. That module will be populated with code from the file 'pathtracker.txt' Here is my solution: Sub AllFolderFiles() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim VBComp As VBComponent Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim VBCM As CodeModule MyPath = "D:\Data\TrackerLayout" ChDir MyPath ' Does an operation to all .xnv files in a folder TheFile = Dir("*.xnv") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) ' if the module called tracker already exists, delete it If ModuleExists("Tracker") = True Then Set VBComp = ActiveWorkbook.VBProject.VBComponents("Tracker") ActiveWorkbook.VBProject.VBComponents.Remove VBComp End If ' add a module called tracker Set VBComp = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct _StdModule) VBComp.Name = "Tracker" ' in the module called tracker, add the code from the text document ModuleName = "Tracker" ImportFromFile = "D:\data\tracker\pathtracker.txt" Set VBCM = wb.VBProject.VBComponents(ModuleName).CodeModule VBCM.AddFromFile ImportFromFile ' close the active workbook, and go to the next file in the folder ActiveWorkbook.SaveAs FileName:=MyPath & "\" & TheFile, FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False wb.Close TheFile = Dir Loop Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ' tests to see if the module already exists Function ModuleExists(ModuleName As String) As Boolean On Error Resume Next ModuleExists = Len( _ ActiveWorkbook.VBProject.VBComponents(ModuleName). Name) < 0 End Function -- ducttape ------------------------------------------------------------------------ ducttape's Profile: http://www.excelforum.com/member.php...o&userid=30416 View this thread: http://www.excelforum.com/showthread...hreadid=510186 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save original data after macro is run again | New Users to Excel | |||
macro to save file automaticaly? | Excel Discussion (Misc queries) | |||
Macro to save | Excel Worksheet Functions | |||
help with macro to save repeating data entry | Excel Discussion (Misc queries) | |||
This one is tricky....Macro to save file as cell value x in di | Excel Discussion (Misc queries) |