To Extract/Display Macro (VBA) from .xls files using VB6 (without openning Excel)

Question

Hi all,

I understand it is possible to do so using excel macro, see: http://stackoverflow.com/questions/49724/programmatically-extract-macro-vba-code-from-word-2007-docs But What I want to do here is to use VB6 to make an application which does the same thing. I am having problem on how to point to the excel workbook (thisworkbook.VBproject is used in the example above). Is it possible to select any .xls file from the hd,say c:\try.xls, and extract/show its macro? Please advise!

Thanks a bunch! Dean

Answer 1

Set a reference to the Microsoft Excel 12.0 Object Library (or whatever version required) and use the workbook's VBProject.VBComponents collection e.g.

Sub ExportCode()
  Dim app As Excel.Application
  Set app = New Excel.Application

  Dim wb As Excel.Workbook
  Set wb = Excel.Application.Workbooks.Open("C:\Book2.xls")

  Dim strExt As String

  Dim VBComp As Object
  For Each VBComp In wb.VBProject.VBComponents
    Select Case VBComp.Type
      Case 2  ' Class module
        strExt = ".cls"
      Case 3  ' Form
        strExt = ".frm"
      Case 1  ' Standard module
        strExt = ".bas"
      Case 100  ' Document?
        strExt = ".cls"
      Case Else
        Stop  ' What else is there?
        strExt = ".cls"
    End Select

    VBComp.Export "C:\" & VBComp.Name & strExt
  Next
  wb.Close False
  app.Quit

End Sub

Answer 2

The following line:

Set wb = Excel.Application.Workbooks.Open("C:\Book2.xls")

should be

Set wb = app.Workbooks.Open("C:\Book2.xls")