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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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