|
|
|
|
|
||
|
||
昨年からずっと忙しくて、いろいろ書きたいネタはあったんだけど、結局まる一年更新さぼってたので軽いネタでとりあえず埋め草。
住所記入欄から都道府県を抽出する
※ 住所文字列より左（前）に空白他、住所以外の文字が入っていないこと。
※ 〓→住所文字列格納セル（例：A1）
=IF(OR(MID(〓,3,1)="都",MID(〓,3,1)="道",MID(〓,3,1)="府",MID(〓,3,1)="県"),LEFT(〓,3),LEFT(〓,4))
OR()の中がくどいので、Access の In 演算子みたいなのが欲しい。
書式はこんな感じ。
IsIn(MID(〓,3,1),("都","道","府","県"))
戻り値は、何番目の要素にヒットしたか、その順番。0のときはヒットしなかったということ。
これを使って都道府県切り出しを書き直すと…
=IF(ISIN(MID(〓,3,1),("都","道","府","県"))<>0,LEFT(〓,3),LEFT(〓,4))
…どうせ VBA でユーザー関数作るなら VBS 呼び出して正規表現でパターンマッチした方がいいかも。
正規表現でパターンマッチするユーザー関数を TESTRE とすると…
testRE(LEFT(〓,3),"[都|道|府|県]$")
これはいずれかにマッチしたかどうかしかわからない。これを使って書き直したら…
=IF(TESTRE(LEFT(〓,3),"[都|道|府|県]$")),LEFT(〓,3),LEFT(〓,4))
ISIN() と TESTRE() はいずれも必要性高そうなのでソースコード書いたら後で追記する（つもり）
MS Access ではマクロでちょっとしたことをやろうとするとすぐに制約にぶつかって、VBA を使わないと目的が果たせないことが多い。
どうもマクロで完結することはあんまり考えておらず、VBA へ誘うための導入編の扱いではないかと勘繰ってしまう。
しかしいまさら VBA を一般人に習得させるのは利口とは思えない。今勤めている一部上場（ただし持株会社の方）では、IT業界ということもあって VBA 使えて当たり前な雰囲気だけど、自分がこれからベンチャー起こすとか個人企業でスタートしようというときに高い Office Pro なんか選べない。Google Doc や Google Apps で済ませるのは当然。だとすると基本は Javascript と Java か Python だ。PHP は VBA 以上にありえない。
定年もそう遠くはない雌伏の今、VBA なんぞに無駄な時間割いてる余裕はないので、MS Access の仕事を依頼されたらクエリとマクロでお茶を濁そうと思う。
そのためにマクロのさまざまな制約を取っ払うためのユーザー関数を予め VBA で作っておくのが、前回の記事と今回の記事（と、もし続きがあったら次回も）の狙い。嫌なことは一回で済ませ、何度も繰り返さない。
というわけで今回のネタについてなのだが MS Access のマクロで、他の Access のテーブルや Excel のワークシートをインポート｜エクスポート｜リンクするときには"データベース変換"アクションを使うのだけど、これはパラメータとして渡されたファイルパスやテーブル名を式として評価しない。つまり、パラメータ欄でユーザー関数やフォームのコントロールの値を参照するようなやり方ができない。ファイルパスやテーブル名はそのまま決め打ちするしかない。
MS Access をツールとして使うとき、読み込ませるファイル名を事前に固定化すればそういう使い方は有りだろうけど、フルパス固定だと実行環境が変わる度にパラメータの書き換えが必要になる。それは簡便してくれ、と利用者サイドがブーたれてくるのは目に見える。
前回はマクロからファイル選択ダイアログが使えるようにユーザー関数を作ってみた。今回は、取得したファイルパスを使って Access のテーブルや Excel のワークシートを読み書きする手段を提供しよう。
マクロアクションの"データベース変換"も DoCmd.TransferDatabase も呼んでる API は同じだけど、VBA の環境では VBA が式を評価するので、引数に変数やユーザー関数やコントロール参照が指定できる。
DoCmd.TransferDatabase の第一引数でインポート｜エクスポート｜リンクを選択するけど、この値はシステム定数なので、VBA Editor のオブジェクトブラウザで知ることができる。しかし一般人にマクロを作ってもらうときにいちいちシステム定数を認識させるのは嫌がられるだろうから、"import"|"export"|"link" で指定できるようにしたのがミソといえばミソ。
それさえ判っていれば、あとはファイルパスと、読み書きするテーブル名を指定するだけ。
Function doCmdTransferDB(mode As String, fPath As String, sTbl As String, dTbl As String)
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
myDic.Add "import", acImport
myDic.Add "export", acExport
myDic.Add "link", acLink
On Error GoTo ErrorHandle
'Debug.Print "passed:doCmdTransferDB:" & mode; ""
DoCmd.TransferDatabase myDic.Item(mode), _
"Microsoft Access", _
fPath, _
acTable, _
sTbl, _
dTbl
doCmdTransferDB = True
'Debug.Print "passed:successfull"
GoOut:
Exit Function
ErrorHandle:
MsgBox Err.Number & vbCrLf & Err.Source & vbCrLf & Err.Description
doCmdTransferDB = False
GoTo GoOut
End Function
こちらも第一引数を、"import"|"export"|"link" で指定できるようにした。
DoCmd.TransferSpreadsheet にはテーブルの見出しを使うか使わないかという指定や、レンジの指定もできるけど、そこまで細かい指定に対応するよりは、MS Access とやりとりする必要があるワークシートはテーブル形式に整えろ！という内部ルールを徹底させるほうが、マクロを簡単に作れる環境を整備しようというこれらユーザー関数提供の目的に適う。
Function doCmdTransferSS(mode As String, fPath As String, tName As String)
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
myDic.Add "import", acImport
myDic.Add "export", acExport
myDic.Add "link", acLink
On Error GoTo ErrorHandle
'Debug.Print "passed:doCmdTransferSS:" & mode; ""
DoCmd.TransferSpreadsheet myDic.Item(mode), _
acSpreadsheetTypeExcel9, _
tName, _
fPath, _
True ', useRange
doCmdTransferSS = True
GoOut:
Exit Function
ErrorHandle:
MsgBox Err.Number & vbCrLf & Err.Source & vbCrLf & Err.Description
doCmdTransferSS = False
GoTo GoOut
End Function
データベース変換やワークシート変換のマクロアクションにしても、今回提供したユーザー関数にしても、インポートやリンクの際に指定した名前のオブジェクトが存在すると上書きしないので失敗する。そこで、インポートやリンクの前に同じ名前のオブジェクトがあれば消してしまいたいが、オブジェクトを消した後にユーザー関数やマクロアクションが失敗して、一旦終了した後、再度一連のマクロを実行した場合、今度は消すべきオブジェクトが存在しないのでエラーになってしまう。
そこでオブジェクトの削除を VBA で実行し On Error Resume Next …という手もあるけど、今回はオブジェクト削除のアクションを実行する前に、目的のオブジェクト（テーブル）が存在するかどうか確認するユーザー関数を作ってみた。On Error Resume Next でなんでもかんでも通してしまうのも怖いし。
"MSysObjects" は MS Access のシステムテーブル。[メニュー]から[オプション]の設定で可視にできるけど、普段は表示されていない。VBA の中で参照するのに、可視｜不可視の設定は関係ないけど。
Function isExistTbl(tblName As String) As Boolean
isExistTbl = (DCount("*", "MSysObjects", "[Name]='" & tblName & "'") > 0)
End Function
himorogi
2012/03/01 12:17
〜変換マクロアクションのパラメータに式を使う時は、式の先頭に=を置くだけで良かったデス…orz
Access2003 のマクロでは変数が使えない。*1代用としてFormのTextBox コントロールをコンテンツホルダーに使ったりすることが多いが、データ保持だけのためのFormを開くのも鬱陶しいのでマクロから呼べるデータ保持用の関数を作ってみた。
Function rdStaticStocker(Optional argVal As Variant) As Variant
Static myData
If Not IsMissing( argVal ) Then myData = argVal
rdStaticStocker = myData
End Function
この関数は、常にローカル変数 myData の値を返す。引数は省略可能で省略したときはローカル変数 myData を返すだけだが、引数を渡すと渡された値をローカル変数 myData に格納した後、戻り値として返す。
Access のマクロは、テキストファイルの読み込みは[テキスト変換]、Excelワークシートの読み込みは[ワークシート変換]アクションが使えるが、何れもファイル名を決め打ちしないといけないので、実行時に任意のテキストファイルやワークシートを読み込むことができない。
そこでAccessVBAからファイル選択ダイアログを呼び出してファイルやフォルダのパスを返すユーザ関数が必要になる。
AccessVBAでファイル選択ダイアログを使う方法はいくつかあるがAccess2002からFileDialogオブジェクトが使えるようになったので、これを使う。
ただし事前に参照設定でMicrosoft Office 10.0 Object Library以上を参照するように設定しなければならない。
自分で使うためだけならさほど問題ではないのだけど、作成したデータベースファイルを共用するといろいろ面倒なことができてくる。
まず、Accessのバージョンによっては参照設定済のデータベースファイルでObject Libraryが参照不可になる場合がある。例えばAccess2003のOffice Object Libraryは、ServicePack 3とそれ以前ではバージョンが異なるためどちらかのバージョンで参照設定されていると、同じAccess2003でもObject Libraryが参照不可になって参照設定をやり直さなければならない。これは一般人でも説明すれば解決できる問題だが、逆に言えば説明もなしにただ渡されただけでは手も足も出ない事態となる。
そこでFileDialogオブジェクトを実行時に呼び出すように VBA コードを記述して参照設定の問題を回避したのが以下の関数。
まず、フォルダ選択ダイアログのための関数。戻り値はフォルダパス。
Function getFolderPicker(Optional dlgTitle As String = "フォルダ選択") As String
' 【引数】
' dTitle: daialog title string(default:"フォルダ選択")
' 【戻り値】
' Cancel)Null String("")
' OK ) folder path string
Const msoFileDialogFolderPicker As Integer = 4
Dim fDlg As Object
Set fDlg = Application.FileDialog(msoFileDialogFolderPicker)
fDlg.Title = dlgTitle
fDlg.InitialFileName = CurrentProject.Path
If fDlg.Show Then getFolderPicker = fDlg.SelectedItems(1) Else getFolderPicker = ""
End Function
使い方はこんな感じ。
' Visual Basic Editor の Immediate Window に結果を表示(Debug.Print)
Function test_getFolderPicker()
Debug.Print getFolderPicker()
Debug.Print getFolderPicker("フォルダを選んでください")
End Function
目的は Access マクロの[テキスト変換]や[ワークシート変換]アクションに渡すためのファイルパスを取得するためなので、予め[全てのファイル][テキストファイル][Excelワークシート][Accessデータベース]の FileFilter を用意し、引数として FilterIndex を指定するだけにしている。この時、範囲外の FilterIndex を指定すると[全てのファイル] FileFilter が選択されるようにしている。
.FileDialog(msoFileDialogFilePicker)
上記メソッドは、選択可能な値より小さな FilterIndex が与えられたときは一番先頭の FilteFIlter が指定され、選択可能な値より大きな FilterIndex が与えられたときは、一番最後の FileFilter が選択されるが、自作関数では .getOpneFileDialog メソッドに振る舞いを合わせた。
戻り値には選択されたファイルパスを返す。
Function getFilePicker(fIndex As Integer, Optional dTitle As String = "ファイル選択")
' 【引数】
' fIindex:
' 1>→All Files(*.*)
' 1→All Files(*.*)
' 2→Text Files(*.csv;*.txt)
' 3→Excel Files(*.xls)
' 4→Access Database(*.mdb)
' 4<→All Files(*.*)
' dTitle:
' daialog title string(default:"ファイル選択")
' 【戻り値】
' Cancel)Null String("")
' OK ) file path string
Const msoFileDialogFilePicker As Integer = 3
Dim fDlg As Object
Set fDlg = Application.FileDialog(msoFileDialogFilePicker)
fDlg.Title = dTitle
fDlg.InitialFileName = CurrentProject.Path
fDlg.AllowMultiSelect = False
fDlg.Filters.Clear
fDlg.Filters.Add "All Files(*.*)", "*.*"
fDlg.Filters.Add "Text Files(*.csv;*.txt)", "*.csv;*.txt"
fDlg.Filters.Add "Excel Files(*.xls)", "*.xls"
fDlg.Filters.Add "Access Databases(*.mdb)", "*.mdb"
If fIndex > fDlg.Filters.Count Then fDlg.FilterIndex = 1 Else fDlg.FilterIndex = fIndex
If fDlg.Show Then getFilePicker = fDlg.SelectedItems(1) Else getFilePicker = ""
End Function
使い方はこんな感じ
' Visual Basic Editor の Immediate Window に結果を表示(Debug.Print)
Function test_getFilePicker()
Debug.Print getFilePicker(1) ' *.*
Debug.Print getFilePicker(2) ' *.csv;*.txt
Debug.Print getFilePicker(3) ' *.xls
Debug.Print getFilePicker(4) ' *.mdb
Debug.Print getFilePicker(10) ' *.*
Debug.Print getFilePicker(2, "テキストファイルを選んでください")
End Function
IE を Automation で呼ばなくても MSXML から呼べば良い（らしい）。
Windows機を実家の親爺に譲ってしまったので、火曜日まで検証できない。いや、火曜日は連休の休み明けだから仕事溜まって無理かも。
だけど多分これで良いはず。
以下のとおり非同期処理のための下ごしらえが必要になるので下記のままでは駄目だな。
open の URL の次の parameter は非同期モード(ture)か同期モード(false)かの設定。
ただし参考にしたのが JScript の parameter 定義なので WSH でも使えるかどうか不明。呼んでる API は同じだが呼び出し側の環境が違うし。
Webアクセスだから、応答が帰ってこないこともありうるし、sync はやめとこう。async で callback 関数渡せるみたい。
Wait 処理、timeout 処理をどう書くか？
while() と sleep() の組み合わせを使ってる例が多いけど、趣味的には setInterval() 使いたい。調べてみたところ WSH でも new ActiveXObject("htmlfile") で document object を生成することで setInterval() が使える。
new ActiveXObject("htmlfile") で document object を生成すれば jQuery も問題ない。
var oDoc = new ActiveXObject("htmlfile");
var oHTTP = new ActiveXObject("MSXML2.XMLHTTP");
oHTTP.open("GET", "http://d.hatena.ne.jp/himorogi",true);
oHTTP.send();
// wait・timeout 処理
// 読み込み
result = oHTTP.responseText;
これが使えると何が嬉しいかというと web service をブラウザではなく普通（？）の application から呼べる。
非同期対応と jQuery 利用で結構大掛かりになりそうなので、とりあえずこのままにして後日改めて別記事に書く予定。
戻り値は XML 形式（KML）若しくは JSON だけど MSXML 使うので KMLフォーマットでも面倒は少ない（はず）。
google map の geocoding service に GET で渡すには
http://maps.google.com/maps/api/geocode/output?parameters
とすれば良い。
ここで paramaters の先頭に json と書けば JSON形式で返ってくるし、 xml と書けば XML形式で返ってくる。
WSH で WebAPI を叩くの記載がどんどん膨らんできたのに一向に終着する気配がないので、Twitter に続きこちらも後日別記事で。
今でも職場の業務端末が Win XP というのは珍しい話ではないが、職場の業務用システムが IE6 でしかアクセスが保証されていないのは流石に不便。メモリーリークが激しいので激重だし。HTML5 対応なら GoogleMap も軽くなるのに。
嘆いていても仕方ないので WSH を使って改善する方法（WSH から IE をコントロールすると、既に IE 起動していても同一プロセスになるらしいので、リソースを軽減できるかもしれないと思ったため）がないか調べてみた。とにかく出来ることを模索してみる。
※ HTML5 対応については Firefox が admin 権限無し（セキュリティ管理レベルの高い部署なので）でも install できたので外部サイト（ほとんど Google）は Firefox 使うようにした。ただし Firefox の Flash plug-in は admin 権限ないと install できないので Flash 使った page は IE6 で見るしかない。まぁ、業務用 Intranet には関係ない話だから全然構わないが。
InternetExploere を起動
js>var objIE = new ActiveXObject("InternetExplorer.Application");
js>
そのままでは、何も表示されないので IE6 を visible に
js>objIE.Visible = true; -1 js>
InternetExplorer を終了
js>objIE.quit(); js>
WSH には HTMLApplication という IE 類似（ほとんど実行 engine は同じ）の機能があるので WSH で GUI アプリケーションを構築する場合には HTA（HTMLApplication）を使うことが多い（と言うほどには一般には知られてない気もするが）。
しかし、HTA では jQuery UI がそのまま動かないケースもあるので、普通の HTML（或いは XHTML）と、HTA の作り分けをするのがだんだん面倒くさくなった。
そこで、WSH から IE を制御することで、Dynamic に HTML page を表示させ、その中で Form を利用し DialogWindow の代用にしようと考えてみたわけ。
これなら jQuery UI が問題なく使える（はず）なので、見てくれも楽して美しくできる（はず）。
ネタ元：
この記事は現在作成中。（間違って下書きを公開してしまった）