excel vba note

2016年9月30日星期五

0930

進階資料庫語言
1.視圖
2.預存程序
3.預存函數
4.觸發器
5.指標存儲過程用
6.事件

視圖 view HeidiSQL_9.3_Portable
預存程序

Public Function dataload(ByVal sql As String, ByVal dbname As String, ByVal pw As String) As String()
Dim conn As New ADODB.Connection '資料庫連線
Dim rs As New ADODB.Recordset '資料集

ConnectionString = "driver={mysql odbc 5.1 driver};database=" & dbname & ";server=127.0.0.1;port=3306;uid=root;password=" & pw & ";opt=3;smt=SET NAMES 'gb2312'"
conn.Open ConnectionString
rs.Open sql, conn

Dim r As Integer
Dim f As Integer
rs.MoveFirst
Do While Not rs.EOF
r = r + 1 '取得總計錄數
rs.MoveNext
Loop
f = rs.Fields.Count '取得總欄位數

ReDim kdata(r, f) As String '設定二維動態陣列

Dim p As Integer
Dim i As Integer
p = 0
rs.MoveFirst
Do While Not rs.EOF
For i = 0 To f - 1 Step 1
kdata(p, i) = rs(i)
Next
p = p + 1
rs.MoveNext
Loop
rs.Close
conn.Close

dataload = kdata

End Function
======================================
Private Sub CommandButton1_Click()
Dim com5 As New Ani5
Dim sql As String
sql = "select * from scoredata"

Dim kdata

kdata = com5.dataload(sql, "persondb", "12345678")

Dim listr As Integer
Dim j As Integer

listr = UBound(kdata, 1)
ListBox1.Clear

For j = 0 To listr - 1 Step 1
ListBox1.AddItem kdata(j, 0)
ListBox1.List(j, 1) = kdata(j, 1)
ListBox1.List(j, 2) = kdata(j, 2)
ListBox1.List(j, 3) = kdata(j, 3)
ListBox1.List(j, 4) = kdata(j, 4)
ListBox1.List(j, 5) = kdata(j, 5)
Next j

End Sub

2016年9月26日星期一

0919

sql = "delete from persondata where 編號='" + id + "'"

Private Sub CommandButton5_Click()
Dim id, sql As String

If TextBox6.Text <> "" Then
id = TextBox1.Text
sql = "delete from persondata where 編號='" + id + "'"
modifydata (sql)
MsgBox "已執行刪除"
Else
MsgBox "請進行查詢後再執行刪除"
End If

End Sub

==================================
Public Function modifydata(ByVal sql As String)
On Error GoTo Label2

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

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

rs.Open sql, conn

conn.Close
Set rs = Nothing
Set conn = Nothing

Exit Function
Label2:
MsgBox "此筆資料已存在"
cleardata
End Function
==================================================

sql = "update persondata set 編號='" + id + "',姓名='" + name + "',血型='" + blood + "',學歷='" + school + "',職務='" + work + "' where 編號='" + id + "'"

where 編號='" + id + "' 為固定指標
=========================================
Private Sub CommandButton4_Click()
Dim sql As String
Dim id As String
Dim name As String
Dim blood As String
Dim school As String
Dim work As String

If TextBox6.Text <> "" Then

id = TextBox1.Text
name = TextBox2.Text
blood = TextBox3.Text
school = TextBox4.Text
work = TextBox5.Text
sql = "update persondata set 編號='" + id + "',姓名='" + name + "',血型='" + blood + "',學歷='" + school + "',職務='" + work + "' where 編號='" + id + "'"
modifydata (sql)

Dim s As String
s = "select * from persondata"
Worksheets("工作表2").displaydata (s)

Else
MsgBox "請進行查詢後再執行更新"
End If
End Sub
=============================
MutiPage元件:
標題1 標題2 標題3
(pages(0) pages(1) pages(2)

Mutipage1.pages(0).caption="標題"
Mutipage1.value=0 啟動時預設頁面

0919

0926

TextBox1.Activate 工作表到焦點
TextBox1.setforcus 表單用

主系統 <==>類別元件
||
=== WebBrowser====AppServ-php====databbase

表單得工具箱=按右鍵=新增控制項=選Microsoft.WebBrowser=確定

表單上的按鈕
Private Sub CommandButton1_Click()
On Error Resume Next
WebBrowser1.Navigate2 ("http://www.google.com")
End Sub

ex:
On Error Resume Next
Dim url As String

If TextBox1 <> "" Then
url = TextBox1.Text
WebBrowser1.Navigate2 (url)
Else
MsgBox "請輸入網址"
TextBox1.SetFocus
End If

WebBrowser1.Navigate2 ("http://192.168.1.26:80/hopechang/php01.php")

結合php

<!DOCTYPE html>

<html>

<body>

<?php

echo "My first PHP script!</BR>";

$con=@mysql_connect("localhost","root","12345678") or die("connect failed");

if(!@mysql_select_db("persondb",$con)) die("DB wanted failure");

mysql_query("set names utf8",$con);

$re=mysql_query("select * from persondata");

while ($tbl=mysql_fetch_array($re)){

print $tbl[0];print ",";

print $tbl[1];print ",";

print $tbl[2];print ",";

print $tbl[3];print ",";

print $tbl[4];print ",";

print "</BR>";

}

mysql_close($con);

</body>

</html>

============================================================

HeidiSQL_9.3 MySql資料庫介面用法

加主鍵

在#1 上按右鍵選創建新索引=primary
從excel匯出cvs檔，匯入資料庫
===================================
進階資料庫語言
1.視圖
2.預存程序
3.預存函數
4.觸發器
5.指標存儲過程用
6.事件

使用視圖 select * from dbview1

dbview1類似匿名為保護資料庫

2016年9月23日星期五

0923

一維參數
一維回傳
二維參數
二維回傳
集合參數
集合回傳

主系統 <===>webBrowser(元件) <=php系統==>AppServ < ===> database

一維參數
一維回傳
class Ani3
Public Function fun1() As Integer()
Dim a(2) As Integer

a(0) = 14
a(1) = 15

fun1 = a
End Function

Private Sub CommandButton5_Click()

Dim con3 As New Ani3

Dim b '由引數決定

b = con3.fun1

MsgBox (Str(b(0)) + ":" + Str(b(1)))

End Sub

==================================================

二維參數

Public Function fun2(b() As Integer)

MsgBox (b(1, 0))

End Function

Private Sub CommandButton6_Click()

Dim a(1, 1) As Integer

a(0, 0) = 12

a(0, 1) = 13

a(1, 0) = 14

a(1, 1) = 15

Dim cin3 As New Ani3

cin3.fun2 a

End Sub

================================

二維回傳

Public Function fun3() As String()

Dim a(1, 1) As String

a(0, 0) = "bill"

a(0, 1) = "lisa"

a(1, 0) = "vovo"

a(1, 1) = "dodo"

fun3 = a

End Function

Private Sub CommandButton7_Click()

Dim cin3 As New Ani3

Dim b

b = cin3.fun3

MsgBox (b(1, 1))

End Sub

===============================

集合元件 Collection

Dim 元件 as New Collection

元件.Add String

set 函數名稱 = 元件

回傳

D變數 Collectuon

set 變數 = 類別元件‧函數

變數‧Item(項目索引值)

===============================

集合回傳

Private Sub CommandButton8_Click()

Dim cin3 As New Ani3

Dim b As Collection '在類別中決定為集合，不須再new

Set b = cin3.fun4

MsgBox b.Item(2)

End Sub

Public Function fun4() As Collection

Dim a As New Collection

a.Add "bill"

a.Add "lisa"

Set fun4 = a

End Function

=========================================

集合參數回傳

Public Function fun5(a As Collection)

MsgBox a.Item(2)

End Function

Private Sub CommandButton9_Click()

Dim cin3 As New Ani3

Dim a As New Collection

a.Add "bill"

a.Add "lisa"

cin3.fun5 a

End Sub

====================================
呼叫函數；無參數無回傳值
無參數有回傳值
有參數無回傳值
有參數有回傳值

當參數或回傳值
1.純變數
2.一維陣列
3.二維陣列
4.元件
=============================
ex:

Private Sub CommandButton10_Click()
Dim qcon As New Qain4
Dim rq '由物件決定
Dim sql As String
Dim idquery As String

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox1.SetFocus

If TextBox1 <> "" Then
On Error GoTo Label1

idquery = TextBox1.Text
sql = "select * from persondata where 編號='" + idquery + "'"
rq = qcon.qfun1(sql) '傳字串參數
TextBox2.Text = rq(1)
TextBox3.Text = rq(2)
TextBox4.Text = rq(3)
TextBox5.Text = rq(4)
Exit Sub
Label1:
MsgBox "查無此筆資料"
TextBox1 = ""
TextBox1.SetFocus
Else
MsgBox "請輸入資料"
TextBox1.SetFocus
End If

End Sub

Private Sub CommandButton2_Click()
Dim com1 As New Ani1
com1.fun2 a:=12, b:=45
End Sub

Private Sub CommandButton3_Click()
Dim com1 As New Ani1
Dim i As Integer

i = com1.fun3
MsgBox i
End Sub
==============================
Public Function qfun1(ByVal q As String) As String()
Dim data(5) As String
Dim conn As New ADODB.Connection '資料庫連線
Dim rs As New ADODB.Recordset '資料集

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
rs.Open q, conn

rs.MoveFirst
Do While Not rs.EOF
data(0) = rs("編號")
data(1) = rs("姓名")
data(2) = rs("血型")
data(3) = rs("學歷")
data(4) = rs("職務")

rs.MoveNext
Loop
rs.Close
conn.Close

qfun1 = data

End Function

訂閱：文章 (Atom)

2016年9月30日 星期五

2016年9月26日 星期一

2016年9月23日 星期五

2016年9月30日星期五

2016年9月26日星期一

2016年9月23日星期五