#1   Report Post  
Posted to microsoft.public.excel.misc
ducttape
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
exceluserforeman
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
ducttape
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
ducttape
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
save original data after macro is run again MINAL ZUNKE New Users to Excel 3 July 7th 05 12:48 PM
macro to save file automaticaly? david Excel Discussion (Misc queries) 1 May 6th 05 05:21 PM
Macro to save John Excel Worksheet Functions 2 April 29th 05 12:38 AM
help with macro to save repeating data entry Tom Excel Discussion (Misc queries) 0 February 16th 05 05:24 AM
This one is tricky....Macro to save file as cell value x in di Andy Excel Discussion (Misc queries) 4 November 26th 04 09:52 AM


All times are GMT +1. The time now is 07:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"