I have approximately 500 workbooks, their layouts and number of sheet
vary. Throughout the years people have changed column names, fo
example loan number is now customer and tracking number maybe fedex o
fedex number or box number. Some sheets may have 5 columns and som
may have 10. The order of columns is not always consistent.

I would like to place all the worksheets from these workbooks int
either one giant workbook or MS Access table. I am able to retreiv
the first sheet of each workbook. Can you tell me how to retreive al
the the worksheets?

thanks
jwallac

--
Message posted from http://www.ExcelForum.com

Hi
as a starting point:
http://www.rondebruin.nl/copy3.htm

--
Regards
Frank Kabel
Frankfurt, Germany


I have approximately 500 workbooks, their layouts and number of
sheets
vary. Throughout the years people have changed column names, for
example loan number is now customer and tracking number maybe fedex
or fedex number or box number. Some sheets may have 5 columns and
some may have 10. The order of columns is not always consistent.

I would like to place all the worksheets from these workbooks into
either one giant workbook or MS Access table. I am able to retreive
the first sheet of each workbook. Can you tell me how to retreive
all
the the worksheets?

thanks
jwallace


---
Message posted from http://www.ExcelForum.com/

jwallace wrote ...

I have approximately 500 workbooks, their layouts and number of sheets
vary. Throughout the years people have changed column names.
Some sheets may have 5 columns and some
may have 10. The order of columns is not always consistent.

How do you identify that two sheets/columns in two different
workbooks/sheets are the same entity if you do not have EITHER
consistent names OR consistent ordinal positions?

--

My users had hopes that I could transform the raw data into some usabl
form and produce the combined output table, but alas that was not th
case. We have since edited all the raw data into a consistent layout
In vba, I open the Access Database, create my table, and open m
table. Then I loop through my workbook folders. Within that loop,
loop through my worksheets for each workbook. After I finish th
worksheets in that workbook, I read another workbook. The proces
seems logical, but it blows up with runtime errors. Please review th
code of the inner worksheet process.


Dim gSheetName As String
Dim gFileName As String
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String

PrepareDatabase
MyPath = "C:\MAILSYS\C1"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
gFileName = TheFile
AllSheets
wb.Close
TheFile = Dir
Loop

End Sub

Sub AllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
gSheetName = ws.Name
CreateDatabaseRecord
Next
End Sub

Sub PrepareDatabase()
'' Microsoft ActiveX Data Objects Library
'' Microsoft ADO Ext. 2.5 for DDL and Security

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset

With conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open "c:\mailsys\chase\chase.mdb"
.Execute "DROP TABLE AllChaseSheets"
End With

cat.ActiveConnection = conn

With tbl
.Name = "AllChaseSheets"
With .Columns
.Append "LoanNo"
.Append "DocType"
.Append "BorrowerName"
.Append "Crescent Loan #"
.Append "Tracking #"
.Append "Box #"
.Append "FromFile"
.Append "Sheet"
End With
End With
' Add the table to the database.
cat.Tables.Append tbl
With rst
.ActiveConnection = conn
.Open "AllChaseSheets", LockType:=adLockOptimistic
End With

End Sub


Sub CreateDatabaseRecord()

Dim rst As New ADODB.Recordset
Dim looprange As Range
Dim currcell As Range
Dim conn As New ADODB.Connection


Set looprange = Range("A2", Range("A2").End(xlDown))

For Each currcell In looprange
With rst
' Add a new record.
.AddNew
.Fields("LoanNo").Value = currcell.Value
.Fields("DocType").Value = currcell.Offset(0, 1).Value
.Fields("BorrowerName").Value = currcell.Offset(0
2).Value
.Fields("Crescent Loan #").Value = currcell.Offset(0
3).Value
.Fields("Box #").Value = currcell.Offset(0, 1).Value
.Fields("Tracking #").Value = currcell.Offset(0, 2).Value
.Fields("FromFile").Value = gFileName
.Fields("Sheet").Value = gSheetName

.Update
End With
Next currcell
rst.Close
Set tbl = Nothing
Set cat = Nothing
conn.Close

End Sub


I thought I understood the different processes. Please review an
help.
thanks
jackie w

--
Message posted from http://www.ExcelForum.com

Here's some comments.

PrepareDatabase creates a Connection to your database but because the
variable is local to this sub procedure you are allowing it to go out
of scope i.e. the connection is immediately closed when the sub
procedure ends. [Aside: change the declarations of all your object
variables from the single line declare-and-instantiate:

Dim conn As New ADODB.Connection

to the two line:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

..]

To keep the connection open, you have some choices:

- change from Sub to Function of return type ADODB.Connection and set
the return to be your conn variable (recommended);
- pass an empty Connection variable as a ByRef argument and use this
object to create the connection;
- change the scope of your conn variable to make it visible to all
procedures (not recommended).

CreateDatabaseRecord doesn't use a Connection object - you need one to
open your recordset, something else you've is omitted (perhaps you
have snipped some code?) Here a suggestion:

Sub CreateDatabaseRecord( _
ByVal ActiveConn As ADODB.Connection, _
ByVal TableName As String _
)

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = ActiveConn
.CommandType = adCmdTable
.CommandText = TableName
End With

Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockOptimistic
Set .Source = cmd
.Open

' Do things with recordset here

End With

Nothing else jumps out at me. If you are having specific errors, post
back with some details.

--