2017年11月18日 星期六

EXCEL VBA動態取消工作表保護與VBA專案保護設定

EXCEL VBA動態取消工作表保護與VBA專案保護設定

最新課程:EXCEL高階函數與大數據VBA自動化 提昇工作效率,晉身職場 Excel 高手!http://my.sce.pccu.edu.tw/MS/business/Detail.aspx?ProdId=8IP1_A6090&Source=HotClas

預覽畫面:


若設定了工作表保護之後,
無論是人工輸入或是VBA輸入
但會失敗,VBA則會跳出錯誤畫面,
因此,
可以動態的將保護取消,之後再動態的加上保護,
ActiveSheet.Unprotect ("1234")

不過,因為會需要將密碼寫到程式之中,
所以保護VBA專案裡的程式碼,又是另一個問題,
因此,此次的分享主要是如何VBA動態取消工作表保護與VBA專案保護設定

教師研習時數
=SUMIF(Sheet2!$B$3:$B$16,B2,Sheet2!$C$3:$C$16)+SUMIF(Sheet3!$B$3:$B$14,B2,Sheet3!$C$3:$C$14)

如何保護公式與資料:

**如何動態輸入公式到儲存格中


Public Sub 研習總時數()
   ActiveSheet.Unprotect ("1234")
   For i = 2 To Range("A2").End(xlDown).Row
       Cells(i, "C") = "=SUMIF(Sheet2!$B$3:$B$16,B" & i & ",Sheet2!$C$3:$C$16)+SUMIF(Sheet3!$B$3:$B$14,B" & i & ",Sheet3!$C$3:$C$14)"
   Next
   ActiveSheet.Protect ("1234")
End Sub
Public Sub 研習總時數清除()
   ActiveSheet.Unprotect ("1234")
   For i = 2 To Range("A2").End(xlDown).Row
       Cells(i, "C") = ""
   Next
   ActiveSheet.Protect ("1234")
End Sub



教學影片:


教學影音完整版在論壇:
https://groups.google.com/forum/#!forum/scu_excel_vba2_86

課程特色:
1.如何將函數轉成VBA2.VBA與資料庫快速結合

EXCEL函數 VBA程式設計資料庫是分別屬於三個領域的知識,
但卻是目前大家都需要的一項專業技能,要把三者融合的很好實在非常不容易,
剛好我有近20年的VB程式設計資料庫設計的經驗,
教EXCEL函數與相關課程也有多年,因此清楚如何把最重要的知識教給大家,
ADO資料庫設計的知識非常多,但根據我多年的設計實務經驗,
覺得最重要的是掌握SQL語言,就可以輕易的完成查詢、新增、修改與刪除等功能,
就可以輕易的完成自己想處理的大量資料,大大提高工作效率了!

上課用書是:
Excel函數&VBA其實很簡單(http://www.books.com.tw/exep/prod/booksfile.php?item=0010457292)
Excel VBA 與資料庫整合大活用(http://www.books.com.tw/exep/prod/booksfile.php?item=0010463634)

完整教學影音DVD申請:http://goo.gl/ZlBZE
論壇:http://groups.google.com/group/labor_excel_vba?hl=zh-TW

相關學習:
函數東吳進修推廣部, EXCEL, EXCEL VBA 函數 程式設計 線上教學 excel vba 教學 excel vba指令教學 vba範例教學excel  excel vba教學視頻 函數教學 excel函數

2017年11月7日 星期二

EXCEL匯出資料到MYSQL與PHP、Phthon、R、APP共用資料

EXCEL匯出資料到MYSQL與PHP、Phthon、R、APP共用資料

將資料存放在MYSQL可以解決多平台不同開發工具共用資料問題,
PHP、Phthon、R、APP的共用資料,EXCEL又是最普遍的辦公室應用程式,
所以若能將兩者串連,相信可以讓資料運用更加彈性。
此外,一般會和EXCEL VBA連接的大多都是用到ACCESSMS SQL資料庫
但ACCESS資料庫與MS SQL資料庫都不是自由軟體,所以會有授權費用
另外ACCESS資料庫基本上是單機型的資料庫,對於多台電腦連線就會有問題
雖然可以透過網芳共用來解決,但還是非常的麻煩的,
所以改用MYSQL資料庫,似乎就可以解決前兩者資料庫的問題,
首先它是自由軟體,再者它也可以讓大家在同個網路區段裡連線。
以下介紹如何安裝EXCEL VBA連線
這篇文章主要是在VBA中呼叫ADO,並用SQL語言和MYSQL溝通,
淺顯易懂,而且容易操作,和大家分享,
不過比較困難的部分,
可能要注意:
1.Windoms有沒有安裝IIS,有就一定要停止,或換PORT號。
2.執行ADO一定要安裝MYSQL的驅動程式。
3.連線字串可以參考 ConnectionStrings 網站:https://www.connectionstrings.com/

預覽畫面:


可以先參考:
PHP與雲端資料庫教學懶人包(2015)
https://terry55wu.blogspot.tw/p/blog-page_18.html

PHP雲端資料庫教學之1(打造PHP與MySQL環境)
https://terry55wu.blogspot.tw/2015/03/php1phpmysql.html

這是PHP雲端資料庫第一次上課,主要是了解學生的程度學習需求
並且打造PHP與MySQL環境下載XAMPP安裝的懶人包 [下載]


1.XAMPP563.zip解壓縮到D碟與執行setup_xampp.batxampp-control.exe

啟動Apache 與 MySQL (注意要允許防火牆)


1.如何設定XAMPP與MQSQL的安全性


2.如何新增MQSQL資料庫與資料表
建立資料庫

3.建立資料表


建立問題2資料表


INSERT INTO `問題2`(`編號`, `品名`, `單價`, `數量`, `小計`, `地區`) VALUES ('0001','玩具',399,46,18354,'東')


程式碼:

Sub 新增單筆()

   r = Range("A2").End(xlDown).Row
   A = Cells(r, 1)
   B = Cells(r, 2)
   C = Cells(r, 3)
   D = Cells(r, 4)
   E = Cells(r, 5)
   F = Cells(r, 6)
   Call 利用ADO新增資料(A, B, C, D, E, F)
   MsgBox "資料新增成功!!", vbInformation
End Sub

Sub 新增全部()

   For i = 2 To Range("B2").End(xlDown).Row
       A = Cells(i, 1)
       B = Cells(i, 2)
       C = Cells(i, 3)
       D = Cells(i, 4)
       E = Cells(i, 5)
       F = Cells(i, 6)
       Call 利用ADO新增資料(A, B, C, D, E, F)
       Application.StatusBar = "新增到第" & i - 1 & "筆"
   Next
   MsgBox "資料新增全部成功!!", vbInformation
End Sub

Sub 利用ADO新增資料(A, B, C, D, E, F)

   '1.建立Connection物件
   Set myCon = CreateObject("ADODB.Connection")
   '2.連結資料庫
   myCon.Open "Driver={MySQL ODBC 5.2 UNICODE Driver};Server=127.0.0.1;Database=問題2;User=root;Password=1234;Option=3;"
   '3.建立Recordset物件&連結資料表
   Sql = "INSERT INTO 問題2 (編號, 品名, 單價, 數量, 小計, 地區) VALUES ('" & _
   A & "','" & B & "', " & C & ", " & D & "," & E & ",'" & F & "')"
   'MsgBox (Sql)
   Set myRs = myCon.Execute(Sql)
End Sub

Public Sub 刪除EXCEL資料()

   Range("A2:F" & Range("A2").End(xlDown).Row).ClearContents
End Sub

Sub 從MYSQL資料庫全部匯入()

   '1.建立Connection物件
   Set myCon = CreateObject("ADODB.Connection")
   '2.連結資料庫
   'myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\問題1.accdb;"
   myCon.Open "Driver={MySQL ODBC 5.2 UNICODE Driver};Server=127.0.0.1;Database=問題2;User=root;Password=1234;Option=3;"
   '3.建立Recordset物件&連結資料表
   Sql = "Select * from 問題2"
   'MsgBox (Sql)
   Set myRs = myCon.Execute(Sql)
   Range("A2").CopyFromRecordset myRs
End Sub

Sub 刪除所有MYSQL資料()

   '1.建立Connection物件
   Set myCon = CreateObject("ADODB.Connection")
   '2.連結資料庫
   'myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\問題1.accdb;"
   myCon.Open "Driver={MySQL ODBC 5.2 UNICODE Driver};Server=127.0.0.1;Database=問題2;User=root;Password=1234;Option=3;"
   '3.建立Recordset物件&連結資料表
   Sql = "Delete from 問題2"
   'MsgBox (Sql)
   Set myRs = myCon.Execute(Sql)
End Sub

**如何遠端連線

1.新增使用者

2.設定權限(任意主機)

3.修改連線主機的IP或名稱


**新增全部_進度狀態列


完成畫面:

表單屬性

標籤屬性

Private Sub UserForm_Activate()

   '狀態列歸零
   PB.Width = 0
   '取得總列數
   r = Range("A2").End(xlDown).Row
   For i = 2 To r
       A = Cells(i, 1)
       B = Cells(i, 2)
       C = Cells(i, 3)
       D = Cells(i, 4)
       E = Cells(i, 5)
       F = Cells(i, 6)
       Call 利用ADO新增資料(A, B, C, D, E, F)
       '更新狀態列用400去除
       PB.Width = (i - 1) * (400 / (r - 1))
       '更新表單
       home.Repaint
       Application.StatusBar = "新增到第" & i - 1 & "筆"
   Next
   home.Hide
End Sub

Sub 新增全部_進度狀態列()

   home.Show
   MsgBox "資料新增全部成功!!", vbInformation
End Sub

教學影音(完整版在論壇):

教學影音完整版在論壇:
https://groups.google.com/forum/#!forum/scu_excel_vba2_86

課程特色:
1.如何將函數轉成VBA2.VBA與資料庫快速結合
EXCEL函數 VBA程式設計資料庫是分別屬於三個領域的知識,
但卻是目前大家都需要的一項專業技能,要把三者融合的很好實在非常不容易,
剛好我有近20年的VB程式設計與資料庫設計的經驗,
教EXCEL函數與相關課程也有多年,因此清楚如何把最重要的知識教給大家,
ADO資料庫設計的知識非常多,但根據我多年的設計實務經驗,
覺得最重要的是掌握SQL語言,就可以輕易的完成查詢、新增、修改與刪除等功能,
就可以輕易的完成自己想處理的大量資料,大大提高工作效率了!

完整教學影音DVD申請:http://goo.gl/ZlBZE

相關學習:

EXCEL,VBA,函數,文化大學推廣部,EXCEL VBA 函數 程式設計 線上教學 excel vba 教學 excel vba指令教學 vba範例教學excel  excel vba教學視頻 excel函數教學 excel函數 MYSQL