Excel: Extract VBA code to text files (for diffing)IntroductionProvided here is a simple tool which will open an Excel XLS file and extract all of its VBA code it to text files. The purpose of this tool is to give you a way to use text-based diff tools (e.g. Beyond Compare or WinMerge) on two versions of an Excel spreadsheet to see what has changed in the VBA code. Note that the extracted code should not be pasted back into Excel directly. Unfortunatley, it will contain some additional junk which will confuse the VBA editor. (You could remove the extra stuff and then paste the code, though.) The tool is actually a short VBScript, .vbs file, which automates Excel. You should run it from a command prompt, giving it the name of the XLS file whose VBA wish to extract. Users of Directory Opus will find my Diff/Merge Toolbar useful since it includes a button to run this script. If you are using the toolbar with Opus then you just have to select one or more XLS files and click the button to extract their VBA code. DownloadExportVBAfromXLS.zip (2k) (PGP signature) SourceHere is the script in text format so you can see how it works before you download it: option explicit Const vbext_ct_ClassModule = 2 Const vbext_ct_Document = 100 Const vbext_ct_MSForm = 3 Const vbext_ct_StdModule = 1 Main Sub 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 End If End Sub HistoryVersion 1 (31st May 2005)
Alternative ToolYou may also be interested in SourceTools.xla. I have no connection to SourceTools.xla; it's just linked here in case you find it fits your needs better than my script. Use whichever is best for you. |