Extract VBA code from another workbook

[rbngrt582]

Hey Guys,

I am looking to extract a certain line of code from another workbook and display the code line as text in another workbook. I know you can extract certain cell data from another workbook, but can you extract actual vba code from another workbook and have it display as text?

I would like my program to search for and pull a certain line of code from another workbook (any workbook that is) and make it visible to the user by displaying the code in a cell.

Thanks!

[LaVolpe]

Welcome to the forums

Google this and see if it answers your question: "List and run Excel worksheet macros using VB6"
Though title specifically addresses macros, the sample code appears to be more than just that

[rbngrt582]

Welcome to the forums

Google this and see if it answers your question: "List and run Excel worksheet macros using VB6"
Though title specifically addresses macros, the sample code appears to be more than just that

Thanks LaVolpe!

I used code I found online and the debugger came back with "user defined type not defined", and the first row of code being highlighted. Here is the code I am using:

Private Sub LoadMacrosList()
    ' Declare variables to access the Excel workbook.
    Dim objXLApp As Excel.Application
    Dim objXLWorkbooks As Excel.Workbooks
    Dim objXLABC As Excel.Workbook

    ' Declare variables to access the macros in the workbook.
    Dim objProject As VBIDE.VBProject
    Dim objComponent As VBIDE.VBComponent
    Dim objCode As VBIDE.CodeModule

    ' Declare other miscellaneous variables.
    Dim iLine As Integer
    Dim sProcName As String
    Dim pk As vbext_ProcKind

    ' Open Excel, and open the workbook.
    Set objXLApp = New Excel.Application
    Set objXLWorkbooks = objXLApp.Workbooks    
    Set objXLABC = objXLWorkbooks.Open("C:\ABC.XLS")

    ' Empty the list box.
    List1.Clear

    ' Get the project details in the workbook.
    Set objProject = objXLABC.VBProject

    ' Iterate through each component in the project.
    For Each objComponent In objProject.VBComponents

        ' Find the code module for the project.
        Set objCode = objComponent.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < objCode.CountOfLines
            sProcName = objCode.ProcOfLine(iLine, pk)
            If sProcName <> "" Then
                ' Found a procedure. Display its details, and then skip 
                ' to the end of the procedure.
                List1.AddItem objComponent.Name & vbTab & sProcName
                iLine = iLine + objCode.ProcCountLines(sProcName, pk)
            Else
                ' This line has no procedure, so go to the next line.
                iLine = iLine + 1
            End If
        Loop
        Set objCode = Nothing
        Set objComponent = Nothing
    Next

    Set objProject = Nothing

    ' Clean up and exit.
    objXLABC.Close
    objXLApp.Quit
End Sub

[LaVolpe]

You'll need to include MS Office references - menu: Project|References

If you don't know what I'm talking about, search the forum for simple examples of using VB6 to read Excel worksheets. Those examples will give you a better idea on interacting with Office from VB. There is also another part of this site dedicated to MS Office: http://www.vbforums.com/forumdisplay...ce-Development

[rbngrt582]

You'll need to include MS Office references - menu: Project|References

If you don't know what I'm talking about, search the forum for simple examples of using VB6 to read Excel worksheets. Those examples will give you a better idea on interacting with Office from VB. There is also another part of this site dedicated to MS Office: http://www.vbforums.com/forumdisplay...ce-Development

Thanks again Lavolpe! I think you're leading me in the right direction. However, I am afraid after googling VB6 to read Excel I still don't understand how to debug my code. Would you mind giving me a little guidance on how to make my VB6 code applicable to be used with VBA?

Thanks in advance!

[westconn1]

if you are working in excel vba, you do not need to create an application object, as you can just use the built in application object and you would not require a reference to microsoft excel, most of the rest of the code would be the same

Private Sub LoadMacrosList()
    ' Declare variables to access the Excel workbook.
    Dim objXLABC As Excel.Workbook

    ' Declare variables to access the macros in the workbook.
    Dim objProject As VBIDE.VBProject
    Dim objComponent As VBIDE.VBComponent
    Dim objCode As VBIDE.CodeModule

    ' Declare other miscellaneous variables.
    Dim iLine As Integer
    Dim sProcName As String
    Dim pk As vbext_ProcKind

    Set objXLABC = Workbooks.Open("C:\ABC.XLS")

    ' Empty the list box.
    List1.Clear

    ' Get the project details in the workbook.
    Set objProject = objXLABC.VBProject

    ' Iterate through each component in the project.
    For Each objComponent In objProject.VBComponents

        ' Find the code module for the project.
        Set objCode = objComponent.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < objCode.CountOfLines
            sProcName = objCode.ProcOfLine(iLine, pk)
            If sProcName <> "" Then
                ' Found a procedure. Display its details, and then skip 
                ' to the end of the procedure.
                List1.AddItem objComponent.Name & vbTab & sProcName
                iLine = iLine + objCode.ProcCountLines(sProcName, pk)
            Else
                ' This line has no procedure, so go to the next line.
                iLine = iLine + 1
            End If
        Loop
        Set objCode = Nothing
        Set objComponent = Nothing
    Next

    Set objProject = Nothing

    ' Clean up and exit.
    objXLABC.Close false  ' do not save

End Sub

the above code should make a list of all the procedures in each module of the abc workbook, does it do so?
you can then modify the code to find whatever specific text and assign it to a cell

[rbngrt582]

Thanks again! I ran the code again and still get the same debug error message: 'Compile error: User-defined type not defined'. I'm looking into this and will let you know if I can figure it out. Any ideas?

[gibra]

Thanks again! I ran the code again and still get the same debug error message: 'Compile error: User-defined type not defined'. I'm looking into this and will let you know if I can figure it out. Any ideas?

Again: see post #4

[rbngrt582]

I know I need to add a References so I added Microsoft SML, v6.0 ---but I'm not having any luck. Does anyone know what Reference library I should be adding to make my code work?

[gibra]

What's SML ???
You use Excel, then you should add Microsoft Excel xx Object Library (where xx is your Office/Excel version, i.e. 14.0 , 15.0, ...)

[vbfbryce]

Where are you coding this? VB6? Excel VBA?

[rbngrt582]

Where are you coding this? VB6? Excel VBA?

I am coding this in Excel VBA. Also, I have MS Excel 16.0 Object Library checked as a reference with no success.

SML was a typo for XML.

[vbfbryce]

As Pete states, you do not need to add a reference to Excel. You do, however, need to add a reference to:

Microsoft Visual Basic for Applications Extensibility x.x

I believe.

[westconn1]

it always helps if you tell us which line is giving the error
bryces post above will solve that error

also you need a listbox list1 or change the name to someother list
if the workbook you want to retrieve the code from is (or could be) already open, you need to change the line that opens the workbook or test if the workbook is already open

on testing the code i got some other type mismatch errors that also related to the vide variables, so i re-dimensioned them to variants (the reference mentioned previously is now no longer required), removed the list1 object and put the list into the active excel worksheet, here is the revised code, worked without error, long list in column A from an already open workbook

Private Sub LoadMacrosList()
    ' Declare variables to access the Excel workbook.
    Dim objXLABC As Excel.Workbook

    ' Declare variables to access the macros in the workbook.
    ' Now as variants, the default type, but you can specify or use object type
    Dim objProject
    Dim objComponent
    Dim objCode
    ' Declare other miscellaneous variables.
    Dim iLine As Integer
    Dim sProcName As String
    Dim pk As vbext_ProcKind

    Set objXLABC = Workbooks("ado test.xls")

    ' Empty the list box.
'    List1.Clear
    rw = 1
    ' Get the project details in the workbook.
    Set objProject = objXLABC.VBProject
    ' Iterate through each component in the project.
    For Each objComponent In objXLABC.VBProject.VBComponents

        ' Find the code module for the project.
        Set objCode = objComponent.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < objCode.CountOfLines
            sProcName = objCode.ProcOfLine(iLine, pk)
            If sProcName <> "" Then
                ' Found a procedure. Display its details, and then skip
                ' to the end of the procedure.
                Cells(rw, 1) = objComponent.Name & vbTab & sProcName
                rw = rw + 1
                iLine = iLine + objCode.ProcCountLines(sProcName, pk)
            Else
                ' This line has no procedure, so go to the next line.
                iLine = iLine + 1
            End If
        Loop
        Set objCode = Nothing
        Set objComponent = Nothing
    Next

    Set objProject = Nothing

    ' Clean up and exit.
    objXLABC.Close False  ' do not save

End Sub

forgot to remove the line to close the already open workbook, so it closed without saving changes

[rbngrt582]

it always helps if you tell us which line is giving the error
bryces post above will solve that error

also you need a listbox list1 or change the name to someother list
if the workbook you want to retrieve the code from is (or could be) already open, you need to change the line that opens the workbook or test if the workbook is already open

on testing the code i got some other type mismatch errors that also related to the vide variables, so i re-dimensioned them to variants (the reference mentioned previously is now no longer required), removed the list1 object and put the list into the active excel worksheet, here is the revised code, worked without error, long list in column A from an already open workbook

Private Sub LoadMacrosList()
    ' Declare variables to access the Excel workbook.
    Dim objXLABC As Excel.Workbook

    ' Declare variables to access the macros in the workbook.
    ' Now as variants, the default type, but you can specify or use object type
    Dim objProject
    Dim objComponent
    Dim objCode
    ' Declare other miscellaneous variables.
    Dim iLine As Integer
    Dim sProcName As String
    Dim pk As vbext_ProcKind

    Set objXLABC = Workbooks("ado test.xls")

    ' Empty the list box.
'    List1.Clear
    rw = 1
    ' Get the project details in the workbook.
    Set objProject = objXLABC.VBProject
    ' Iterate through each component in the project.
    For Each objComponent In objXLABC.VBProject.VBComponents

        ' Find the code module for the project.
        Set objCode = objComponent.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < objCode.CountOfLines
            sProcName = objCode.ProcOfLine(iLine, pk)
            If sProcName <> "" Then
                ' Found a procedure. Display its details, and then skip
                ' to the end of the procedure.
                Cells(rw, 1) = objComponent.Name & vbTab & sProcName
                rw = rw + 1
                iLine = iLine + objCode.ProcCountLines(sProcName, pk)
            Else
                ' This line has no procedure, so go to the next line.
                iLine = iLine + 1
            End If
        Loop
        Set objCode = Nothing
        Set objComponent = Nothing
    Next

    Set objProject = Nothing

    ' Clean up and exit.
    objXLABC.Close False  ' do not save

End Sub

forgot to remove the line to close the already open workbook, so it closed without saving changes

Thanks west! I put the code you suggested in but now I'm getting a 424 'object required' error. The error occurs at:

Set objXLABC = Workbooks ("ado test.xls")

I tried changing the file name and also creating a file named ado test. Cant quite figure this out. Thanks again!

[westconn1]

you do have your code in a workbook project?

the error indicates that the workbook object is not valid, so if this code is in excel vba i do not understand why that should be

can you post a sample workbook (zip first), so we can test

[gibra]

I am coding this in Excel VBA. Also, I have MS Excel 16.0 Object Library checked as a reference with no success.

SML was a typo for XML.

So excuse me for my mistake.
I was thinking that you used VB6.
I got confused.

[rbngrt582]

I was able to figure out how to make this code work. However, I am looking to do something a little different. I would like to pull code from another excel file...not necessarily list macros from my current workbook. Would there be a way to do this with code?

[westconn1]

Would there be a way to do this with code?

yes of course

the example i posted listed the code from all modules in a different workbook that in my case was already open, the only macro in the current workbook was the code as posted here, i used a workbook with multiple modules and many procedures for testing

you could just as easily list the procedures from all workbooks in a folder, open each one read all the code and close in turn

according to your earlier posts you did not want to list all procedures, but a specific line, but you have not mentioned the criteria to find the line you want, though it is probably only some minor change to achieve what you want, we can only help to the level of detail given