2025年4月5日土曜日

accessのODBC接続でSQLサーバからデータ取得するときにVBAでログインのコードは要らなかった。5年後に分かった。

 この記事は、5年前に書いたのだが、実は、このログイン画面を出さないためのvbaのコードは全く不要だった。  実は、ODBC接続のテーブルをインポートする際、「ユーザIDとパスワードを保存する」というチェックボックスが出てくるので、そこにチェックを入れておくだけで良かったのである。 5年間も気が付かなかった。あーそれそれ。おーそれそれ。いやはや、なんとも言えない。灯台もと暮らし。
 accessのODBC接続でSQLサーバからデータ取得するときに、SQLサーバの参照の度にログイン画面がでてくのは、ウザイ。VBAでログイン処理を書いて自動化したい。つまり、ログイン画面をポップアップさせずに、VBAでODBCでSQLサーバにログインし、データを取って来たいのだ。  よくあるやりかたとしては、ADOコネクションを生成し、DB接続情報をセット(ODBC)、接続をOpenするというrecordsetでデータを取得するというアプローチで、1レコードずつ処理することになるので、面倒くさい。
  1. Set objConn = CreateObject("ADO.Connection")
  2. objConn.ConnectionString = "DSN=DSN1;UID=user1;PWD=password1;"
  3. objConn.Open
 SQLサーバのテーブルをリンクした、選択クエリを作成している場合、SQLサーバのテーブルへアクセスするのにログイン画面がでてくる。 そのログイン画面を出さないためには、Docmdを使った「簡単な」方法があるのだ。 以下のコードを標準モジュールのどこかに記述してやる。
  1. DoCmd.DeleteObject  Actable,"dbo_TBL-A"
  2. DoCmd.TransferDatabase acLInk,"ODBC Database", _
  3.  "ODBC;DSN=dsn_name;UID=user_id;PWD=pass_wd; _
  4.  & "DATABASE= database_name",acTable,"tabl_name","dbo.tbl_name"
 このテクニックのミソは、TransferDatabaseでSQLサーバのテーブルへのリンクをログイン画面をださすに接続できる、つまり、自動的にコード内で密かにログインしているところだ。そのためにTransferDatabase acLInkの後ろにDB接続情報を記述している。そこがミソなのだ。ちなみにこの文字列はACCESSのリンクテーブルにカーソルを持っていくと、ヌッと表示されるのだ。試してもらいたい。  そして、SQLのテーブルを参照した選択クエリを実行し、EXCELにエクスポートするという2つの操作を1つのDocmdで実現できてしまうのだ。実に効率がいいね。
Dim file1 As String
Docmd TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xmi,"SELECT_QUERY_USING_SQLserversTable",file1,True

2025年3月26日水曜日

vbaで名前を付けて保存というタイトル名のウィンドウを捕まえて、任意の場所にファイルを保存する

vbaで「名前を付けて保存」というタイトルのウィンドウを捕まえるには、AppActivate "名前を付けて保存"と書く。
そして、保存先を自由に設定するには、フルパスのファイル名で保存すれば良いのである。

SendKeysとAppActivateの組み合わせでRPAを実現してみたが、マウスカーソルを当てないと動かせないアプリがいる。

マウスのカーソル(矢印)を当てなくても、TABキーやENTERキーを叩けば動くアプリは、SendKeysで自動運転することができる。
しかしながら、マウスのカーソル(矢印)を当てないと動かないアプリがある。
そういう場合には、WindowsAPIのSetCursorPos関数とmouse_event関数をつかう。
厄介なのは、マウスのカーソルの座標をどうやって取得するかである。
Windowsアクセサリのペイントは、イメージファイルを開くと、マウスのカーソルの座標を画面左隅に出力してくれる。これを利用してクリックしたいボタンの座標を求めればよい。そこで、まず、クリックしたいアプリの画面をアクティブにして、print screen キーを押下する。そして、ペイントを開いて。その画像をペーストする。次に画像を拡大し、座標を調べたい場所にカーソルを合わせる。すると、ペイントの画面左隅にX座標とY座標を表示されるのである。

2025年2月18日火曜日

dosバッチのログはcmd >> log.txt 2>&1でexcel vbaツールのログはdebugprintモジュールで取得する。

dosバッチのログはcmd >> log.txt 2>&1でexcel vbaツールのログはdebugprintモジュールで取得する。
ログを取るのは自己防御のためである。正しい処理をしているのか、何かトラブルが発生してから対策するのではなく、日頃から自分が何をしたかトレースできる仕組みを構築しておくことが肝要である。
excel vbaの場合、application.statusbar="文字列"でツール画面の左下隅に進行状況を知らせる文字列を出力する。内部トレースのルーチンを作り、そのトレース情報をツールと同じフォルダのdebugprint.txtに出しつつ、application.statusbar=にも設定し、ツール画面の左下隅に出すのも面白いかもしれない。

2025年2月13日木曜日

昔はdir("*.xls*")でxlsやxlsxやxlsmも検出できたと記憶している。ところが今はdir("*.xlsx*")と書かないとxlsに加えてxlsxを検出できない。本当かな?

昔はdir("*.xls*")でxlsやxlsxやxlsmも検出できたと記憶している。ところが今はdir("*.xlsx*")と書かないとxlsに加えてxlsxを検出できない。本当かな?

2025年1月30日木曜日

vbaでAppActivate [title]とSendKeys {TAB} or {ENTER}等々で基幹システムへのログイン、検索といった様々な画面の操作を簡単に自在に操る。

SendKeysとAppActivateの組み合わせでRPAを実現してみた。ポイントは{TAB}と{ENTER}だ。{TAB}や{矢印キー}でカーソルを移動させ、{ENTER}でボタンを押下すれば良いのだ。そして、表示が出てくるのを待つのはApplication.Waitが簡単だが、WindowAPIで画面のボタンのハンドルIDをゲットし、ボタンの状態(アクティブか否か)を得ることができれば、確実に画面表示を待つことができる。

  • まず、AppActivate [title(画面タイトル)]で操作したい画面を捕まえる。
    AppActivate "ログイン"
  • そして、SendKeys {TAB}を繰り返し、目的の場所へカーソルを動かす。
    SendKeys (TAB 2] ・・・2回タブ
  • さらに、SendKeys nn(数値)で設定したい値にする。
    SendKeys ID001でロウインIDを設定する。
  • ふたたびSendKey {TAB}を繰り返し、実行ボタンに辿り着く。
    SendKeys 1234でパスワードを設定する。
  • そして、SendKeys {ENTER}で実行ボタンをクリックする。
    SendKeys {ENTER}でログインボタンをクリックする。
  • 検索のボタンをクリックした後、検索結果の画面が表示されるのを待つには、Application.Waitを使うのが一番、簡単である。Application.Wait Now() + TimeValue("00:00:03")と書くと、3秒待つことができる。
  • windowsAPIの関数を駆使し、検索のボタンのハンドルIDを探すと、確実にボタンの操作することができる。
  • また、検索結果の表示ボタンのハンドルIDが取得できれば、そのボタンがアクティブか否かを検出することで、検索結果の画面を待つことができる。

    以上のようなことをvbaのモジュールに記述すればよい。時々、Appactvateで操作したい画面が捕まらないとか、不可解な現象が発生することがあるので、過信は禁物だ。たまに動かないことがあるので、確認しながら使うのが宜しいと思う。

2025年1月22日水曜日

vba application.FileDialogでフォルダを設定する

menuシートのB5セルにファイルせんたくのダイアログを使用して、出力用のフォルダを設定する。

Sub SetFolder()
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            Worksheets("menu").Range("B5") = .SelectedItems(1)
        End If
    End With
End Sub