listing all sheets in all workbooks in a directory

Dave Peterson and LenB responded to this post yesterday.

Im sorry I forgot to state that I needed this without actually opening
any workbooks.

Below is what I need:
I need to list all the worksheets in all the workbooks in a specfic
directory.


I need it to look in C:\Test and list the Workbook name and the sheet
name next to it. For example:
Workbook1 - Sheet1
Workbook1 - Sheet2
Workbook1 - Sheet3
Workbook1 - Sheet4
Workbook2 - Sheet1
Workbook2 - Sheet2
Workbook2 - Sheet3


What is the code for this?

Although I have been lambasted for the ethics that I have used in formatting
responses within this forum (i.e., providing words to solve the problem, and
not specifically writing out the code for the user), a matter of point would
be to try to maintain all responses regarding a single "problem" in one
thread/response group. You may have an answer there already if you haven't
looked, and you may have reposted this particular question there as well. I
have not done the investigation to determine that.

I can not answer the question, as I would think that it would require
"opening" each workbook, even if the user doesn't see this action, to be able
to determine the worksheets in each workbook. As for the code to open all
workbooks in a subdirectory, I think that I have seen help somewhere in this
forum that does such, and then you can pull the list of worksheets within
that workbook and build your data to present in the method you desire.

V/R,
GB

"Todd Huttenstine" wrote:

> Dave Peterson and LenB responded to this post yesterday.
>
> Im sorry I forgot to state that I needed this without actually opening
> any workbooks.
>
> Below is what I need:
> I need to list all the worksheets in all the workbooks in a specfic
> directory.
>
>
> I need it to look in C:\Test and list the Workbook name and the sheet
> name next to it. For example:
> Workbook1 - Sheet1
> Workbook1 - Sheet2
> Workbook1 - Sheet3
> Workbook1 - Sheet4
> Workbook2 - Sheet1
> Workbook2 - Sheet2
> Workbook2 - Sheet3
>
>
> What is the code for this?
>
>

Check this site out:

http://www.appspro.com/Tips/Tips_And_Tricks.htm

Look for "Get a List of Sheetnames From a Closed Workbook"

Good luck,
GS

Learn something every day. Thanks for the info also, I might have a need for
that in the future.

"GS" wrote:

> Check this site out:
>
> http://www.appspro.com/Tips/Tips_And_Tricks.htm
>
> Look for "Get a List of Sheetnames From a Closed Workbook"
>
> Good luck,
> GS