M
Michael
I have the following sub working except for the situation where the
field is empty.
In the case that either the path or filename fields of the record set
are empty I simply want to skip over the record.
I keep getting an error in the line " Path = rst!Path.Value" if there
is no path in the dataset. The error says "invalid use or NULL" I have
tried trapping the error and also an if statement that checks the rst!
Path.Value for NULL but nothing seems to work.
Am I missing something
'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
Debug.Print "start " & Now()
Set dbs = CurrentDb
strSQL = "SELECT * FROM tmpExcelDat"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rst.EOF
Debug.Print rst!DealName.Value
Path = rst!Path.Value
Filename = rst!Filename.Value
varArray = GetExcelPFDat(Path, Filename)
rst.Edit
rst!Units.Value = varArray(0)
rst!taxcredit.Value = varArray(1)
rst.Update
rst.MoveNext
Loop
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Debug.Print "End " & Now()
End Sub
field is empty.
In the case that either the path or filename fields of the record set
are empty I simply want to skip over the record.
I keep getting an error in the line " Path = rst!Path.Value" if there
is no path in the dataset. The error says "invalid use or NULL" I have
tried trapping the error and also an if statement that checks the rst!
Path.Value for NULL but nothing seems to work.
Am I missing something
'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
Debug.Print "start " & Now()
Set dbs = CurrentDb
strSQL = "SELECT * FROM tmpExcelDat"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rst.EOF
Debug.Print rst!DealName.Value
Path = rst!Path.Value
Filename = rst!Filename.Value
varArray = GetExcelPFDat(Path, Filename)
rst.Edit
rst!Units.Value = varArray(0)
rst!taxcredit.Value = varArray(1)
rst.Update
rst.MoveNext
Loop
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Debug.Print "End " & Now()
End Sub