M
Michael
This must be something stupid because it was working when I was only
using 2 fields.
the sub below does not seem to want to update the record set. The
array has the data I want for the GetExcelPFDat function but for some
reason the recordset is not being updated.
Any ideas would be appreciated.
'This Sub updates data based on path and file name found in fields
Sub DbReadExcelAndUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim Path As String
Dim Filename As String
Dim varArray As Variant
Dim v As Variant
Debug.Print "start " & Now()
Set dbs = CurrentDb
strSQL = "SELECT * FROM tmpExcelDat2"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
On Error GoTo ErrorHandler
Do Until rst.EOF
Debug.Print rst!DealName.Value
' The "" at the end gets around a null value problem.
Path = rst!Path.Value & ""
Filename = rst!Filename.Value & ""
'skip over records that are missing path or filename data
If Len(Path) = 0 Or Len(Filename) = 0 Then
rst.MoveNext
Else
'Call fuction that gets data from named cells in excel file
varArray = GetExcelPFDat(Path, Filename)
' fill empty array values with 0
For v = 0 To UBound(varArray)
If varArray(v) = "" Then
varArray(v) = 0
End If
Next v
rst.Edit
'The array varArray is full becuase thise debug statemetns print out
the values I want inserted.
Debug.Print "units " & varArray(0)
Debug.Print "taxcredit " & varArray(1)
Debug.Print "LIHTCFedEquity " & varArray(2)
Debug.Print "HTCFedEquity " & varArray(3)
Debug.Print "LIHTCStateEquity " & varArray(4)
Debug.Print "HTCStateEquity " & varArray(5)
Debug.Print "OtherEquity " & varArray(6)
Debug.Print "HTCAmount " & varArray(7)
Debug.Print "DebtPerm " & varArray(8)
Debug.Print "DebtConstLoan " & varArray(9)
Debug.Print "DebtConstBridge " & varArray(10)
Debug.Print "DebtSoftTotal " & varArray(11)
Debug.Print "TDC " & varArray(12)
' puts the excel data into the db
rst!Units.Value = varArray(0)
rst!taxcredit.Value = varArray(1)
rst!LIHTCFedEquity.Value = varArray(2)
rst!HTCFedEquity.Value = varArray(3)
rst!LIHTCStateEquity.Value = varArray(4)
rst!HTCStateEquity.Value = varArray(5)
rst!OtherEquity.Value = varArray(6)
rst!HTCAmount.Value = varArray(7)
rst!DebtPerm.Value = varArray(8)
rst!DebtConstLoan.Value = varArray(9)
rst!DebtConstBridge.Value = varArray(10)
rst!DebtSoftTotal.Value = varArray(11)
rst!TDC.Value = varArray(12)
rst.Update
rst.MoveNext
End If
Loop
ErrorHandler:
' dont think this comes up anymore.
If Err.Number = 94 Then ' invalid use of null. This happends if a
record is missing Path or Filename
Resume Next
End If
'clean up and close
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Debug.Print "End " & Now()
End Sub
using 2 fields.
the sub below does not seem to want to update the record set. The
array has the data I want for the GetExcelPFDat function but for some
reason the recordset is not being updated.
Any ideas would be appreciated.
'This Sub updates data based on path and file name found in fields
Sub DbReadExcelAndUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim Path As String
Dim Filename As String
Dim varArray As Variant
Dim v As Variant
Debug.Print "start " & Now()
Set dbs = CurrentDb
strSQL = "SELECT * FROM tmpExcelDat2"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
On Error GoTo ErrorHandler
Do Until rst.EOF
Debug.Print rst!DealName.Value
' The "" at the end gets around a null value problem.
Path = rst!Path.Value & ""
Filename = rst!Filename.Value & ""
'skip over records that are missing path or filename data
If Len(Path) = 0 Or Len(Filename) = 0 Then
rst.MoveNext
Else
'Call fuction that gets data from named cells in excel file
varArray = GetExcelPFDat(Path, Filename)
' fill empty array values with 0
For v = 0 To UBound(varArray)
If varArray(v) = "" Then
varArray(v) = 0
End If
Next v
rst.Edit
'The array varArray is full becuase thise debug statemetns print out
the values I want inserted.
Debug.Print "units " & varArray(0)
Debug.Print "taxcredit " & varArray(1)
Debug.Print "LIHTCFedEquity " & varArray(2)
Debug.Print "HTCFedEquity " & varArray(3)
Debug.Print "LIHTCStateEquity " & varArray(4)
Debug.Print "HTCStateEquity " & varArray(5)
Debug.Print "OtherEquity " & varArray(6)
Debug.Print "HTCAmount " & varArray(7)
Debug.Print "DebtPerm " & varArray(8)
Debug.Print "DebtConstLoan " & varArray(9)
Debug.Print "DebtConstBridge " & varArray(10)
Debug.Print "DebtSoftTotal " & varArray(11)
Debug.Print "TDC " & varArray(12)
' puts the excel data into the db
rst!Units.Value = varArray(0)
rst!taxcredit.Value = varArray(1)
rst!LIHTCFedEquity.Value = varArray(2)
rst!HTCFedEquity.Value = varArray(3)
rst!LIHTCStateEquity.Value = varArray(4)
rst!HTCStateEquity.Value = varArray(5)
rst!OtherEquity.Value = varArray(6)
rst!HTCAmount.Value = varArray(7)
rst!DebtPerm.Value = varArray(8)
rst!DebtConstLoan.Value = varArray(9)
rst!DebtConstBridge.Value = varArray(10)
rst!DebtSoftTotal.Value = varArray(11)
rst!TDC.Value = varArray(12)
rst.Update
rst.MoveNext
End If
Loop
ErrorHandler:
' dont think this comes up anymore.
If Err.Number = 94 Then ' invalid use of null. This happends if a
record is missing Path or Filename
Resume Next
End If
'clean up and close
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Debug.Print "End " & Now()
End Sub