Extract macro text out of Excel (Newbie)
 

Hello

Sorry if this is a FAQ...

I am currently in a bind. The organisation I am working for has thousands
of excel sheets scattered around the business. Unfortunately around 10% of
them include VBA that uses ADO to connect to a database that is being
decommissioned.

Is there a property on the Excel COM object or VSTO that can be used so that
I can determine if the file includes macros? Even better would be a method
that can extract the macro text out of the excel file so I can use a regular
expression to search for ADODB.

At the moment I am planning on writing a tool (running under an account with
read only file access) that will recursively loop over all directories within
the organisation and find all the excel sheets. I want to then do a
preliminary check to see if the file
1) Contains Macros
2) Contains VBA
3) References other excel sheets or xla files that do 1 or 2.



Thanks

--
David Ross

Extract macro text out of Excel (Newbie)
 

Hi David,

See Chip Pearson's VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled 'Exporting All Modules In A Project'
which includes a procedure which exports all of the modules in a workbook to
text files.



---
Regards,
Norman



"David Ross" wrote in message
...
Hello

Sorry if this is a FAQ...

I am currently in a bind. The organisation I am working for has thousands
of excel sheets scattered around the business. Unfortunately around 10%
of
them include VBA that uses ADO to connect to a database that is being
decommissioned.

Is there a property on the Excel COM object or VSTO that can be used so
that
I can determine if the file includes macros? Even better would be a
method
that can extract the macro text out of the excel file so I can use a
regular
expression to search for ADODB.

At the moment I am planning on writing a tool (running under an account
with
read only file access) that will recursively loop over all directories
within
the organisation and find all the excel sheets. I want to then do a
preliminary check to see if the file
1) Contains Macros
2) Contains VBA
3) References other excel sheets or xla files that do 1 or 2.



Thanks

--
David Ross

Extract macro text out of Excel (Newbie)
 

Legend.

--
David Ross



"Norman Jones" wrote:

Hi David,

See Chip Pearson's VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled 'Exporting All Modules In A Project'
which includes a procedure which exports all of the modules in a workbook to
text files.



---
Regards,
Norman



"David Ross" wrote in message
...
Hello

Sorry if this is a FAQ...

I am currently in a bind. The organisation I am working for has thousands
of excel sheets scattered around the business. Unfortunately around 10%
of
them include VBA that uses ADO to connect to a database that is being
decommissioned.

Is there a property on the Excel COM object or VSTO that can be used so
that
I can determine if the file includes macros? Even better would be a
method
that can extract the macro text out of the excel file so I can use a
regular
expression to search for ADODB.

At the moment I am planning on writing a tool (running under an account
with
read only file access) that will recursively loop over all directories
within
the organisation and find all the excel sheets. I want to then do a
preliminary check to see if the file
1) Contains Macros
2) Contains VBA
3) References other excel sheets or xla files that do 1 or 2.



Thanks

--
David Ross