Code in workbook module still runs after saving file as xlsx
Using a button on a VB.NET form, the file is .xlsm
opened and saved as .xlsx
. The code in the file .xlsx
( Workbook_BeforeClose
) is NOT deleted after the file is saved, so when I want to close the file, the code runs! Code is missing after reopening the file.
This is my VB.NET class:
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkbooks As Excel.Workbooks
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
xlApp = New Excel.Application
xlWorkbooks = xlApp.Workbooks
xlWorkBook = xlWorkbooks.Open("C:\Temp\testTemplate.xlsm")
xlApp.DisplayAlerts = False
xlWorkBook.SaveAs(Filename:="C:\Temp\testTemp.xlsx", FileFormat:=51) '51=xlOpenXMLWorkbook
xlApp.DisplayAlerts = True
xlApp.Visible = True
'Clean Up
releaseObject(xlWorkBook)
releaseObject(xlWorkbooks)
releaseObject(xlApp)
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
GC.WaitForPendingFinalizers()
End Try
End Sub
End Class
This is an Excel file, a workbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "event still runs"
End Sub
How to save the file correctly so that no code remains in it?
source to share
You are right, the code is not removed until it is closed. I suppose you could set some type of flag in the xlsm event BeforeClose
that checks its file type and only fires if it's xlsm. Or you can do Worksheets.Copy
instead SaveAs
and save the resulting workbook (which will not contain VBA) as xlsx, but there may be problems with cleanup fixes. Or you can install xlApp.EnableEvents=False
, close the newly saved xlsx, set it back to True and reopen the xlsx.
Here is the post I wrote on the second option: http://yoursumbuddy.com/copy-an-xlsm-xlsx/
source to share