パソコンを使う仕事を始めて4年も過ぎました
仕事も暇になったのでメモをつけることにしました
内容は自分のメモ程度に抑えることにしました
間違いや勘違いはいつもあることなので他の人にはほとんど参考にはならないと思うからです
したがってこの文章を読んでやってみたらとんでもない間違いで損害をこうむったなどということへの責任は負わないことを宣言します。
（読む人がいなければこんなことも書かずに済むのですが）
ぼちぼちあたりばったりで追加していきます。
|
 エクセルVBAからデータベースを使用する |
データベースという言葉には憧れがあります。現役のときにコンピュータにさわれないで結果を出してもらっていただけでしたので、なんとなく魔法のようなイメージがあったのです
だけど他人にやってもらうのではなく自分でやりたいとずっと思ってきました。
ところがキーボードから文字を打ち込むことが面倒くさく感じられてさわらないできたのです。
しかしエクセルVBAを使ってスタンドアロンのシステムを作ってみるとスタンドアロンからLANを使ってデータベースに入力したりデータを引き出したりする必要性が高まりました。
データベースもいろいろあります。アクセスは手っ取り早いのですが比較的高価です。
とりあえず秀和システムから出版された豊崎直也著「Access+MySQL実用データベースシステム構築」を購入してみました。
この本によって概略は判ったつもりになりました。
ところがACCESSはエクセルVBAにくらべてフォームの設計の自由度が少ないように感じました。エクセルVBAのフォームはVisualBasicに非常によく似ています。
データの出力はエクセルに出すのが自分としては便利ですし、エクセルはパソコンを買うとついてくる感じに普及されているので「Excel+MySQL実用データベースシステム構築」を作ろうと思った次第です。 |
|
 Excel＋mySQL実用データベースシステム構築のキーテク
システムを構築するためには種々の設定が必要でした
これらのソフトの入手方法などは簡単に紹介します。
ｍｙSQLの操作に力を入れたいと思いますが、実は準備作業がうまく出来るかどうかも大切な手順でしたので出来る限り詳しく記述したいと思います。
ツールを開発した人々のおかげで簡単に出来るようになりました。 |
 ｍｙSQLのインストールするパソコン |
mySQL をインストールするパソコンのOSはNT4.0ワークステーション、Windows2000、Windowsｘｐ、Linuxなどパソコンの電源を入れておけばサービスとしてmySQLが動作するOSがいいと思います。このサービスとして・・・がわからなかったです。電源だけが入っているパソコンを見て使っていないのに電気のムダ遣いをしていると思ったりした時期もありました。
声に出して言わなかったので恥をかかずにすみました。
ｍｙｓｑｌはサービスとして動作させておきます
インストールのやり方はたとえば次のサイトなどに出ています。
http://tsuttayo.sytes.net/mysql/install/win/index.html |
 ODBCmySQLのインストールと設定
googleなどでｍｙｓｑｌ ODBC ドライバーなどとキーワード検索すると関連サイトが表示されます。下記は一例です
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_01.htm
http://dev.mysql.com/doc/mysql/ja/Installing_MyODBC.html |
 かねやんｍｙSQLのインストール
これはすごく便利なものです。これがあったおかげでテーブル作成などが早く進みました。
http://www.mmdb.net/m_kaneko/からダウンロードしインストールしました。
s-jis版とeuc版があります。windowsのときはs-jis版を使いました。 |
 LANの構築
mySQLをインストールしたパソコンは固定IPにします。192.168.1.40などというものです
|
以下少しずつ追加していきます。判りにくいところがあると思います。
MySQLにデータベースとテーブルを用意する
エクセルVBAの用意 |
 エクセルファイルからMySQLにデータをインポートした例
例として雇用保険料のエクセルシートからMySQLテーブルへデータを移す作業をするのを例にとります。CSVファイルからデータをインポートする方法を試したのですがうまくいかなかったので苦肉の策で作ったものです。
かねやんMySQLAdminでデータベースにテーブルを作っておきます
エクセルシートは下図のようにしました。
管理用のエクセルファイルでEXCELVBAで下図のようなフォームを用意します。
あとあといろいろなデータをこの手(方法）でインポートすることを想定してエクセルファイルを指定しています。
ユーザフォームにコンボボックス1個とコマンドボタン１個を配置します。
コンボボックスでエクセルファイルを指定してコマンドボタンをクリックするとデータがインポートされるようにします。
'--------------------------------------------------------
ユーザフォームがアクティブになったときにコンボボックスにエクセルファイル名が表示されるようにします
Private Sub UserForm_Activate()
With ComboBox_テーブル指定
.ColumnCount = 1
.AddItem "koyouhokenryo14_10.xls"
.ListIndex = 0
End With
End Sub
'----------------------------------------------------------------
コマンドボタン名はCommandButton_データ取込実行です。
コマンドボタンをクリックしたときの動作は下記のようになります。
Private Sub CommandButton_データ取込実行_Click()
Dim ThisbookPath As String
Dim DatabookName As String
Dim setCell As Range
Dim rowNo As Integer
Dim N As Integer
'-----管理用のエクセルファイルの下部フォルダーdataの中に
'-----データファイル（koyouhokenryo14_10.xls）を置いておきます。
ThisWorkbook.Activate
ThisbookPath = ActiveWorkbook.Path
ChDir ThisbookPath
Workbooks.Open Filename:=ThisbookPath & "\data\koyouhokenryo14_10.xls"
DatabookName = ActiveWorkbook.Name
Dim cN_delete As ADODB.Connection
Dim rS_delete As ADODB.Recordset
Dim cNstr_delete As String
Dim mySQLstr_delete As String
Set cN_delete = New ADODB.Connection
Set rS_delete = New ADODB.Recordset
cNstr_delete = "DSN=houmonkaigo40"
With cN_delete
.ConnectionString = cNstr_delete
.Open
End With
'-----まず登録されているデータを全削除する-------------------
mySQLstr_delete = "delete from koyouhokenryou ;"
Set rS_delete = cN_delete.Execute(mySQLstr_delete)
' rS_delete.Close
cN_delete.Close
Set rS_delete = Nothing
Set cN_delete = Nothing
'-----データファイルのデータが終わるまでデータを登録します
With ActiveWorkbook
Set setCell = Range("A1")
setCell.Select
N = 1
’-----Do Loopの始まりです。インデントしていないので判りにくいですね。
'-----実際にはインデントしています。
'---シートのデータ行がブランクになるまで回ります。
Do Until setCell.Offset(N, 0) = ""
'------------------------------------------------
Dim cN As ADODB.Connection
Dim rS As ADODB.Recordset
Dim ToukyUu As Integer
Dim cNstr As String
Dim mySQLstr As String
Dim myFileName As String
' Dim N As Integer
Set cN = New ADODB.Connection
Set rS = New ADODB.Recordset
cNstr = "DSN=houmonkaigo40"
With cN
.ConnectionString = cNstr
.Open
End With
'-----登録されている最大のrownoを探す-------------------
mySQLstr = "Select * From koyouhokenryou order by rowno desc ;"
Set rS = cN.Execute(mySQLstr)
If rS.EOF = True Then
rowNo = 0
Else
rS.MoveFirst
rowNo = rS.Fields(0)
rowNo = rowNo + 1
End If
rS.Close
cN.Close
Set rS = Nothing
Set cN = Nothing
'-----データを登録する-------tekiyouA------------,
Set cN = New ADODB.Connection
Set rS = New ADODB.Recordset
cNstr = "DSN=houmonkaigo40"
With cN
.ConnectionString = cNstr
.Open
End With
mySQLstr = "insert into koyouhokenryou " _
& " set rowno = " & rowNo _
& " , ijougaku = " & setCell.Offset(N,
1) _
& " , mimangaku = " & setCell.Offset(N,
2) _
& " , tekiyouA = " & setCell.Offset(N,
3) _
& " , tekiyouB = " & setCell.Offset(N,
4) _
& " , Bikou = " & "'" & setCell.Offset(N,
5) & "'" _
& " ;"
’エクセルVBAでは行継続文字（半角）”_”を使えるのは23行までのようです
Set rS = cN.Execute(mySQLstr)
' rS.Close
cN.Close
Set rS = Nothing
Set cN = Nothing
'-------------------------------------------------
N = N + 1
If setCell.Offset(N, 0) = "" Then
Exit Do
End If
Loop '-----Do Loopのおわりです
End With
setCell.Offset(N - 1, 0).Select
End Sub
インポートした結果です
（かねやんｍｙSQLadminで表示しました）
もちろんエクセルに表示することが可能です
下図はエクセルのシートにrowNoの降順で出力したものです
|
 表のデータをエクセルのシートに表示する
これは次回 |
 mySQLのSQL文でunionが使えないことの対応
ｍｙSQLのver３．XXではunionが使いないようです。使えるのはver４からだそうです
これでは困る場合に早速遭遇しました
対策として下記のようにしました。
１．データを追加するテーブルを用意する（TempTable）
２．第一のテーブル（TableName1)から用意したテーブル（TempTable）にデータを追加する
３．第二のテーブル（TableName2）から用意したテーブル（TempTable)にデータを追加する
・（同様なことを全部のテーブルについて実行する）
・
４．用意したテーブルを並べ替える
上の手順をSQL文は下記のようになりました。
'-----現在のデータを削除する。----------------------
mySQLstr = "delete From TempTable" _
& " ;"
Set rS = cN.Execute(mySQLstr)
'-----新しいデータを追加する-------------------------
mySQLstr = "insert into TempTable select * From " & TableName1
_
& " ;"
Set rS = cN.Execute(mySQLstr)
mySQLstr = "insert into TempTable select * From " & TableName2
_
& " ;"
Set rS = cN.Execute(mySQLstr)
'--------------------------------------------
・（同様なことを全部のテーブルについて実行する）
・
'-----追加されたデータを並べ替える-----------
mySQLstr = "select * from TempTable " _
& " order by tantouname desc ;" '------tantounameは並べ替えのキーフィールド名
Set rS = cN.Execute(mySQLstr)
'--------
|
 時間を意味する文字列（１６：３０）の昇順、降順の並べ替え
ExcelVBAではtimevalue("16:30")*24で数値になる。
SQLで並べ替えをするために
select jikokumojiretu,timevalue(jikokumojiretu) as jikoku from table order
by jikoku desc ;
としてみたらエラーでした。
timevalue関数が使えないです。どうすればいいのでしょう。
|
健康
腰痛とパワーヘルス
パソコンと健康って関係あるのです。健康でなければパソコンなんてやっていられませんです。
実は長年（10年くらい前から）腰痛で苦しんでいました。
椅子に掛けると立ち上がるときに腰がすごく痛くなったのです。食事のとき、車から降りるときなど息が出来ないくらいでした。それで机を改造して立姿勢でパソコンをやっているのです。
それが2004年4月1日から自宅近くにパワーヘルスの体験会場ができたので毎日通い詰めました。
結果21日目で良くなってきたのです。1時間近く椅子の掛けていてもそれほど痛くなくなったのです。今のところ痛さ２０％くらいです。不思議ですね。
5月10日まで会場を開いているとのことでそれまでは体験します。
無料で腰痛が軽減するとはいいですね。
体験会場がなくなったらどうしよう。機械を買わないと出来ません。会員を60名になれば6ヶ月やるそうです。現在４０名だそうです。とりあえず1ヶ月は伸ばすそうです。多分この地区での売上が止まったときには終了になるのだと思います。
・・・・2005年10月追記
上記から1.5年経過しました。インターネットで購入して毎晩使用して来ました。
結果はGOODです。腰の痛みはほとんど消えました。
半年くらいでかなりよくなる傾向は見えていたのですが半信半疑でした。
1年経過ではずいぶんよくなった感じでした。
現在は家の山ノ神にやって使用してもらっています。
なんといっても山の神に丈夫で長持ちしてもらわないとこちらの面倒をみてもらえませんので。
・・・・・2005年12月追記
先日ゴルフのパットの練習を家の中でしているときに前傾したところボキﾂと音がして腰に痛みが走りました。腰痛が再発したと観念しました。
パワーヘルスの効果もだめだったかと落胆でした。
パワーヘルスの効果を過信し腰の落としこみが足りなかったのです。
医者に行く前にパワーヘルスを試してみようとやって見ました。
1週間ほどで回復しました。その後順調でゴルフに出かけてラウンドして異常ありません。しかし再発したくないのでパットの練習時の前傾姿勢は腰を落としてボールを拾うようにしています。
|
パソコン雑記帳