list excel wookbooks and worksheets
 

Hi,
Is there a way to utilize a bit of VBA code that will create a list of excel workbooks in a directory with all the corresponding worksheets in an adjacent column with the contents of a specific cell, say C1, next to that?

Thanks in advance.

list excel wookbooks and worksheets
 

Hi,

Am Wed, 7 Oct 2015 08:15:15 -0700 (PDT) schrieb :

Is there a way to utilize a bit of VBA code that will create a list of excel workbooks in a directory with all the corresponding worksheets in an adjacent column with the contents of a specific cell, say C1, next to that?

try:

Sub Test()
Dim objFSO As Object, objFolder As Object, objFile As Object
Dim varHeader As Variant, varOut() As Variant
Dim i As Long, n As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Modify your path
Const myPath = "E:\Excel_NG\AktuelleProjekte\"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(myPath)

varHeader = Array("Workbook", "Sheets", "C1")
With ActiveSheet
.Range("A1:C1") = varHeader
.Range("A1:C1").Font.Bold = True
For Each objFile In objFolder.Files
If InStr(objFile.Name, ".xls") Then
.Cells(Rows.Count, 2).End(xlUp)(2).Offset(, -1) =
objFile.Name
Workbooks.Open myPath & objFile.Name
n = 0
ReDim Preserve varOut(1, Worksheets.Count - 1)
For i = 1 To ActiveWorkbook.Worksheets.Count
varOut(0, n) = Worksheets(i).Name
varOut(1, n) = Worksheets(i).Range("C1").Value
n = n + 1
Next
ActiveWorkbook.Close savechanges:=False
.Cells(Rows.Count, 2).End(xlUp)(2).Resize(n, 2) _
= Application.Transpose(varOut)
End If
Next
.Columns("A:C").AutoFit
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

list excel wookbooks and worksheets
 

On Wednesday, October 7, 2015 at 11:15:33 AM UTC-4, wrote:
Hi,
Is there a way to utilize a bit of VBA code that will create a list of excel workbooks in a directory with all the corresponding worksheets in an adjacent column with the contents of a specific cell, say C1, next to that?

Thanks in advance.

I'm getting a runtime error '1004', it can't find the file

list excel wookbooks and worksheets
 

Hi,

Am Wed, 7 Oct 2015 10:13:18 -0700 (PDT) schrieb :

I'm getting a runtime error '1004', it can't find the file

have you modified the path correctly?
At the end of the path must be a backslash.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

list excel wookbooks and worksheets
 

On Wednesday, October 7, 2015 at 11:15:33 AM UTC-4, wrote:
Hi,
Is there a way to utilize a bit of VBA code that will create a list of excel workbooks in a directory with all the corresponding worksheets in an adjacent column with the contents of a specific cell, say C1, next to that?

Thanks in advance.

that was it! works perfectly now, thank you so much for your help.

list excel wookbooks and worksheets
 

Claus,
Do you know you can do the same using ADO without having to open the
Excel files? The file serves in ADO as the database, and its sheets
serve as the database tables. Thus, filename is returned via Dir() and
SQL for the list of table names.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

list excel wookbooks and worksheets
 

Hi Garry,

Am Wed, 07 Oct 2015 14:21:42 -0400 schrieb GS:

Do you know you can do the same using ADO without having to open the
Excel files? The file serves in ADO as the database, and its sheets
serve as the database tables. Thus, filename is returned via Dir() and
SQL for the list of table names.

and how do I get a cell value?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

list excel wookbooks and worksheets
 

Hi Garry,

Am Wed, 07 Oct 2015 14:21:42 -0400 schrieb GS:

Do you know you can do the same using ADO without having to open the
Excel files? The file serves in ADO as the database, and its sheets
serve as the database tables. Thus, filename is returned via Dir()
and SQL for the list of table names.

and how do I get a cell value?


Regards
Claus B.

You query the range. Here's an example tutorial by Rob Bovey, one of
the authors of *Pro Excel Development*...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

list excel wookbooks and worksheets
 

Hi Garry,

Am Wed, 07 Oct 2015 14:40:59 -0400 schrieb GS:

http://www.appspro.com/conference/Da...rogramming.zip

thank you. I will look for the code tomorrow.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

list excel wookbooks and worksheets
 

Hi Garry,

Am Wed, 07 Oct 2015 14:21:42 -0400 schrieb GS:

Do you know you can do the same using ADO without having to open the
Excel files? The file serves in ADO as the database, and its sheets
serve as the database tables. Thus, filename is returned via Dir()
and SQL for the list of table names.

and how do I get a cell value?


Regards
Claus B.

If the cell has a defined name it doesn't need to be at a specific
address. For example, if the workbooks contain quotes/estimates of
various numbers of items, the SubTotal, Tax, and Total can be anywhere
on the sheet. You can use the defined name in the ADO query as the
range ref when the address will be unknown...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion