Dim lastid As String
lastid = Cells(Rows.Count, 1).End(xlUp).Value
MsgBox lastid
查詢
ㄧ
布局基本架構
Private Sub CommandButton6_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim path As String
Dim idquery As String
If TextBox1.Text <> "" Then
Call cleardata
Else
MsgBox "請輸入資料"
End If
End Sub
===========================
布局例外
Private Sub CommandButton6_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim path As String
Dim idquery As String
If TextBox1.Text <> "" Then
On Error GoTo Label1
Call cleardata
Label1:
MsgBox "查無此筆資料"
Call browserdata
Else
MsgBox "請輸入資料"
End If
End Sub
=========================
Private Sub CommandButton6_Click()
Dim conn As New ADODB.Connection '資料庫連線
Dim rs As New ADODB.Recordset '資料集
Dim sql As String '資料庫語言
Dim path As String '位址變數
Dim idquery As String '資料來源框查詢變數
If TextBox1.Text <> "" Then
On Error GoTo Label1
Call cleardata
idquery = TextBox1.Text
ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
conn.Open ConnectionString
sql = "select * from persondata where 編號='" + idquery + "'"
rs.Open sql, conn
rs.MoveFirst
Do While Not rs.EOF
Cells(Rows.Count, 1).End(xlUp).Select
path = Selection.Address
Range(path).Offset(1, 0).Value = rs("編號")
Range(path).Offset(1, 1).Value = rs("姓名")
Range(path).Offset(1, 2).Value = rs("血型")
Range(path).Offset(1, 3).Value = rs("學歷")
Range(path).Offset(1, 4).Value = rs("職務")
rs.MoveNext
Loop
rs.Close
conn.Close
Exit Sub '跳出結束
Label1:
MsgBox "查無此筆資料"
Call browserdata
Else
MsgBox "請輸入資料"
End If
End Sub
============================
Public Sub browserdata()
Call cleardata
Sheets(1).Cells(1, 1).Value = "編號"
Sheets(1).Cells(1, 2).Value = "姓名"
Sheets(1).Cells(1, 3).Value = "血型"
Sheets(1).Cells(1, 4).Value = "學歷"
Sheets(1).Cells(1, 5).Value = "職務"
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim path As String
ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
conn.Open ConnectionString
sql = "select * from persondata"
rs.Open sql, conn
rs.MoveFirst
Do While Not rs.EOF
Cells(Rows.Count, 1).End(xlUp).Select
path = Selection.Address
Range(path).Offset(1, 0).Value = rs("編號")
Range(path).Offset(1, 1).Value = rs("姓名")
Range(path).Offset(1, 2).Value = rs("血型")
Range(path).Offset(1, 3).Value = rs("學歷")
Range(path).Offset(1, 4).Value = rs("職務")
rs.MoveNext
Loop
rs.Close
conn.Close
End Sub
Call cleardata
Sheets(1).Cells(1, 1).Value = "編號"
Sheets(1).Cells(1, 2).Value = "姓名"
Sheets(1).Cells(1, 3).Value = "血型"
Sheets(1).Cells(1, 4).Value = "學歷"
Sheets(1).Cells(1, 5).Value = "職務"
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim path As String
ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
conn.Open ConnectionString
sql = "select * from persondata"
rs.Open sql, conn
rs.MoveFirst
Do While Not rs.EOF
Cells(Rows.Count, 1).End(xlUp).Select
path = Selection.Address
Range(path).Offset(1, 0).Value = rs("編號")
Range(path).Offset(1, 1).Value = rs("姓名")
Range(path).Offset(1, 2).Value = rs("血型")
Range(path).Offset(1, 3).Value = rs("學歷")
Range(path).Offset(1, 4).Value = rs("職務")
rs.MoveNext
Loop
rs.Close
conn.Close
End Sub
沒有留言:
張貼留言