Extracting VBA from a Excel spreadsheet [closed]
Previous versions of Excel and Access (prior to 2003) supported VBA Source Code Version Control via an add-in. I have used this very effectively in Office 2000.
Visual SourceSafe (VSS) support for VBA was dropped with the release of Office 2003, but the add-in that was shipped with Office XP Developer apparently works with Office 2003.
Microsoft Knowledge Base articles:
Failing that you can use this code to extract the VBA code (from here but it was missing the final object cleanups). Read the webpage for caveats:
option explicitConst vbext_ct_ClassModule = 2Const vbext_ct_Document = 100Const vbext_ct_MSForm = 3Const vbext_ct_StdModule = 1MainSub Main Dim xl Dim fs Dim WBook Dim VBComp Dim Sfx Dim ExportFolder If Wscript.Arguments.Count <> 1 Then MsgBox "As the only argument, give the FULL path to an XLS file to extract all the VBA from it." Else Set xl = CreateObject("Excel.Application") Set fs = CreateObject("Scripting.FileSystemObject") xl.Visible = true Set WBook = xl.Workbooks.Open(Trim(wScript.Arguments(0))) ExportFolder = WBook.Path & "\" & fs.GetBaseName(WBook.Name) fs.CreateFolder(ExportFolder) For Each VBComp In WBook.VBProject.VBComponents Select Case VBComp.Type Case vbext_ct_ClassModule, vbext_ct_Document Sfx = ".cls" Case vbext_ct_MSForm Sfx = ".frm" Case vbext_ct_StdModule Sfx = ".bas" Case Else Sfx = "" End Select If Sfx <> "" Then On Error Resume Next Err.Clear VBComp.Export ExportFolder & "\" & VBComp.Name & Sfx If Err.Number <> 0 Then MsgBox "Failed to export " & ExportFolder & "\" & VBComp.Name & Sfx End If On Error Goto 0 End If Next xl.Quit Set fs = Nothing Set xl = Nothing End IfEnd Sub
The other answers to this question address it pretty well, but if you're just starting to write the code, look into VSTO. It provides managed code for Office, and best of all, you can even do it in C# or whatever .NET language strikes your fancy. It also works with Excel 2003, which is a bonus, in your case.