Hi all,
Let say that I have over 2000 old excel files in one folder. Each of these
excel files contain one macro and this is the same macro in all excel files.

I want to be able to create a script that will open each file one by one,
delete or overwrite the current macro by a new one.

All my code to open excel files is done and working. My problem is that I
don't know
how to retrieve the current macro once the file is open. Neither how to
overwrite it.

Any help will be greatly appreciated.

Christian

Hi Christian,

this is just the opposite ... : )
but with a bit of editing... I guess it's the right way

Sub DeleteMyCode()
'On Error Resume Next

Dim m_ThisWB
Set m_ThisWB =
ThisWorkbook.VBProject.VBComponents.Item(1).Collec tion("ThisWorkbook").CodeModule

m_ThisWB.deletelines 4, m_ThisWB.countoflines
m_ThisWB.deletelines 2

DeleteComponent "Mod1"
DeleteComponent "Mod2"
DeleteComponent "Class1"

ThisWorkbook.Save
End Sub

Sub DeleteComponent(sz_CompName As String)
Dim vbc
Set vbc = ThisWorkbook.VBProject.VBComponents.Item(sz_CompNa me)
ThisWorkbook.VBProject.VBComponents.Remove vbc
End Sub

Hi ste,
thank's very much, you put me on the right way.
Here is a part of code I use to do what I want. There is more with it in an
hta.
Again, thank's for your help.

'--------------------------------------------------------------------------------------------------
'
================================================== ===============================================
' TITLE: ReplaceMacro
'
' PURPOSE: Open each Excel files in a specified folder, Delete all
vbcode in ThisWorkbook.
' Open a text file containing a new vbcode and insert it in module
' ThisWorkbook in each Excel files. Save the file and close Excel.
'
' PARAMETERS: strSourceFolder [STRING] = Folder containing Excel files to
work with.
'
' HOW TO USE: ReplaceMacro strSourceFolder
'
================================================== ===============================================
Sub ReplaceMacro(strSourceFolder)
Dim objFSO, objFolder, objFile, objComps, objComp
'Instantiate objects.
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Connect to folder to work with.
Set objFolder = objFSO.GetFolder(strSourceFolder)

'Loop through all files in current folder.
For Each objFile In objFolder.Files
ProgressWindow strSourceFolder & "\" & objFile.Name, "Open"
objXLS.Workbooks.Open strSourceFolder & "\" & objFile.Name 'Open Excel file.
Set objComps = objXLS.ActiveWorkbook.VBProject.VBComponents 'Connect to
VBE module.
ProgressWindow strSourceFolder & "\" & objFile.Name, "Del"
'Delete vb code in ThisWorkbook Project.
DelMacro objComps, objComp
'Insert module code in module named ThisWorkbook.
ProgressWindow strSourceFolder & "\" & objFile.Name, "Insert"
AddProcedure objComps
ProgressWindow strSourceFolder & "\" & objFile.Name, "Save"
objXLS.ActiveWorkbook.Save 'Save workbook and close it.
Next
End Su
'--------------------------------------------------------------------------------------------------
'
================================================== ===============================================
' TITLE: DelMacro
'
' PURPOSE: Delete all macro code in an Excel file.
'
' PARAMETERS: vbComps [OBJECT] = Contain components code module collection.
' cbComp [OBJECT] = Contain Vbcode itself in a module.
'
' HOW TO USE: DelMacro objComps, objComp
'
================================================== ===============================================
Sub DelMacro(vbComps, vbComp)
'Delete vb code in ThisWorkbook Project.
For Each vbComp In vbComps
Select Case vbComp.Name
Case "ThisWorkbook" 'If name of current module is ThisWorkbook,
Delete code in it.
With vbComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next
End Su
'--------------------------------------------------------------------------------------------------
'
================================================== ===============================================
' TITLE: AddProcedure
'
' PURPOSE: Add a new procedure in module ThisWorkbook based on a text
file.
'
' PARAMETERS: vbComps [OBJECT] = Contain components code module collection.
'
' HOW TO USE: AddProcedure objComps
'
================================================== ===============================================
Sub AddProcedure(objVBModule)
On Error Resume Next
Dim objFSO
Dim intLineNum
Dim strCode

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Open code.txt file which is the template macro code for Excel to copy in
module Achat.
Set strTemplFile = objFSO.OpenTextFile(SrcTemplateFolderName.Value &
"\code.txt")
'Connect to module Achat.
Set objVbModule =
objXLS.ActiveWorkbook.VBProject.VBComponents("This Workbook").CodeModule
'Loop through each lines in code.txt file and add each line in module Achat.
Do Until strTemplFile.AtEndOfStream
'Read line one by one.
strCode = strTemplFile.ReadLine
intLineNum = intLineNum + 1 'Increment line number counter.
On Error Resume Next
objVbModule.InsertLines intLineNum, strCode 'Add line of code in module.
Loop
'Clean up objects.
strTemplFile.Close
Set objFSO = Nothing
End Sub

"ste" wrote:

Hi Christian,

this is just the opposite ... : )
but with a bit of editing... I guess it's the right way

Sub DeleteMyCode()
'On Error Resume Next

Dim m_ThisWB
Set m_ThisWB =
ThisWorkbook.VBProject.VBComponents.Item(1).Collec tion("ThisWorkbook").CodeModule

m_ThisWB.deletelines 4, m_ThisWB.countoflines
m_ThisWB.deletelines 2

DeleteComponent "Mod1"
DeleteComponent "Mod2"
DeleteComponent "Class1"

ThisWorkbook.Save
End Sub

Sub DeleteComponent(sz_CompName As String)
Dim vbc
Set vbc = ThisWorkbook.VBProject.VBComponents.Item(sz_CompNa me)
ThisWorkbook.VBProject.VBComponents.Remove vbc
End Sub

Hi Chris,

To cycle through the files, see VBA help on the Dir function or adapt Ron de
Bruin's sample code at:

http://www.rondebruin.nl/copy3.htm

To delete, amend or insert code, see Chip Pearson's page on programming to
the VBE at:

http://www.cpearson.com/excel/vbe.htm

I would suggest, however, that rather than replacing the existing code in
each of the 2000 workbooks, you should consider creating a single Addin and
making the Addin available to all appropriate users.

In this way, future editing could be limited to a single file,


---
Regards,
Norman



"ChrisSaw" wrote in message
...
Hi all,
Let say that I have over 2000 old excel files in one folder. Each of these
excel files contain one macro and this is the same macro in all excel
files.

I want to be able to create a script that will open each file one by one,
delete or overwrite the current macro by a new one.

All my code to open excel files is done and working. My problem is that I
don't know
how to retrieve the current macro once the file is open. Neither how to
overwrite it.

Any help will be greatly appreciated.

Christian

I've already visited these sites and they are very helpful. But in this case,
this customer want an external solution to manage his excel files because it
was a one
shot deal.

So I've built a vbscript in an hta to do this.
But thank's for your suggestion I appreciate.

"Norman Jones" wrote:

Hi Chris,

To cycle through the files, see VBA help on the Dir function or adapt Ron de
Bruin's sample code at:

http://www.rondebruin.nl/copy3.htm

To delete, amend or insert code, see Chip Pearson's page on programming to
the VBE at:

http://www.cpearson.com/excel/vbe.htm

I would suggest, however, that rather than replacing the existing code in
each of the 2000 workbooks, you should consider creating a single Addin and
making the Addin available to all appropriate users.

In this way, future editing could be limited to a single file,


---
Regards,
Norman



"ChrisSaw" wrote in message
...
Hi all,
Let say that I have over 2000 old excel files in one folder. Each of these
excel files contain one macro and this is the same macro in all excel
files.

I want to be able to create a script that will open each file one by one,
delete or overwrite the current macro by a new one.

All my code to open excel files is done and working. My problem is that I
don't know
how to retrieve the current macro once the file is open. Neither how to
overwrite it.

Any help will be greatly appreciated.

Christian

Christian,

If the code is exactly the same in all these workbooks, it may make
sense to create an addin to contain the code. You should save code
maintenance headaches over time.

Paul

-------------------------------------------------------------------------

Hi all,
Let say that I have over 2000 old excel files in one folder. Each of these
excel files contain one macro and this is the same macro in all excel files.

I want to be able to create a script that will open each file one by one,
delete or overwrite the current macro by a new one.

All my code to open excel files is done and working. My problem is that I
don't know
how to retrieve the current macro once the file is open. Neither how to
overwrite it.

Any help will be greatly appreciated.

Christian