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]

Wigi's idea is good. I you could save the files as xlsx then back to xls - that should work. Anything involving 30 files is probably already a bad idea, though. Centralize the data.

 

[glhfgtd]

i can't save to xlsx since my workplace only has office2003 available

 

[wigi]

I won't be of help since I did not play around a lot with that stuff in the VBE. Frankly, I do not want to mess around with these kind of operations. Hope someone else will come in with more experience on this one. As xenou wrote, whenever possible reorganize the files and the code and your problem will be far less daunting.

 

[xenou]

It may be possible to save in xlsx if you have the compatibility pack installed, even if you have 2003, so this may not be ruled out in fact (I'm not sure). As I mentioned, you could investigate chip pearson's excel site for more info - I'm afraid I rarely write code to manipulate the vb components so I've no experience with this kind of thing and can't take it on tonight. There's probably code available out there somewhere if you google Excel strip all code from workbook / Excel remove all code from workbook / Excel delete all code and so on... worth a try anyway. Though if you have code all over the place in 30 workbooks I think you need to step back from this and ask why we are adding so much code to workbooks that should not, ultimately, have any code in them at all ...

Chip's site:
http://www.cpearson.com/excel/vbe.aspx

ξ