IsNull异常

''删除不适用的数据 DoCmd.OpenQuery" Delete_blanks" ''从表AOD中删除上周数据， CSCI_AOD，CSCI_AOD_Phase， CSCI_AOD_HLD（DD，代码，CBT，UCT） DoCmd.RunSQL" delete * from CSCI_AOD_HLD" DoCmd。 RunSQL" delete * from CSCI_AOD_DD" DoCmd.RunSQL" delete * from CSCI_AOD_Code" DoCmd.RunSQL" delete * from CSCI_AOD_UCT" DoCmd.RunSQL" delete * from CSCI_AOD_CBT" DoCmd.RunSQL" delete * from CSCI_AOD_Phase" DoCmd.RunSQL" delete * from CSCI_AOD" DoCmd.RunSQL从AOD中删除* ''运行追加查询以将本周的数据添加到$ b $中b DoCmd.OpenQuery" Append_AOD1" DoCmd.Open查询Append_CSCI_AOD ''DoCmd.OpenQuery" Append_AOD_Phase" ''DoCmd.OpenQuery" Append_HLD" ''DoCmd .OpenQueryAppend_DD ''DoCmd.OpenQuery" Append_Code" ''DoCmd.OpenQuery" Append_UCT" '' DoCmd.OpenQueryAppend_CBT ''错误处理程序用于在字段为空时捕获错误 退出函数 Exit_Handler： On Error Resume Next Err_Handler： On Error Resume Next 结束功能 If only I could, the problem is I have about 10 workbooks with a numberof worksheets that can be added to or taken away from dynamically,therefore the only way to get all the data in is to loop through all ofthe worksheets in the workbook and pick up the ones with the relevantname, I''ve inserted the code here: Function Get_Historical_AOD_Hours() On Error GoTo Err_HandlerDim xlApp As Object '' Excel.ApplicationDim xlBook As Object '' Excel.WorkbookDim xlSheet As Object '' Excel.WorksheetDim strPath As StringDim Workbook_Array As VariantDim intI As VariantDim rowcount As Variant''Dim startcol As Variant''Dim endcol As VariantDim sheetRange As String''Dim range As Variant''Dim stage As StringDim week_end As VariantDim CSCI As VariantDim AOD As VariantDim Phase As VariantDim Current_DB As DatabaseDim rst As RecordsetDim rst_update As RecordsetDim rst_csci As Recordset''Dim rst_status As RecordsetDim XLTARGET As VariantDim Today As VariantDim csci_flag As Boolean ''MsgBox "i''d like to start"Set Current_DB = DBEngine.Workspaces(0).Databases(0)Set rst = Current_DB.OpenRecordset("Temp_Historical_Hours")''MsgBox "oh my God"Set rst_update = Current_DB.OpenRecordset("AOD_Historical_Hours")''MsgBox "and even"Set rst_csci = Current_DB.OpenRecordset("CSCI")''MsgBox "and then" ''startcol = "B"''endcol = "R"XLTARGET = "Temp_Historical_Hours" DoCmd.SetWarnings FalseDoCmd.RunSQL "delete * from Temp_Historical_Hours" ''Clear tableDoCmd.RunSQL "delete * from AOD_Historical_Hours" ''Clear tableMsgBox "running" Set xlApp = CreateObject("Excel.Application")''Path of the file being importedWorkbook_Array = Array("D:\N15_Charts\MSRS_&_RAP CTR iFACTS.xls","D:\N15_Charts\RDP & CMS CTR iFACTS.xls", "D:\N15_Charts\SEC CTRiFACTS.xls", "D:\N15_Charts\WDM & SCC CTR iFACTS.xls","D:\N15_Charts\CGW_&_SIS CTR iFACTS.xls", "D:\N15_Charts\CxSS CTRiFACTS.xls", "D:\N15_Charts\FDP CTR iFACTS.xls","D:\N15_Charts\ADS_&_TOOLS CTR iFACTS.xls", "D:\N15_Charts\CMTOOLS CTRiFACTS.xls") For Each intI In Workbook_Array strPath = intI ''Establish workbook stringSet xlBook = xlApp.workbooks.Open(strPath, False, True) ''Loop through all the Sheets in the workbookFor Each xlSheet In xlBook.WorksheetsOn Error Resume NextsheetRange = xlSheet.Name ''if the worksheet''s name contains EV then import the dataIf sheetRange Like "*EV*" Then''Transfer the data from the worksheetDoCmd.TransferSpreadsheet acImport, _acSpreadsheetTypeExcel5, _XLTARGET, _strPath, _False, _xlSheet.Name & "!A25:J80"Set rst = Current_DB.OpenRecordset("Temp_Historical_Hours") ''Get the overarching data into local variablesCSCI = xlSheet.Namecsci_flag = Falserst_csci.MoveFirst Do While Not rst_csci.EOF And Not csci_flag If CSCI Like "*" & rst_csci!CSCI.Value & "*" Thencsci_flag = TrueCSCI = rst_csci!CSCI.ValueEnd Ifrst_csci.MoveNextLoopAOD = rst!F1.ValuePhase = rst!F2.Value ''Get today''s dateToday = CDate(FormatDateTime(Now, vbShortDate))''move to the first record with a date in it, know this fromthe spreadsheetrst.MoveNextrst.MoveNext''convert to date formatweek_end = CDate(FormatDateTime(rst!F1.Value, vbShortDate)) Do While Not IsEmpty(CDate(FormatDateTime(rst!F1.Value,vbShortDate)))If IsEmpty(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))ThenMsgBox "this is not actually possible"End If ''On Error Resume Next''MsgBox "I''m in"rst_update.AddNewrst_update![AOD] = AODrst_update![Week Ending] =CDate(FormatDateTime(rst!F1.Value, vbShortDate))If IsNull(rst_update![Week Ending]) ThenMsgBox "this is driving me round the bend"End If''MsgBox rst_update![Week Ending]rst_update![CSCI] = CSCI''MsgBox rst_update![CSCI]rst_update![Phase] = Phase''MsgBox rst_update![Phase]rst_update![Hours Type] = "Implementation"''MsgBox rst_update![Hours Type]rst_update![Hours Total] = rst!F2.Value''MsgBox rst_update![Hours Total]rst_update![Phase % Complete] = rst!F10.Value''MsgBox rst_update![Phase % Complete]rst_update.Update''MsgBox "and I should have done summat"rst.MoveNext''week_end = CDate(FormatDateTime(rst!F1.Value,vbShortDate))Loop ''end of status addition ''Commit changes to the recordset''rst_update.UpdateDoCmd.RunSQL "delete * from Temp_Historical_Hours" ''Cleartable End If''loop to next worksheet in workbookNext''MsgBox "Complete " & strPath''loop to next workbook in arrayNext xlBook.Application.Quit ''Delete data that does not applyDoCmd.OpenQuery "Delete_blanks" ''Delete last weeks data from tables AOD, CSCI_AOD, CSCI_AOD_Phase,CSCI_AOD_HLD (DD, Code, CBT, UCT)DoCmd.RunSQL "delete * from CSCI_AOD_HLD"DoCmd.RunSQL "delete * from CSCI_AOD_DD"DoCmd.RunSQL "delete * from CSCI_AOD_Code"DoCmd.RunSQL "delete * from CSCI_AOD_UCT"DoCmd.RunSQL "delete * from CSCI_AOD_CBT"DoCmd.RunSQL "delete * from CSCI_AOD_Phase"DoCmd.RunSQL "delete * from CSCI_AOD"DoCmd.RunSQL "delete * from AOD" ''Run the append query to add this week''s data inDoCmd.OpenQuery "Append_AOD1"DoCmd.OpenQuery "Append_CSCI_AOD"''DoCmd.OpenQuery "Append_AOD_Phase"''DoCmd.OpenQuery "Append_HLD"''DoCmd.OpenQuery "Append_DD"''DoCmd.OpenQuery "Append_Code"''DoCmd.OpenQuery "Append_UCT"''DoCmd.OpenQuery "Append_CBT" ''Error Handler used to catch error when the fields are blankExit Function Exit_Handler:On Error Resume Next Err_Handler:On Error Resume NextEnd Function 这篇关于IsNull异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！