• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA: Delete macros from file

Monty

Monty

Well-Known Member
Hello Everyone.

Vierd Question....Bu thats the requirment on which am working as part of the project.

We have some macro files where the code is written in Sheets...That Meens even if we save the file as .XLSX still code exist..

So need to have a macro which open another macro file and delete where the code exists in the sheets...and we know the the password of that macro file.

Any thoughts?
 
We have some macro files where the code is written in Sheets...That Meens even if we save the file as .XLSX still code exist..
Click to expand...

I don't agree with that point as saving the file as XLSX will remove any codes in modules or sheets module
 
Generally if the sheet code module existed in another workbook and you need a way to remove this module try the following
Suppose the closed workbook "Sample.xlsm" and the sheet name that has the code in sheet module that needs to be removed is "Sheet1"
Code: 
'Reference : "Microsoft Visual Basic For Applications Extensibility"

Sub RemoveSheetModule()
    Dim wb      As Workbook
    Dim sh      As Worksheet
    Dim str    As String
   
    Application.ScreenUpdating = False
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Sample.xlsm")
       
        For Each sh In wb.Worksheets
            Select Case sh.Name
                Case "Sheet1"
                    str = sh.CodeName
                    With wb.VBProject.VBComponents(str).CodeModule
                        .DeleteLines 1, .CountOfLines
                    End With
                Case Else
            End Select
        Next sh
       
        wb.Close True
    Application.ScreenUpdating = True
   
    MsgBox "Done...", 64
End Sub
 
Monty said:
We have some macro files where the code is written in Sheets...That Meens even if we save the file as .XLSX still code exist..
Click to expand...
Hi, Monty!
I'd pay for giving a look at one of those files, and Satya Nadella too.
Regards!
 
YasserKhalil said:
I don't agree with that point as saving the file as XLSX will remove any codes in modules or sheets module[/quoteyes by saving .xlsm extention to .XLSX will remove macro coding expect if code is written in Sheets or workbook.
Click to expand...
 
YasserKhalil said:
Generally if the sheet code module existed in another workbook and you need a way to remove this module try the following
Suppose the closed workbook "Sample.xlsm" and the sheet name that has the code in sheet module that needs to be removed is "Sheet1"
Code: 
'Reference : "Microsoft Visual Basic For Applications Extensibility"

Sub RemoveSheetModule()
    Dim wb      As Workbook
    Dim sh      As Worksheet
    Dim str    As String
  
    Application.ScreenUpdating = False
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Sample.xlsm")
      
        For Each sh In wb.Worksheets
            Select Case sh.Name
                Case "Sheet1"
                    str = sh.CodeName
                    With wb.VBProject.VBComponents(str).CodeModule
                        .DeleteLines 1, .CountOfLines
                    End With
                Case Else
            End Select
        Next sh
      
        wb.Close True
    Application.ScreenUpdating = True
  
    MsgBox "Done...", 64
End Sub
[/quote

Tried these googling but no luck...as I said by saving .XLSX we came remove module's code permanently but not not the sheet code...hanks for your efforts.
Click to expand...
 
YasserKhalil said:
I wish I see a sample that conform with your post ...
I think it is impossible ...!!
Click to expand...
It is not totally impossible ;)

Open any xlsm workbook containing code in sheet modules and save it as xlsx. Without closing the file open Visual Basic Editor. The code will still remain there.

It only goes away once you close the workbook and re-open it!
 
Monty said:
We have some macro files where the code is written in Sheets...That Meens even if we save the file as .XLSX still code exist..
Click to expand...
Could we be talking about Excel 4.0 macros here?
Monty, post one of your workbooks here.
 
You must log in or register to reply here.
Back
Top