VBA: Compile error: Expected end sub

[glhfgtd]

i'm trying to empty "ThisWorkbook" module for all excel files in the same directory. Here is my code, but it pops up an Compile error: Expected end sub when i run it.
Please help!

Sub RemoveWbkCode()
Dim itm As String
    itm = Dir(ThisWorkbook.Path & "\*.xls")
    Do While itm <> ""
        If itm = ThisWorkbook.Name Then GoTo 66
        Workbooks.Open ThisWorkbook.Path & "\" & itm
        With ActiveWorkbook.VBProject.VBComponents("thisworkbook").CodeModule
            .DeleteLines 1, .CountOfLines
        End With
        ActiveWorkbook.Close True
66      itm = Dir
    Loop
End Sub

 

[xenou]

66 itm = Dir
probably should be
66: itm = Dir

Or, recoded without GoTo:

Sub RemoveWbkCode()
Dim itm As String
    itm = Dir(ThisWorkbook.Path & "\*.xls")
    Do While itm <> ""
        If itm <> ThisWorkbook.Name Then
	    Workbooks.Open ThisWorkbook.Path & "\" & itm
            With ActiveWorkbook.VBProject.VBComponents("thisworkbook").CodeModule
                .DeleteLines 1, .CountOfLines
            End With
            ActiveWorkbook.Close True
        End If
        itm = Dir
    Loop
End Sub

This looks like dangerous code ... be careful!

 

[wigi]

Welcome to MrExcel.

It should be 66: instead of 66

However, do not use this kind of structures. By rewriting the if with an inequality (<>) you avoid spaghetti type coding.

 

[wigi]

And Dir should be Dir()

 

[wigi]

And Dir should be Dir()

Correction: that does not need to be the case, I verified it. Though I always thought it was

 

[xenou]

^^ I was ready to believe you ... I've seen Dir() just about all the time I think...

 

[glhfgtd]

thank you Xenou and wigi for the prompt response.
I tried Xenou's code but it results the same compile error.
then i F8 each line of code to see where it causes the error and found out that the target excel contains Workbook_open, Workbook_BeforeClose and Workbook_beforesave events, it seems they are causes of conflict.
Would be possible to clear ThisWorkbook module without open the target workbooks? or is there any other way to clean them?
thanks a lot!

 

[wigi]

or is there any other way to clean them?

I never tried it, but if you save them as .xlsx files, it might be possible. I do not know whether there will be messages from Excel because you save a workbook with macros as a workbook without.

 

[xenou]

I'm afraid I really don't know. I've never been in such a situation. Many things VBE-related are covered at Chip Pearson's site. It's not entirely clear why this should cause a failure ... why do you need to clear vba from all excel files in a folder?

 

[glhfgtd]

as part of my work required process. I have to add vba to 30 plus excel files and remove all the codes before achieve them