List all modules in workbook X?

This is probably an easy one, I just don't know the commands to use- I'm
looping through a series of workbooks, and need to identify the module names
in each.

If possible, it would be really, really helpful to know when each module was
last updated- in some cases there are modules with the same name, some of
which were imported replacements for older (broken) code, so if I can figure
out which workbooks have that module /and/ which ones are the old ones that
still need to be replaced, that would be very, very cool.

Thanks for any help or advice,
Keith

The following code will list all the modules in the
ActiveWorkbook. There is no way to determine when a module was
last modifed.


"KR" <[email protected]> wrote in message
news:[email protected]...
> This is probably an easy one, I just don't know the commands to
> use- I'm
> looping through a series of workbooks, and need to identify the
> module names
> in each.
>
> If possible, it would be really, really helpful to know when
> each module was
> last updated- in some cases there are modules with the same
> name, some of
> which were imported replacements for older (broken) code, so if
> I can figure
> out which workbooks have that module /and/ which ones are the
> old ones that
> still need to be replaced, that would be very, very cool.
>
> Thanks for any help or advice,
> Keith
>
>

The following code will list all the modules in the
ActiveWorkbook. There is no way to determine when a module was
last modified.

Dim VBComp As VBIDE.VBComponent
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
Debug.Print VBComp.Name
End If
Next VBComp

You'll need a reference to the Extensibility library. In VBA, go
to the Tools menu, choose References, and scroll down to
"Microsoft Visual Basic For Application Extensibility Library"
and put a check next to it.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"KR" <[email protected]> wrote in message
news:[email protected]...
> This is probably an easy one, I just don't know the commands to
> use- I'm
> looping through a series of workbooks, and need to identify the
> module names
> in each.
>
> If possible, it would be really, really helpful to know when
> each module was
> last updated- in some cases there are modules with the same
> name, some of
> which were imported replacements for older (broken) code, so if
> I can figure
> out which workbooks have that module /and/ which ones are the
> old ones that
> still need to be replaced, that would be very, very cool.
>
> Thanks for any help or advice,
> Keith
>
>

Chip-

Thanks for the snippet, I'll give it a try.

Just curious, is there a way to pull in a specific line of code? Since I
know which module was modified, if I could pull in, for example, the 15th
line in that module, then I could compare it against the 15th line in the
modified (new) module to see if it matches up. It wouldn't require a date
but would still allow me to detect outdated modules. Any suggestions on how
I might go about this?

Many (many many) thanks,
Keith


"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> The following code will list all the modules in the
> ActiveWorkbook. There is no way to determine when a module was
> last modified.
>
> Dim VBComp As VBIDE.VBComponent
> For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> If VBComp.Type = vbext_ct_StdModule Then
> Debug.Print VBComp.Name
> End If
> Next VBComp
>
> You'll need a reference to the Extensibility library. In VBA, go
> to the Tools menu, choose References, and scroll down to
> "Microsoft Visual Basic For Application Extensibility Library"
> and put a check next to it.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "KR" <[email protected]> wrote in message
> news:[email protected]...
> > This is probably an easy one, I just don't know the commands to
> > use- I'm
> > looping through a series of workbooks, and need to identify the
> > module names
> > in each.
> >
> > If possible, it would be really, really helpful to know when
> > each module was
> > last updated- in some cases there are modules with the same
> > name, some of
> > which were imported replacements for older (broken) code, so if
> > I can figure
> > out which workbooks have that module /and/ which ones are the
> > old ones that
> > still need to be replaced, that would be very, very cool.
> >
> > Thanks for any help or advice,
> > Keith
> >
> >
>
>

Keith

Dim CodeLine As String
Dim VBComp As VBIDE.VBComponent
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
CodeLine = VBComp.CodeModule.Lines(15, 1)
Debug.Print CodeLine
End If
Next VBComp

See www.cpearson.com/excel/vbe.htm for details about working with
the Extensibility libary.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"KR" <[email protected]> wrote in message
news:%[email protected]...
> Chip-
>
> Thanks for the snippet, I'll give it a try.
>
> Just curious, is there a way to pull in a specific line of
> code? Since I
> know which module was modified, if I could pull in, for
> example, the 15th
> line in that module, then I could compare it against the 15th
> line in the
> modified (new) module to see if it matches up. It wouldn't
> require a date
> but would still allow me to detect outdated modules. Any
> suggestions on how
> I might go about this?
>
> Many (many many) thanks,
> Keith
>
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>> The following code will list all the modules in the
>> ActiveWorkbook. There is no way to determine when a module was
>> last modified.
>>
>> Dim VBComp As VBIDE.VBComponent
>> For Each VBComp In ActiveWorkbook.VBProject.VBComponents
>> If VBComp.Type = vbext_ct_StdModule Then
>> Debug.Print VBComp.Name
>> End If
>> Next VBComp
>>
>> You'll need a reference to the Extensibility library. In VBA,
>> go
>> to the Tools menu, choose References, and scroll down to
>> "Microsoft Visual Basic For Application Extensibility Library"
>> and put a check next to it.
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "KR" <[email protected]> wrote in message
>> news:[email protected]...
>> > This is probably an easy one, I just don't know the commands
>> > to
>> > use- I'm
>> > looping through a series of workbooks, and need to identify
>> > the
>> > module names
>> > in each.
>> >
>> > If possible, it would be really, really helpful to know when
>> > each module was
>> > last updated- in some cases there are modules with the same
>> > name, some of
>> > which were imported replacements for older (broken) code, so
>> > if
>> > I can figure
>> > out which workbooks have that module /and/ which ones are
>> > the
>> > old ones that
>> > still need to be replaced, that would be very, very cool.
>> >
>> > Thanks for any help or advice,
>> > Keith
>> >
>> >
>>
>>
>
>

Chip (or others)-

I thought I had this code working, but now I'm getting a weird error- the
relevant code is below. The first workbook opens, then I get a pop-up error:
Run time error '-2147024890 (80070006)': System Error
&H80070006(-2147024890). The handle is invalid.

When I look in the code (debug) and mouseover to get the VBComp.Type the
mouseover box shows: VBComp.Type = <Method 'type' of object '_vbComponent'
failed> and it actually errored out on the "if VBComp.Name..." statement.
I'm way out of my league here... I have added the iserror(VBComp.type) to
try to just bypass this and keep the workbook going, but it still bugs
out...

Any help would be greatly appreciated!
Thanks,
Keith

<snipped code that gets all workbook names in directory and adds them to
MyFiles()>
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

'lets me verify how many workbooks have been processed
Application.StatusBar = "Processing " & Fnum & " of " &
UBound(MyFiles) & " -> " & MyFiles(Fnum)

'open as readonly, called "mybook"
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0, False)
Application.EnableEvents = True

For Each VBComp In mybook.VBProject.VBComponents
If IsError(VBComp.Type) Then
'do nothing
Else
If VBComp.Type = vbext_ct_StdModule Then
'MsgBox VBComp.Name

If VBComp.Name = "ValidateAndPasteData1" Then
mybook.VBProject.VBComponents.Remove VBComp

If VBComp.Name = "ValidateAndPasteData" Then
(etc.)

"Chip Pearson" <[email protected]> wrote in message
news:%[email protected]...
> Keith
>
> Dim CodeLine As String
> Dim VBComp As VBIDE.VBComponent
> For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> If VBComp.Type = vbext_ct_StdModule Then
> CodeLine = VBComp.CodeModule.Lines(15, 1)
> Debug.Print CodeLine
> End If
> Next VBComp
>
> See www.cpearson.com/excel/vbe.htm for details about working with
> the Extensibility libary.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "KR" <[email protected]> wrote in message
> news:%[email protected]...
> > Chip-
> >
> > Thanks for the snippet, I'll give it a try.
> >
> > Just curious, is there a way to pull in a specific line of
> > code? Since I
> > know which module was modified, if I could pull in, for
> > example, the 15th
> > line in that module, then I could compare it against the 15th
> > line in the
> > modified (new) module to see if it matches up. It wouldn't
> > require a date
> > but would still allow me to detect outdated modules. Any
> > suggestions on how
> > I might go about this?
> >
> > Many (many many) thanks,
> > Keith
> >
> >
> > "Chip Pearson" <[email protected]> wrote in message
> > news:[email protected]...
> >> The following code will list all the modules in the
> >> ActiveWorkbook. There is no way to determine when a module was
> >> last modified.
> >>
> >> Dim VBComp As VBIDE.VBComponent
> >> For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> >> If VBComp.Type = vbext_ct_StdModule Then
> >> Debug.Print VBComp.Name
> >> End If
> >> Next VBComp
> >>
> >> You'll need a reference to the Extensibility library. In VBA,
> >> go
> >> to the Tools menu, choose References, and scroll down to
> >> "Microsoft Visual Basic For Application Extensibility Library"
> >> and put a check next to it.
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> "KR" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > This is probably an easy one, I just don't know the commands
> >> > to
> >> > use- I'm
> >> > looping through a series of workbooks, and need to identify
> >> > the
> >> > module names
> >> > in each.
> >> >
> >> > If possible, it would be really, really helpful to know when
> >> > each module was
> >> > last updated- in some cases there are modules with the same
> >> > name, some of
> >> > which were imported replacements for older (broken) code, so
> >> > if
> >> > I can figure
> >> > out which workbooks have that module /and/ which ones are
> >> > the
> >> > old ones that
> >> > still need to be replaced, that would be very, very cool.
> >> >
> >> > Thanks for any help or advice,
> >> > Keith
> >> >
> >> >
> >>
> >>
> >
> >
>
>