- 帖子
- 76
- 主題
- 17
- 精華
- 0
- 積分
- 95
- 點名
- 0
- 作業系統
- windowsxp
- 軟體版本
- office2007
- 閱讀權限
- 20
- 性別
- 女
- 註冊時間
- 2016-8-19
- 最後登錄
- 2021-5-5
|
2#
發表於 2017-3-22 10:23
| 只看該作者
我嘗試用以下代碼解出來了
方法有點笨- Sub autonumber()
- Dim arr, i%
- Dim myCon As Object, myRs As Object, sql$
-
- USE = Worksheets("工作表1").Range("A65536").End(xlUp).Row
- Worksheets("工作表1").Range("A2:C" & USE).ClearContents
- Set myCon = CreateObject("ADODB.Connection")
- Set myRs = CreateObject("ADODB.Recordset")
- myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\test.accdb;"
-
- sql = "SELECT * FROM 資料表1 ORDER BY 資料表1.編號;"
- Set myRs = myCon.Execute(sql)
- Do While Not myRs.EOF
- vl = myRs.Fields(2).Value
- Sheets(1).Cells(2, 1) = vl + 1
- Sheets(1).Cells(3, 1) = vl + 2
- Sheets(1).Cells(4, 1) = vl + 3
- Sheets(1).Cells(5, 1) = vl + 4
- Sheets(1).Cells(6, 1) = vl + 5
- myRs.MoveNext
- Loop
-
- Application.EnableEvents = True
- End Sub
|
|
