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