how to delete all macros - save file with cell-name

T

Tom

Hi,

Is it possible to make a macro that deletes all macro's?
I've got a large xls file (7MB) and I want to save only
the fill-in cells.
I've made a macro that copies the fill-in cells to a
sheet and deletes all the others sheets.
I use an auto_open macro and after deleting the sheets
the auto_open macro cannot be played, so I have te delete
it.

Second question : How can I save a file with the value
(name) of a cell ?
ex : A1 = TOM
I want to make a marco that saves the file as:
Version 1.0 TOM

Greez
Tom
 
P

papou

Hi Tom
Sub DeleteAllMacros()
Dim Composantvbe As Object
With ActiveWorkbook.VBProject
For Each Composantvbe In .VBComponents
If Composantvbe.Type = 100 Then
With Composantvbe.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove Composantvbe
End If
Next Composantvbe
End With
End Sub

To save your file with value in A1:
Activeworkbook.SaveAs "Version 1.0" &
Worksheets("YourSheet").Range("A1").Value & ".xls"

HTH
Cordially
Pascal
 
G

Guest

Perfect !

Can I open an xls file which name I don't know.
So I want to make a macro that opens an xls file that
will be selected by the user(browse) and than I want to
copy a sheet of this file.

Greez
Tom
 
G

Guest

The macro DeleteAllMacros doesn't work.
Message : Methiode VBPtoject of object_workbook has
failed.

And the line : With ActiveWorkbook.VBProject is colored
yellow.
What can I do ?
Tom
 
P

papou

Tom
Sub OpenFile()
Filename = Application.Dialogs(xlDialogOpen).Show
If Filename <> False Then
On Error Resume Next
Workbooks(ActiveWorkbook.Name).Sheets("YourSheet").Copy
If Err <> 0 Then MsgBox "The active workbook has no sheet named " &
"YourSheet"
Else: MsgBox "Opening operation has been cancelled", vbInformation
End If
End Sub

HTH
Cordially
Pascal
<[email protected]> a écrit dans le message de
Perfect !

Can I open an xls file which name I don't know.
So I want to make a macro that opens an xls file that
will be selected by the user(browse) and than I want to
copy a sheet of this file.

Greez
Tom
 
P

papou

Tom
Does the active workbook contain your macros?
If not you may change with ThisWorkbook

HTH
Cordially
Pascal

<[email protected]> a écrit dans le message de
The macro DeleteAllMacros doesn't work.
Message : Methiode VBPtoject of object_workbook has
failed.

And the line : With ActiveWorkbook.VBProject is colored
yellow.
What can I do ?
Tom
 
G

Guest

Pascal,

I have changed ActiveWorkbook into This Workbook but it
doesnt 't work yet.
This is the current macro :

Sub DeleteAllMacros()
Dim Composantvbe As Object
With ThisWorkbook.VBProject
For Each Composantvbe In .VBComponents
If Composantvbe.Type = 100 Then
With Composantvbe.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove Composantvbe
End If
Next Composantvbe
End With
End Sub

PS : I have only one XLS file open


The Macro "openfile" doens't work eather.
Message : The Variable "Filename" is not defined.
I can use this macro :

Workbooks.Open(Filename:="C:\Export Quogen NeXspan
1.0.xls").RunAutoMacros _
Which:=xlAutoOpen

But the problem here is that the file must be named as :
Export Quogen NeXspan 1.0.xls
I want that the user (not me) can select himself an XLS
file (by browse) because the file won't be saved
as "Export Quogen NeXspan 1.0.xls" by the user.
I don't know the name that the other user wil use to save
the file

Tom

PS : I'm working in Office 2003.
 
P

papou

Tom
I am also using Excel 2003 and the delete macros routine works fine so there
may be some other issue I honestly cannot see, unless you are executing the
code directly from the VBE which could be the reason?
Please also bear in mind that you won't be able to undo the deletion of
code.
This means that you should perhaps save your workbook before deleting and
save with a different name once macros are deleted.
As regards FileName just add a:
Dim FileName

Hope this helps
Cordially
Pascal

<[email protected]> a écrit dans le message de
Pascal,

I have changed ActiveWorkbook into This Workbook but it
doesnt 't work yet.
This is the current macro :

Sub DeleteAllMacros()
Dim Composantvbe As Object
With ThisWorkbook.VBProject
For Each Composantvbe In .VBComponents
If Composantvbe.Type = 100 Then
With Composantvbe.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove Composantvbe
End If
Next Composantvbe
End With
End Sub

PS : I have only one XLS file open


The Macro "openfile" doens't work eather.
Message : The Variable "Filename" is not defined.
I can use this macro :

Workbooks.Open(Filename:="C:\Export Quogen NeXspan
1.0.xls").RunAutoMacros _
Which:=xlAutoOpen

But the problem here is that the file must be named as :
Export Quogen NeXspan 1.0.xls
I want that the user (not me) can select himself an XLS
file (by browse) because the file won't be saved
as "Export Quogen NeXspan 1.0.xls" by the user.
I don't know the name that the other user wil use to save
the file

Tom

PS : I'm working in Office 2003.
 
T

Tom

Pascal,

Openfile works great !
Deleteallmarcros doenst't work but I have found another
solution.
I've copied te worksheet to a new map and than saved it
as the name of cell A2 like you told me.

Thank you very much for your support!
Tom
 
Top