Delete module while still running

Question

I want to be able to delete a couple of modules in an Excel spreadsheet while the macro is still running.  I have found code that will delete the modules but only when the macro completes running does the module actually delete.

My macro continues on to range value data, delete complete sheets and then save the remaning 1 sheet as a report, then quits Excel. When the new file is opened the Modules are still in the new file.

If I step through the DeletethisModule step by step and complete it the module are deleted.  If I step through the whole macro they do not delete, thinking because the macro is still running.

I have tried DoEvents but no luck.

Ideally I'd like to delete all code except for the last bit to save the new file (1 remaining sheet in my case), but otherwise to delete the modules.

Can someone help with this please?

current code to delete modules is

Sub DeleteThisModule()

Dim vbCom As Object

    Set vbCom = Application.VBE.ActiveVBProject.VBComponents

    vbCom.Remove VBComponent:= _

    vbCom.Item("SQL")

    vbCom.Remove VBComponent:= _

    vbCom.Item("VBACode")

End Sub

and this is called from a Private sub Workbook_Open(), on ThisWookbook

Thanks

Dean

Answer 1

ok,

you have 10 sheets

do you want to export all data (10 sheets)

in  a new workbook in one sheet ?

(values and formats only?)

if so, try this..

The code below, creates a new workbook (1 sheet)

workbook name is 'Report' and sheet1 name is 'ALL DATA'

(copy/paste values and formats)

and SaveAs in same folder as 'Source' workbook.

Sub abc()

Dim myWB As Workbook, WB As Workbook

Dim r As Long, N As Long, i As Integer

Dim myPath As String

Set myWB = ThisWorkbook

Application.ScreenUpdating = False

myPath = ThisWorkbook.Path & ""

Set WB = Workbooks.Add

For i = WB.Sheets.Count To 2 Step -1

Application.DisplayAlerts = False

Sheets(i).Delete

Application.DisplayAlerts = True

Next i

WB.Sheets(1).Name = "ALL DATA"

N = 1

On Error Resume Next

For i = 1 To myWB.Sheets.Count

r = myWB.Sheets(i).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

myWB.Sheets(i).Rows("1:" & r).Copy

Cells(N, 1).PasteSpecial xlValues

Cells(N, 1).PasteSpecial xlFormats

Application.CutCopyMode = False

N = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2

Next

Application.DisplayAlerts = False

WB.SaveAs (myPath & "Report.xls")

Application.DisplayAlerts = True

Application.ScreenUpdating = True

MsgBox "Workbook ""Report"" has been updated"

WB.Close

End Sub

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXxxxx

and..

 in ThisWorkbook write:

Private Sub Workbook_Open()

Run "abc"

End Sub

Answer 2

Thank you, thank you.

It almost does it.  It's only the one sheet from the origional file that needs saving currently called "Report" if that helps,

also is it possiblt to save the format, column and row withds / heights, cell formatting etc.  This sheet is currently printed as a report by some users and others still copy data from it so it's formatting is important.

As well as the page setup!

Answer 3

Ok, yes.possibly got stuck on the wrong train of thought.

The whole workbook starts with 10 sheets, some Macros on the Workbook and a couple of modules.  In the end all I want is 1 sheet "Report" formatted as it currently is (including the print range), but range valued and no macros, modules etc.

However this is to be saved in a new file

When the workbook is opened it refreshes data on msqueries, which updates data and calculations and eventually everything is updated on the "Report" sheet, again calculations. 

I've got the macros to range value the "Report" sheet, delete the now unused query sheets, and even save just the "Report" sheet (with print setting) but can't get rid of the modules.

Can I save just the "Report" sheet as a new file / workbook with out all the other hassle?

Thanks

Dean

Answer 4

Hi,

if you want to delete Modules-Macros in order to create a new workbook

without Modules-macros, then try this:

From Source-Macro Workbook,

add  a  New Workbook and export your data

(using copy-paste values only)

I am not sure, but i don't think that you can delete module or macro via macro..