Excel Error on SaveAs/Delete VBA Code

[kdinardi]

Using the following code:

    Sub DeleteProcedureFromModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
    
        ProcName = "DeleteThisProc"
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With
    End Sub
thisworkbook.saveas filename:=filesavename

The code seems to work fine as long as the "Trust Access to Visual Basic Project" is checked... however, when I get to the thisworkbook.saveas line, Excel completely errors out and closes, asking me to submit a report to Microsoft. Using VS 2005 I was able to debug and get an error code:

Unhandled exception at 0x651deb5b in EXCEL.EXE: 0xC0000005: Access violation reading location 0x753c0240.

Any ideas on what is going on? I am using Excel 2003! Thanks!

[Siddharth Rout]

This works for me provided

1) this line

thisworkbook.saveas filename:=filesavename

is before "End Sub" and not after that.

2) "filesavename" has been set

For Example

Dim filesavename as String

filesavename = "C:\MyFile.xls" '<~~ .xlsx for 2007

[kdinardi]

I am sorry, i must have posted wrong... filesavename is already defined and the code actually looks like this:

Dim filesavename as String
filesavename = "C:\MyFile.xls" 
call DeleteProcedureFromModule
thisworkbook.saveas filename:=filesavename

where

 Sub DeleteProcedureFromModule()
  Dim VBProj As VBIDE.VBProject
  Dim VBComp As VBIDE.VBComponent
  Dim CodeMod As VBIDE.CodeModule
  Dim StartLine As Long
  Dim NumLines As Long
  Dim ProcName As String
  
  Set VBProj = ActiveWorkbook.VBProject
  Set VBComp = VBProj.VBComponents("ThisWorksheet")
  Set CodeMod = VBComp.CodeModule
 
  ProcName = "Workbook_BeforeSave"
  With CodeMod
   StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
   NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
   .DeleteLines StartLine:=StartLine, Count:=NumLines
  End With
 End Sub

Hope this is better to understand!

[Siddharth Rout]

Where do you call the save as event? Is it before the DeleteProcedureFromModule() or after that?

Can u show me the complete code?

[kdinardi]

the code I use is as follows:

Dim filesavename as String
filesavename = "C:\MyFile.xls" 
call DeleteProcedureFromModule
thisworkbook.saveas filename:=filesavename

however, in the workbook that I am trying to save there is code within "thisworksheet" and its a "BeforeSave" Event... this is the code I am deleting with the DeleteProcedureFromModule code... it works fine and deletes the code... but on the next line 'thisworkbook.saveas filename:=filesavename" is where my error occurs... I hope this clearifies for you

[westconn1]

try a doevents before saveAs

note before save event will run again when the saveAs is called, try a boolean to prevent code from running twice (or more)