台灣最大程式設計社群網站
線上人數
563
 
會員總數:245454
討論主題:189218
歡迎您免費加入會員
討論區列表 >> office VBA / WinOS >> 請問各位前輩 VBA 原在xp win7 可以正常執行在win10 就有異常訊息
[]  
[我要回覆]
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
請問各位前輩 VBA 原在xp win7 可以正常執行在win10 就有異常訊息
價值 : 20 QP  點閱數:115 回應數:0

樓主

定義
門外漢
0 2
18 3
發送站內信

如下程式碼在 Win7 XP裡可以正常執行,在Win10裡如箭頭標示的地方就會跳出
<所要求名稱或序數的相關集合物件中找不到此項目>

不知有哪個前輩有遇過此情況,勞請指導


Dim excelSQL As String, conStr As String, strDBQ As String
strDBQ = "DBQ=" + ActiveWorkbook.FullName + ";"
conStr = "Driver={Microsoft Excel Driver (*.xls)};" & _
strDBQ & _
"ReadOnly=True"
excelSQL = "select * from [" + WSH.Name + "$] "
Dim Con As New ADODB.Connection, rst As New ADODB.Recordset
Con.Open conStr
rst.Open excelSQL, Con
Dim MergeNum As Integer, 上次理想模數 As Integer

While Not rst.EOF
'If (RST.Fields("週需求量").Value > 0) Then
MergeNum = rst.Fields("合併").Value

' If MergeNum > 0 Then
上次理想模數 = 0
' End If
If (Sheets("sheet2").Range("成型站").Value = "KY-P1410") Or (Sheets("sheet2").Range("成型站").Value = "KY-P1210") Then
If (MergeNum > 0 And tempRecordNum <> RecordNum) Then
RowIndex = RowIndex + 6
tempRecordNum = tempRecordNum - 6
End If
Else
If (MergeNum > 0 And tempRecordNum <> RecordNum) Then
RowIndex = RowIndex + 1
tempRecordNum = tempRecordNum - 1
End If
End If
'If (MergeNum > tempRecordNum ) Then
If (MergeNum >= tempRecordNum And tempRecordNum <= 0) Then

ReportLayout.generateLayout WSHOut, RowStart, ColStart, PageNum
ReportLayout.generateDateLayout WSHOut, RowStart, ColStart
ReportLayout.setContentFormat WSHOut, RowStart, ColStart
PageNum = PageNum + 1
'tempRecordNum = RecordNum
tempRecordNum = 19
RecordNum = 19
RowIndex = RowStart + 4
RowStart = RowStart + PAGERECLIMIT
End If
Dim 加硫工具群組編號 As String
Dim loRange As Range, 成代Range As Range
Dim 剩餘可用加硫模數 As Integer, 成品代號 As String, 成型機代號 As String
With WSHOut
Dim 加硫模數 As Integer
.Cells(RowIndex, 1).Value = rst.Fields("工具群組").Value ---> 從這裡往後 rst.Fields("****") 都跳出 <所要求名稱或序數的相關集合物件中找不到此項目> 無法寫入excel
成品代號 = rst.Fields("成品代號").Value
.Cells(RowIndex, 2).Value = 成品代號
.Cells(RowIndex, 3).Value = rst.Fields("規格").Value
.Cells(RowIndex, 4).Value = rst.Fields("特性").Value

'加硫模數 = RST.Fields("加硫模數").Value 'edit by bruce
'加硫模數改抓第一天開模數 by taddy 20100111
加硫模數 = rst.Fields("第一天開模數").Value
'加硫模數 = rst.Fields("理想模數").Value
加硫工具群組編號 = rst.Fields("加硫工具群組").Value
上次理想模數 = 0
成型機代號 = " "
成型機代號 = Mid(rst.Fields("成型機代號").Value, 1, 30)
.Cells(RowIndex, 6).Value = 成型機代號
上次理想模數 = 加硫模數

Dim rst1 As New ADODB.Recordset, sql1 As String
rst1.ActiveConnection = Vsqlcon
sql1 = "SELECT toolsetid,(SELECT MIN(qty) FROM aps_temp_tool WHERE id = aps_temp_toolset_line.toolid) moldqty FROM aps_temp_toolset_line " & _
"WHERE toolsetid IN (SELECT UNIQUE toolsetid FROM aps_temp_item_spec WHERE itemid = '" & Trim(rst.Fields("成品代號").Value) & "' AND stepseq = '2' )"
rst1.Open sql1
While Not rst1.EOF ----> 這一段無窮迴圈
If Trim(.Cells(RowIndex, 20 + (day - 7)).Value) <> "" Then
.Cells(RowIndex, 20 + (day - 7)).Value = Trim(.Cells(RowIndex, 20 + (day - 7)).Value) & " ," & rst1("moldqty").Value
Else
.Cells(RowIndex, 20 + (day - 7)).Value = rst1("moldqty").Value
End If
rst1.MoveNext
Wend ----> 這一段無窮迴圈
rst1.Close
'.Cells(RowIndex, 20 + (day - 7)).Value = rst.Fields("模具數").Value

.Cells(RowIndex, 8).Value = rst.Fields("加硫時間").Value
.Cells(RowIndex, 9).Value = rst.Fields("加硫日需求量").Value
.Cells(RowIndex, 10).Value = rst.Fields("週需求量").Value
'.Cells(RowIndex, 12).Value = RST.Fields("上週已完成量").Value

Dim rst2 As New ADODB.Recordset, sql As String
rst2.ActiveConnection = Vsqlcon
sql = "select ordlist from aps_order WHERE codekind = 'AA' AND itemid='" & 成品代號 & "' and weekno = '" & Sheets("sheet2").Range("週別").Value & "' and workstation = '" & Sheets("sheet2").Range("成型站").Value & "' and usrno = '" & Sheets("sheet2").Range("執行人員").Value & "'"
rst2.Open sql
While Not rst2.EOF
.Cells(RowIndex, 7).Value = rst2("ordlist").Value
rst2.MoveNext
Wend
rst2.Close
sql = "select case code5 when 'Y' then '環保油' when '1' then '環保油(1)' else ' ' end laphs from invmas WHERE itnbr='" & Mid(成品代號, 4, 11) & "'"
rst2.Open sql
While Not rst2.EOF
'.Cells(RowIndex, 11).Value = rst2("laphs").Value
.Cells(RowIndex, 4).Value = Trim(.Cells(RowIndex, 4).Value) & " " & rst2("laphs").Value
rst2.MoveNext
Wend
rst2.Close
Set rst2 = Nothing

.Cells(RowIndex, 21 + (day - 7)).Value = rst.Fields("交期").Value

Set 成代Range = BasicData.取得Phase2成代位置(成品代號)
For i = 1 To day
.Cells(RowIndex, 12 + i).Value = WSH.Cells(成代Range.Row, Phase2起始日ColIndex + i - 1).Value
Next i

'bruce 20081008 第一天沒開工,模數一律修正為0
'bcs 20100408KS反應模數為0,先MARK
'If .Cells(RowIndex, 13).Value = 0 Then
' .Cells(RowIndex, 5).Value = 0
'End If
End With
RowIndex = RowIndex + 1
tempRecordNum = tempRecordNum - 1
'End If 'RST.Fields("週需求量").value>0
rst.MoveNext
Wend


搜尋相關Tags的文章: [ VBA win10 ] ,
本篇文章發表於2020-01-03 10:34
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
目前尚無任何回覆
   

回覆
如要回應,請先登入.