3

I understand it is possible to do so using Excel macro, see: 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 macros? Please advise!

Community
  • 1
  • 1

3 Answers3

2

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
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

The following line:

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

should be

Set wb = app.Workbooks.Open("C:\Book2.xls")
TAbdiukov
  • 1,185
  • 3
  • 12
  • 25
1

VbaDiff can read Excel macros directly from the Excel file, exactly in the way you describe. If you need programmatic access, there is an Enterprise version with an API. You can do some quite fun stuff with this, like in these examples.

Full disclosure - I built this product. I kept coming across similar problems to what Dean was having and decided that the time had come to solve it once and for all!

Chris Spicer
  • 2,144
  • 1
  • 13
  • 22