Excel 連接 MS SQL 資料庫VBA巨集程式

Sub connectSQL()     Dim Conn As ADODB.Connection     Dim sConnect As String     Dim strSqlInstance As String     Dim strSqlDB As String     Dim strSqlUser As String     Dim strSqlPWD As String     ' strSqlInstance = "SERVER_NAME\INSTANCE"     ' 如果伺服器只有裝一次 SQL，就只要輸入伺服器名稱     strSqlInstance = "Your SQL Server Name or IP Address"     strSqlDB = "Your Database Name"     strSqlUser = "sa"     strSqlPWD = "password"     sConnect = "Provider=SQLOLEDB"     sConnect = sConnect & ";DATA SOURCE=" & strSqlInstance & ";Initial Catalog=" & strSqlDB    ' 如果把 User ID=...;Password=... 改成 "Integrated Security=sspi;"，認證方式就變成 AD 登入帳號     sConnect = sConnect & ";User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"     Set Conn = New ADODB.Connection     Conn.ConnectionString = sConnect     Conn.Open    ' 這裡輸入你處理 SQL 的命令     Conn.Close End Sub 實際範例： Sub GetTableValues()     Dim Conn As ADODB.Connection     Dim rs As ADODB.Recordset     Dim intColCounter As Integer     Dim sConnect As String     Dim strSqlInstance As String     Dim strSqlDB As String     Dim strSql As String     Dim strWorkSheet As String         strWorkSheet = "工作表1"     strSqlInstance = "Your SQL Server Name or IP Address"     strSqlDB = "Your Database Name"     strSqlUser = "sa"     strSqlPWD = "password"       sConnect = "Provider=SQLOLEDB"     sConnect = sConnect & ";Data Source=" & strSqlInstance & ";Initial Catalog=" & strSqlDB     sConnect = sConnect & ";User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"     Set Conn = New ADODB.Connection    ' strSql 設定為查詢資料表的 SQL 命令字串      strSql = "SELECT * FROM dbo.Asset"     ' 把資料都放進 rs 資料集合(Recoredset)     With Conn         .ConnectionString = sConnect         .CursorLocation = adUseClient         .Open         .CommandTimeout = 0         Set rs = .Execute(strSql)      End With     ' 清除 Excel 工作表內容     Worksheets(strWorkSheet).Cells.Clear     ' 如果 rs 資料集的資料筆數屬性大於 0     If rs.RecordCount > 0 Then         ' 從 A1 格，開始輸出欄位名稱(橫向 Row)         For intColCounter = 0 To rs.Fields.Count - 1            Worksheets(strWorkSheet).Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name         Next         ' 在 A2 格，一次輸出所有資料內容        Worksheets(strWorkSheet).Range("A2").CopyFromRecordset rs     Else         MsgBox ("找不到數據")     End If     rs.Close     Conn.Close     Set rs = Nothing     Set Conn = Nothing End Sub

Read More

自己寫 Excel 函式

以下是一個簡單的亂數密碼產生函式

首先您在 Excel ，按下 Alt-F11，開啟 Visual Basic，

然後選擇 “插入”→"模組”，把以下程式碼輸入，就完成一個函式 RandPwd()

如果要使用它，就在 Cell 裡輸入 =RandPwd(8)，該格就會產生一個8碼長度的亂數密碼

Public Function RandPwd(Num As Integer) Dim Rand As String Application.Volatile Do i = i + 1 Randomize Rand = Rand & Chr(Int((85) * Rnd + 38)) Loop Until i = Num RandPwd = Rand End Function

Read More