「Libreoffice VBA」:修訂間差異
跳至導覽
跳至搜尋
| 第21行: | 第21行: | ||
MsgBox "執行完成" | MsgBox "執行完成" | ||
End Sub | End Sub | ||
==判斷INSERT OR UPDATE== | |||
Option VBASupport 1 | |||
Private Sub CommandButton1_Click() | |||
Dim I As Integer | |||
Dim cn As New ADODB.Connection | |||
Dim strCn As String, strSQL As String | |||
strCn = "Provider=sqloledb;Server=192.168.1.x;Database=xxx;Uid=xx;Pwd=xxxxxxxxx;" | |||
cn.Open strCn | |||
I = 3 | |||
Do While Cells(I, 1) <> "" | |||
strSQL = "IF EXISTS (SELECT * FROM CUPRICE WHERE Date = '" &Range("A" & I) &"')" | |||
strSQL = strSQL & "UPDATE CUPRICE SET Exchange = '"&Range("B" & I) &"'" | |||
strSQL = strSQL & "WHERE Date='"&Range("A" & I) &"'" | |||
strSQL = strSQL & "Else " | |||
strSQL = strSQL & "INSERT INTO CUPRICE (Date,Exchange,LPrice,HPrice,LCPrice,HCPrice,L3MPrice,H3MPrice,L3MCPrice,H3MCPrice) VALUES ('" &Range("A" & I) &"','"_ | |||
&Range("B" & I) &"','" &Range("C" & I) &"','" &Range("D" & I) &"','" &Range("E" & I) &"','" &Range("F" & I) &"','" &Range("G" & I) &"','"_ | |||
&Range("H" & I) &"','" &Range("I" & I) &"','" &Range("J" & I)&"')" | |||
'底下寫執行sql語法 | |||
cn.Execute strSQL | |||
I = I + 1 | |||
Loop | |||
cn.Close | |||
Set cn = Nothing | |||
MsgBox "執行完成 RANGE A" &Range("A" & I) | |||
End Sub | |||
==開舊檔案== | ==開舊檔案== | ||
Sub Main | Sub Main | ||
於 2014年11月18日 (二) 15:32 的修訂
相容指令
Option VBASupport 1
SQL輸入範例
Option VBASupport 1
Private Sub CommandButton1_Click()
Dim I As Integer
Dim cn As New ADODB.Connection
Dim strCn As String, strSQL As String
strCn = "Provider=sqloledb;Server=192.168.1.xx;Database=DEMO;Uid=xx;Pwd=xxxx;"
cn.Open strCn
I = 2
Do While Cells(I, 1) <> ""
strSQL = "UPDATE PURTD set TD012='" & Range("E" & I) & "' where TD001='" & Range("A" & I) & "' and TD002='" & Range("B" & I) & "'
and TD003='" & Range("C" & I) & "'"
'底下寫執行sql語法
cn.Execute strSQL
I = I + 1
Loop
cn.Close
Set cn = Nothing
MsgBox "執行完成"
End Sub
判斷INSERT OR UPDATE
Option VBASupport 1
Private Sub CommandButton1_Click()
Dim I As Integer
Dim cn As New ADODB.Connection
Dim strCn As String, strSQL As String
strCn = "Provider=sqloledb;Server=192.168.1.x;Database=xxx;Uid=xx;Pwd=xxxxxxxxx;"
cn.Open strCn
I = 3
Do While Cells(I, 1) <> ""
strSQL = "IF EXISTS (SELECT * FROM CUPRICE WHERE Date = '" &Range("A" & I) &"')"
strSQL = strSQL & "UPDATE CUPRICE SET Exchange = '"&Range("B" & I) &"'"
strSQL = strSQL & "WHERE Date='"&Range("A" & I) &"'"
strSQL = strSQL & "Else "
strSQL = strSQL & "INSERT INTO CUPRICE (Date,Exchange,LPrice,HPrice,LCPrice,HCPrice,L3MPrice,H3MPrice,L3MCPrice,H3MCPrice) VALUES ('" &Range("A" & I) &"','"_
&Range("B" & I) &"','" &Range("C" & I) &"','" &Range("D" & I) &"','" &Range("E" & I) &"','" &Range("F" & I) &"','" &Range("G" & I) &"','"_
&Range("H" & I) &"','" &Range("I" & I) &"','" &Range("J" & I)&"')"
'底下寫執行sql語法
cn.Execute strSQL
I = I + 1
Loop
cn.Close
Set cn = Nothing
MsgBox "執行完成 RANGE A" &Range("A" & I)
End Sub
開舊檔案
Sub Main Dim Doc As Object Dim sUrl As String Dim Dummy() sUrl = "file:///C:/test/test.odt" 」 Doc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, Dummy()) End Sub