はじめに
会社でのデータ管理は、基本的にすべてExcel。
ファイルの管理や、バージョン管理が大変。
そのためWeb化＆データベース化の推進をしていきたい。
Excel文化が根強いため、ひとまずExcelベースでデータベース化を目指す。
今回は標題のとおり、ExcelとMySQLとの連携を調査。
と、言っている私もプログラミングは少しかじったレベルのため学習しつつチャレンジ。
調査環境
- Server version: 5.7.29 MySQL Community Server (GPL)
- Microsoft(R) Excel for Office 365 MSO (16.0.12527.20612) 32 ビット
MySQL for Excel
システム
Excelアドイン
概要
Excel上のアドイン機能からMySQLの読み込み編集が可能.
英語。
機能紹介
接続
読み込み
編集
レビュー
アドイン単体のインストールのため、導入は初心者でも楽
また、データベースの知識を伝えずに機能を使える。
しかし、VBAとの連携機能はなく(?)、汎用性・自動化は難しいか。
導入方法
If you already have a MySQL server installed.
- ツリーを展開して「MySQL for Excel」を選択し、右Windowに移動。
- Next を押す
Click Execute
Visual Studio 2010 Tools for Office Runtime is not installed がないと言われているので,
Executeを押して不足しているRequirementをインストール。
Accept してInstall
Intall Now
Click Finish
Click Next >
Requirements がInstall され準備ができたので、次のStepに進めるように。
Click Execute
Install完了
ODBC
システム環境
ODBCドライバ ⇔ Excel
ODBCドライバ ⇔ VBA
概要
Open Data Base Connectivity の略。
ODBCドライバを使うことで、Excel・VBAとデータの入出力をすることができる。
機能紹介
ODBC 接続と読み込み(Excel)
接続設定
DSN: Data Source Name
Input Database Profile
Execute Test
テーブルの取り込み
 |
|---|
 |
|---|
「読み込み」を選択するとアクティブセルにテーブルを挿入。
「読み込み先...」を選択すると挿入先を選択可能。
ODBC DB操作1(VBA)
-
SQL_CONNでユーザー情報とDB名を設定。 -
SQL_OrderでSQL文を設定。 -
SQL_Orderを実行する。- CREATE、INSERTはSQL文がそのまま実行される。
- SELECTのときは取得したレコードをRecords(配列変数)に返すので、お好みで調理。
- SQL文ごとに
SQL_Orderを複製して使用することを想定。
Sub SQL_Order()
' このプロシージャでSQL文を設定して、RunSQL(Function)に渡すと、 SQL文を実行する。'
' また、SQL文がSELECTの場合は、Records(配列)に結果を返す。'
' データベースの設定は、SQL_CONN(Function)で行う。'
Dim SQL As String
Dim Records As Variant
' SELECT'
SQL = "SELECT * FROM usage_history"
retVal = RunSQL(SQL, Records)
End Sub
'----------------------------------------------------------'
Function RunSQL(ByVal SQL, ByRef Records)
' SQL処理のコントローラ。'
' Conn(DB接続) → Exec(SQL実行)'
' → GetDataArray(データを配列に取得) → Close(後処理)'
On Error GoTo ErrorTrap
Dim adoCon As Object ' ADOコネクション'
Dim adoRs As Object ' ADOレコードセット'
Dim i As Long
' Conn'
retVal = SQL_CONN(adoCon, adoRs)
' Exec'
retVal = SQL_EXEC(adoCon, adoRs, SQL)
' Get Data Array'
If LCase(Left(SQL, 6)) = "select" Then ' SELECTのときだけ配列生成'
ReDim Records(adoRs.RecordCount, adoRs.Fields.Count - 1)
retVal = SQL_RECORD(adoRs, Records)
' Close'
retVal = SQL_CLOSE(adoCon, adoRs)
Else
adoCon.Close: Set adoCon = Nothing
End If
Exit Function
ErrorTrap:
MsgBox (Err.Description)
End Function
'----------------------------------------------------------'
Function SQL_CONN(ByRef adoCon, ByRef adoRs)
On Error GoTo ErrorTrap
' ADOコネクションを作成'
Set adoCon = CreateObject("ADODB.Connection")
' ODBC接続'
adoCon.Open _
"DRIVER={MySQL ODBC 8.0 Unicode Driver};" & _
" SERVER=localhost;" & _
" PORT=3306;" & _
" DATABASE=admin_pc_usage;" & _
" STMT=SET NAMES sjis;" & _
" UID=root;" & _
" PWD=;"
Exit Function
ErrorTrap:
Set adoRs = Nothing
Set adoCon = Nothing
MsgBox (Err.Description)
End Function
'----------------------------------------------------------'
Function SQL_EXEC(ByRef adoCon, ByRef adoRs, ByVal SQL)
'adoRs.RecordCountを取得するため、カーソルの設定をクライアント&静的に変更。'
' SQLの実行'
Set adoRs = CreateObject("ADODB.Recordset")
adoRs.CursorLocation = adUseClient
adoRs.Open _
Source:=SQL, _
ActiveConnection:=adoCon, _
CursorType:=adOpenStatic
End Function
'----------------------------------------------------------'
Function SQL_RECORD(ByVal adoRs, ByRef Records)
' 配列を生成する。1行目に列タイトル、2行目～にレコード。'
i = 0
Do Until adoRs.EOF
For j = 0 To adoRs.Fields.Count - 1
If i = 0 Then: Records(i, j) = adoRs.Fields(j).Name
Records(i + 1, j) = adoRs(j).Value
Next j
adoRs.MoveNext
i = i + 1
Loop
End Function
'----------------------------------------------------------'
Function SQL_CLOSE(ByVal adoCon, ByVal adoRs)
On Error GoTo ErrorTrap3
' 解放処理'
adoRs.Close
adoCon.Close
Set adoRs = Nothing
Set adoCon = Nothing
ErrorTrap3:
Set adoRs = Nothing
Set adoCon = Nothing
MsgBox (Err.Description)
End Function
'----------------------------------------------------------'
ODBC DB操作2(VBA)
Create Table
Sub Order_CreateTable()
' このプロシージャでSQL文を設定して、RunSQL(Function)に渡すと、 SQL文を実行する。'
' また、SQL文がSELECTの場合は、Records(配列)に結果を返す。'
' データベースの設定は、SQL_CONN(Function)で行う。'
Dim SQL As String
Dim Records As Variant
' CREATE'
DataCnt = Range("C6").End(xlDown).Row - 6
SQL = "Create Table " & Range("D4") & "(" '& vbCrLf'
For r = 0 To DataCnt - 1
' Col_Name'
buf = "`" & Range("C" & r + 7) & "`" & " "
SQL_BODY = SQL_BODY + buf
' TYPE'
buf = Range("D" & r + 7) & " "
SQL_BODY = SQL_BODY + buf
' NULL'
If Range("E" & r + 7) = 1 Then: _
SQL_BODY = SQL_BODY + "NOT NULL "
' Auto_Increment'
If Range("F" & r + 7) = 1 Then
SQL_BODY = SQL_BODY + "AUTO_INCREMENT "
mem_AI = Range("C" & r + 7)
End If
' Comment'
If Range("H" & r + 7) <> "" Then
SQL_BODY = SQL_BODY + "COMMENT '" & Range("H" & r + 7) & "'"
End If
' Next'
SQL_BODY = SQL_BODY & "," '& vbCrLf'
SQL_BODY = Replace(SQL_BODY, " ,", ",")
Next r
SQL = SQL & SQL_BODY
If mem_AI <> "" Then: _
SQL = SQL & "PRIMARY KEY (`" & mem_AI & "` ))"
retVal = RunSQL(SQL, Records)
End Sub
Insert Record
Sub Order_InsertRecord()
Dim SQL As String
Dim Records As Variant
' INSERT'
'INSERT INTO admin_pc_usage.年休申請'
'(申請者, 申請日, 種別, 備考, 登録日)'
'VALUES('', '', '', '', '');'
SQL = "insert into " & Range("D6") & "(申請者, 申請日, 種別, 備考, 登録日) " & " values "
buf = "('" & Range("D8") & "','" & Range("D9") & "','" & Range("D10") & "','" _
& Range("D11") & "','" & Now() & "');"
SQL = SQL & buf
retVal = RunSQL(SQL, Records)
End Sub
Select
Sub Order_SELECT()
Dim SQL As String
Dim Records As Variant
' SELECT'
SQL = "SELECT * FROM " & Range("D4")
retVal = RunSQL(SQL, Records)
WriteRecords (Records)
End Sub
UPDATE
Sub Order_getRecord()
' UPDATE するレコードを選択して取得'
UserForm1.Show
End Sub
Private Sub CommandButton2_Click()
Dim lRow As Long, i As Long
Dim ListNo As Long
ListNo = ListBox1.ListIndex
If ListNo <= 0 Then
MsgBox "いずれかの行を選択してください"
Exit Sub
End If
With Worksheets("UPDATE")
.Range("D7") = ListBox1.List(ListNo, 0)
.Range("D8") = ListBox1.List(ListNo, 1)
.Range("D9") = ListBox1.List(ListNo, 2)
.Range("D10") = ListBox1.List(ListNo, 3)
.Range("D11") = ListBox1.List(ListNo, 4)
End With
Unload UserForm1
End Sub
Private Sub CommandButton3_Click()
Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Dim SQL As String
Dim Records As Variant
' SELECT'
SQL = "SELECT * FROM 年休申請"
retVal = RunSQL(SQL, Records)
Header = WorksheetFunction.Index(Records, 1)
With ListBox1
.ColumnCount = 6
.ColumnWidths = "40;40;60;40;80;70"
.List = Records
End With
ListBox1.List = Records
End Sub
Sub Order_UPDATE()
Dim SQL As String
Dim Records As Variant
' UPDATE'
'Update 年休申請'
'SET 申請者='', 申請日='', 種別='', 備考='', 登録日='''
'WHERE `申請ID`=0;'
SQL = "Update " & Range("D4") & _
" SET 申請者='" & Range("D8") & "', 申請日='" & Range("D9") & "', 種別='" & Range("D10") & _
"', 備考='" & Range("D11") & "', 登録日='" & Now() & "'" & _
" WHERE `申請ID`=" & Range("D7") & ";"
retVal = RunSQL(SQL, Records)
End Sub
レビュー
- 導入はインストーラを統一すれば簡単。
- Excel上で使用する場合は、ユーザーDSNの設定が必要だが、手順書でカバー可能なレベル。
- 開発は前者(MySQL for Excel) より難易度高いが、割と簡単な部類。
- 要VBA、SQL文基礎。ネット検索で方法がすぐ出てくるレベル。
- 機能性は、VBAでCRUD全てできるので充分。
- 運用面もVBAで処理できるため期待大。
ODBCのインストール
mysql-connector-odbc-8.0.21-win32.msi
Visual Studio 2019 x86 Redistributableがないとエラー
 |
|---|
インストールする
https://aka.ms/vs/16/release/vc_redist.x86.exe
再起動する
ODBCドライバのインストール確認(windowsが64bitの場合)
-
C:\Windows\SysWOW64\odbcad32.exeのodbcad32.exeをダブルクリックで実行。 - ドライバータブをクリックし「MySQL ODBC 8.0 Unicode Driver」があればインストールは成功。
「8.0」はバージョン番号のため環境により違いあり。
Successful Installation
