在此程序中運行最后一個回圈后，我收到一條錯誤訊息。在回圈遍歷 excel 作業簿中的最后一個作業表后收到錯誤訊息，并且該行是“xl.ActiveSheet.Next.Select”（第 17 行來自底部）。誰能給我指點？我已經閱讀并被告知使用 .select 并不理想，但這是我繼承的舊資料庫，并希望在改進代碼之前解決此問題。
該回圈打開一個 excel 檔案并回圈遍歷每個作業表（本作業簿中的 24 個）并將資料匯入訪問資料庫。我已經搜索了這個主題，但許多問題似乎無關，或者更容易除錯。我什至洗掉了除了兩個作業表之外的所有作業表，并且錯誤仍然是可重復的。任何幫助表示贊賞！謝謝！
Private Sub lbl_WV_import_Click()
If gcfHandleErrors Then On Error GoTo Err_frmMe_home_lbl_WV_import
Dim xl As Object, wrkbk1 As Object
Dim pcd As String, pcd_title As String, model As String, station As String, task As String, pic As String, bom_pn As String, c1 As String, c2 As String, spec As String, sApp As String
Dim pn As String, pname As String, ts_part_name As String, ts_part_num As String, model_info As String, color As String, dest As String, tool As String, socket As String, torque As String, misc As String
Dim rev As Integer, zone As Integer, seq As Integer, mod_id As Integer, delta As Integer, st_seq As Integer, qty As Integer, i As Integer, J As Integer, ts_sec As Integer, sec As Integer, row As Integer
Dim pcd_id As LongPtr, stat_id As LongPtr, task_id As LongPtr, task_list_id As LongPtr, pn_id As LongPtr, tool_id As LongPtr, task_step_id As LongPtr
Dim pitch As Double
Set db = CurrentDb()
sApp = "Excel.Application"
If IsAppRunning(sApp) = True Then
Set xl = GetObject(, "Excel.Application")
For Each wrkbk1 In xl.Workbooks
If wrkbk1.Name = "P1_PCD.xlsm" Then
Exit For
End If
Next
strSQL = DLookup("db_loc", "tbl_ext_source") & DLookup("area", "tblArea") & "\P1_PCD.xlsm"
Set wrkbk1 = xl.Workbooks.Open(strSQL)
Else
Set xl = CreateObject("Excel.Application")
xl.Application.Visible = True
strSQL = DLookup("db_loc", "tbl_ext_source") & DLookup("area", "tblArea") & "\P1_PCD.xlsm"
Set wrkbk1 = xl.Workbooks.Open(strSQL)
End If
xl.Workbooks("P1_PCD.xlsm").Activate
xl.Sheets(1).Select
xl.Range("B1").Select
pcd_title = xl.Range("B1").Value
xl.Range("O2").Select
pitch = xl.Range("O2").Value
xl.Range("Q2").Select
pcd = "PCD-" & xl.Range("Q2").Value
xl.Range("R2").Select
rev = xl.Range("R2").Value
xl.Range("K3").Select
model = xl.Range("K3").Value
If IsNull(DLookup("model", "tblModel", "[model] = '" & model & "'")) Then
strSQL = "INSERT INTO tblModel (model) " _
& "Values ('" & model & "')"
db.Execute strSQL
lid = DMax("mod_id", "tblModel")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblModel"", '" & lid & "', '" & fosUserName() & "', ""model"", ""New Record"", '" & model & "')"
db.Execute strSQL
End If
If IsNull(DLookup("pcd", "tblPCD", "pcd='" & pcd & "' AND rev=" & rev & "")) Then
mod_id = DLookup("mod_id", "tblModel", "[model] = '" & model & "'")
bom_pn = DLookup("ItemNo", "PYMAC", "LV = ""00""")
strSQL = "INSERT INTO tblPCD ( pcd, pcd_title, rev, mod_id, pitch, pre_rev, bom_pn ) " _
& "VALUES ('" & pcd & "','" & pcd_title & "', " & rev & ", " & mod_id & ", " & pitch & ", " & rev - 1 & ", '" & bom_pn & "')"
db.Execute strSQL
pcd_id = DLookup("pcd_id", "tblPCD", "pcd='" & pcd & "' AND rev=" & rev & "")
lid = DMax("pcd_id", "tblPCD")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblPCD"", '" & lid & "', '" & fosUserName() & "', ""ALL"", ""New Record"", ""New PCD from excel "" & " & pcd_id & ")"
db.Execute strSQL
Call upload_pymac(pcd_id)
Set db = CurrentDb()
Else
If InputBox("This PCD already exists. Is this another section of this PCD that needs to be imported? [Y/N]") = "y" Then
Else
Exit Sub
End If
End If
pcd_id = DLookup("pcd_id", "tblPCD", "pcd='" & pcd & "' AND rev=" & rev & "")
If IsNull(DMax("seq", "tblTask", "[pcd_id] = " & pcd_id & "")) Then
seq = 1
Else
seq = DMax("seq", "tblTask", "[pcd_id] = " & pcd_id & "")
End If
For J = 1 To xl.ActiveWorkbook.Worksheets.Count
xl.Range("O3").Select
station = xl.Range("O3").Value
If IsNull(DLookup("station", "tblStation", "[station] = '" & station & "'")) Then
If IsNull(DMax("stat_id", "tblStation")) Then
sec = 1
Else
sec = DMax("stat_id", "tblStation") 1
End If
strSQL = "INSERT INTO tblStation ( station, zone_id,sec ) " _
& "VALUES ('" & station & "',1," & sec & ")"
db.Execute strSQL
lid = DMax("stat_id", "tblStation")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblStation"", '" & lid & "', '" & fosUserName() & "', ""ALL"", ""New Records"", ""New record for PCD "" & " & pcd_id & ")"
db.Execute strSQL
End If
stat_id = DLookup("stat_id", "tblStation", "[station] = '" & station & "'")
st_seq = 1
c1 = "C6"
xl.Range(c1).Select
Do Until xl.CountA(xl.ActiveCell) = 0
xl.Range(c1).Select
task = xl.ActiveCell.Value
If Len(task) > 250 Then
task = Left(task, 255)
End If
If IsNull(DLookup("task_txt", "tblTask_txt", "[task_txt] = '" & task & "'")) Then
strSQL = "INSERT INTO tblTask_txt ( task_txt ) " _
& "VALUES ('" & task & "')"
db.Execute strSQL
lid = DMax("task_list_id", "tblTask_txt")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblTask_txt"", '" & lid & "', '" & fosUserName() & "', ""task_txt"", ""New Record"", '" & task & "')"
db.Execute strSQL
End If
task_list_id = DLookup("task_list_id", "tblTask_txt", "[task_txt] = '" & task & "'")
xl.ActiveCell.Offset(0, 4).Select
If xl.ActiveCell.Value = "Q" Then
delta = 1
ElseIf xl.ActiveCell.Value = "C" Then
delta = 2
ElseIf xl.ActiveCell.Value = "CTQ" Then
delta = 3
ElseIf xl.ActiveCell.Value = "R" Then
delta = 4
Else
delta = 0
End If
xl.ActiveCell.Offset(0, 1).Select
spec = Replace(xl.ActiveCell.Value, "'", "''")
If Len(spec) > 250 Then
spec = Left(spec, 255)
End If
strSQL = "INSERT INTO tblTask ( pcd_id, seq, stat_id, task_list_id, st_seq, spec_inst ) " _
& "Values (" & pcd_id & ", " & seq & ", " & stat_id & ", " & task_list_id & "," & st_seq & ", '" & spec & "')"
db.Execute strSQL
task_id = DMax("task_id", "tblTask")
lid = DMax("task_id", "tblTask")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblTask_txt"", '" & lid & "', '" & fosUserName() & "', ""ALL"", ""New Record"", ""New record for PCD "" & " & pcd_id & ")"
db.Execute strSQL
row = xl.ActiveCell.row()
c2 = "E" & row
xl.Range(c2).Select
strSQL = DLookup("db_loc", "tbl_ext_source") & DLookup("area", "tblArea") & "\PICS\" & task_id & ".jpg"
xl.Run "getpic", strSQL
For i = 1 To 10
c2 = "K" & row & ":" & "T" & row
If xl.CountA(xl.Range(c2)) = 0 Then
Exit For
End If
c2 = "K" & row
xl.Range(c2).Select
c2 = xl.ActiveCell.Address
ts_part_num = ""
ts_part_name = ""
pn = ""
pname = ""
pn_id = 0
tool = ""
tool_id = 0
pname = xl.ActiveCell.Value
If pname = "Part Name" Then
Exit For
End If
xl.ActiveCell.Offset(0, 1).Select
pn = xl.ActiveCell.Value
If IsNull(DLookup("part_num", "tblPart_master", "[part_num] = '" & pn & "'")) Then
ts_part_num = pn
ts_part_name = pname
Else
pn_id = DLookup("pn_id", "tblPart_master", "[part_num] = '" & pn & "'")
End If
xl.ActiveCell.Offset(0, 1).Select
qty = xl.ActiveCell.Value
xl.ActiveCell.Offset(0, 1).Select
model_info = xl.ActiveCell.Value
xl.ActiveCell.Offset(0, 1).Select
color = xl.ActiveCell.Value
xl.ActiveCell.Offset(0, 1).Select
dest = xl.ActiveCell.Value
xl.ActiveCell.Offset(0, 1).Select
tool = xl.ActiveCell.Value
If Len(tool) > 1 Then
If IsNull(DLookup("tool_id", "tblTools", "[tool] = '" & tool & "'")) Then
strSQL = "INSERT INTO tblTools (tool, tool_type_id ) " _
& "Values ('" & tool & "',1)"
db.Execute strSQL
lid = DMax("tool_id", "tblTools")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblTools"", '" & lid & "', '" & fosUserName() & "', ""tool"", ""New Record"", '" & tool & "')"
db.Execute strSQL
End If
tool_id = DLookup("tool_id", "tblTools", "[tool] = '" & tool & "'")
End If
xl.ActiveCell.Offset(0, 1).Select
socket = xl.ActiveCell.Value
xl.ActiveCell.Offset(0, 1).Select
torque = xl.ActiveCell.Value
xl.ActiveCell.Offset(0, 1).Select
misc = xl.ActiveCell.Value
strSQL = "INSERT INTO tblTask_steps ( task_id ) " _
& " Values ( " & task_id & " )"
db.Execute strSQL
task_step_id = DMax("task_step_id", "tblTask_steps")
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, RecordSource, RecordID, User, Field, BeforeValue, AfterValue) " _
& "VALUES (Now(), ""tblTask_steps"", '" & task_step_id & "', '" & fosUserName() & "', ""ALL"", ""New Record"", ""New record for PCD "" & " & pcd_id & ")"
db.Execute strSQL
strSQL = "SELECT tblTask_steps.* " _
& "FROM tblTask_steps " _
& "WHERE (((tblTask_steps.task_step_id)=" & task_step_id & "))"
Set rs = db.OpenRecordset(strSQL)
If Len(ts_part_name) > 0 Then
rs.Edit
rs!ts_part_name = ts_part_name
rs.Update
End If
If Len(ts_part_num) > 0 Then
rs.Edit
rs!ts_part_num = ts_part_num
rs.Update
End If
If pn_id > 0 Then
rs.Edit
rs!pn_id = pn_id
rs.Update
End If
If qty > 0 Then
rs.Edit
rs!qty = qty
rs.Update
End If
If Len(model_info) > 0 Then
rs.Edit
rs!model_info = model_info
rs.Update
End If
If Len(color) > 0 Then
rs.Edit
rs!color = color
rs.Update
End If
If Len(dest) > 0 Then
rs.Edit
rs!dest = dest
rs.Update
End If
If tool_id > 0 Then
rs.Edit
rs!tool_id = tool_id
rs.Update
End If
If Len(socket) > 0 Then
rs.Edit
rs!socket = socket
rs.Update
End If
If Len(torque) > 0 Then
rs.Edit
rs!torque = torque
rs.Update
End If
If delta > 0 Then
rs.Edit
rs!delta = delta
rs.Update
End If
If Len(misc) > 0 Then
rs.Edit
rs!misc = misc
rs.Update
End If
xl.Range(c2).Select
xl.ActiveCell.Offset(1, 0).Select
row = xl.ActiveCell.row()
Next i
ts_sec = 0
xl.Range(c1).Select
xl.ActiveCell.Offset(1, 0).Select
If xl.ActiveCell.Column() <> 3 Then
row = xl.ActiveCell.row()
c1 = "C" & row 1
xl.Range(c1).Select
End If
If xl.ActiveCell.Value = "Task Description" Then
xl.ActiveCell.Offset(1, 0).Select
If xl.ActiveCell.Column() <> 3 Then
row = xl.ActiveCell.row()
c1 = "C" & row 1
xl.Range(c1).Select
End If
End If
If IsEmpty(xl.ActiveCell) Then
strSQL = DLookup("db_loc", "tbl_ext_source") & DLookup("area", "tblArea") & "\PICS\" & task_id & "_2.jpg"
xl.Run "getpic", strSQL
xl.ActiveCell.Offset(1, 0).Select
If xl.ActiveCell.Column() <> 3 Then
row = xl.ActiveCell.row()
c1 = "C" & row 1
xl.Range(c1).Select
End If
End If
If xl.ActiveCell.Value = "Task Description" Then
xl.ActiveCell.Offset(1, 0).Select
If xl.ActiveCell.Column() <> 3 Then
row = xl.ActiveCell.row()
c1 = "C" & row 1
xl.Range(c1).Select
End If
End If
c1 = xl.ActiveCell.Address
st_seq = st_seq 1
seq = seq 1
Loop
xl.ActiveSheet.Next.Select
Next J
xl.Application.ScreenUpdating = True
strSQL = "UPDATE tblTask SET tblTask.image_id = [task_id] " _
& "WHERE (((tblTask.pcd_id)=" & pcd_id & "))"
db.Execute strSQL
Call recal_secs(1, pcd_id, 0)
Call sort_stat_all(pcd_id)
MsgBox "The PCD has been imported. Go to stations and assure the zones are correct."
Set rs = Nothing
Set db = Nothing
Exit_frmMe_home_lbl_WV_import:
Exit Sub
Err_frmMe_home_lbl_WV_import:
Call LogError(Err.Number, Err.Description, "frmMe_home_lbl_WV_import()")
Resume Exit_frmMe_home_lbl_WV_import
End Sub
uj5u.com熱心網友回復：
正如我在上面的評論中嘗試建議的那樣，看起來您設計代碼的方式（可能主要基于宏記錄器......）使用不合理的選擇數量，使得代碼在相應的行上出錯，因為事實Next在最后一張之后不存在一張表......
如果您想保持代碼不變并且只解決錯誤，請嘗試在有問題的代碼行上方插入下一個：
If J = xl.ActiveWorkbook.Worksheets.Count Then Exit For
但是您的代碼根本沒有效率。下一部分，例如：
xl.Workbooks("P1_PCD.xlsm").Activate
xl.Sheets(1).Select
xl.Range("B1").Select
pcd_title = xl.Range("B1").value
應該用更有效的替換，不涉及任何激活/選擇：
Dim ws As Object
Set ws = xl.Workbooks("P1_PCD.xlsm")
pcd_title = ws.Range("B1").value
然后，在片材之間回圈的方式也不是有效的。激活作業表并選擇要使用的任何范圍只會消耗 Excel 資源。而不是您使用的方式：
For J = 1 To xl.ActiveWorkbook.Worksheets.Count
你可以用一種不同的、更有效的方式來做到這一點：
Dim sh As Object
For each sh in xl.ActiveWorkbook.Worksheets
station = sh.Range("O3").Value
'your code...
'instead of
'xl.Range(c1).Select
'task = xl.ActiveCell.Value
'you should use:
task = sh.Range(c1).Value ' or better
task = sh.Range("C6").Value 'and eliminate c1 useless variable...
'it is at least strange to use
'c1 = "C" & row 1
'xl.Range(c1).Select
'instead of directly:
sh.Range("C" & row 1) 'selection is useless, anyhow...
Next sh
你的代碼太長了，你沒有用語言解釋你想要完成什么，我不能花太多時間來推斷你嘗試做什么。我只是建議一些必要的改變，以提高代碼質量和速度......
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/497867.html