|
本帖最后由 newsoft1 于 2018-6-2 17:46 编辑
我是直接拿資料庫來改的...
報錯為:當物件關閉時,不允許操作
紅色為錯誤起點.
請高手指點迷津
感謝
Sub 更新資料庫()
Dim myCon As ADODB.Connection
Dim myRS As ADODB.Recordset
Dim nRow As Long
Dim nRow_2 As Long
Dim i As Long
Dim k As Long
Dim j As Long
nRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To nRow
Set myCon = New ADODB.Connection
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\" & Sheet2.Cells(i, "A") & " (日線).xls"
Set myRS = New ADODB.Recordset
' With myRS
' .LockType = adLockPessimistic
' .Source = "價量資料"
' .ActiveConnection = myCon
' .Open
' End With
With Worksheets("Temp")
nRow_2 = .Cells(Rows.Count, "A").End(xlUp).Row
myCon.BeginTrans
k = 0
For j = 2 To nRow_2
'由第一檔股票的位置到最後一檔,搜尋出代號有四碼數字者(普通股)
' If IsNumeric(Worksheets("Sheet1").Cells(i, "A")) = True And Len(Worksheets("Sheet1").Cells(i, "A")) = 4 Then
'將資料寫入資料庫
myRS.AddNew
myRS![Date] = .Cells(j, 1)
myRS![Open] = .Cells(j, 2)
myRS![High] = .Cells(j, 3)
myRS![Low] = .Cells(j, 4)
myRS![Close] = .Cells(j, 5)
myRS![Volume] = .Cells(j, 6)
myRS![Adj Close] = .Cells(j, 7)
myRS.Update
k = k + 1
' End If
'以每100筆為批次寫入資料庫
If k Mod 100 = 0 Then
myCon.CommitTrans
myCon.BeginTrans
End If
Next j
myCon.CommitTrans
End With
Application.StatusBar = Worksheets("Sheet1").Cells(i, "A") & " 价量资料写入完成!"
myRS.Close
Set myRS = Nothing
myCon.Close
Set myCon = Nothing
Next i
End Sub
|
|