2017年7月17日月曜日

[VBA] MSAccess の全テーブルを CSV に出力する

MSAccess のテーブルを CSV 形式のファイルに出力したい場合は、目的のテーブルからエクスポートしてやれば良いが、まとめて出力したい場合のサンプルです。

Dim Tabledef As DAO.Tabledef
For Each Tabledef In CurrentDB.TableDefs
 If Not Left(Tabledef.Name, 4) = "MSys" Then
  DoCmd.TransferText _
   TransferType:=AcTextTransferType.acExportDelim, _
   TableName:=Tabledef.Name, _
   Filename:=Tabledef.Name & ".csv"
 End If
Next


順序をカスタマイズしたいならば、テーブルでなくクエリを使って出力する。

2017年7月15日土曜日

[VBA] ExcelからDAOで接続すると「データベースの形式を認識できません」

Excel VBA から MSAccess への DAO による接続で、
「3343 データベースの形式 '*.accdb' を認識できません。」
というエラーメッセージについて。

ライブラリの参照設定が古くて(Microsoft DAO 3.6 Object Library)
新しい DAO も選べないので、デフォルトだと 3.6 のままです。

Debug.Print DAO.DBEngine.Version
3.6


MSAccess のバージョンに合う DAO を準備します。

MSAccess 2007 の場合は ACE 12 なので
Dim Engine As DAO.DBEngine
Set Engine = CreateObject("DAO.DBEngine.120")
Call Engine.OpenDatabase(Name:=ThisWorkbook.Path & "\xxx.accdb")


(資料)Office と データベースエンジンのバージョン
https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

2017年7月12日水曜日

[VBA] Oracle テーブルのレコードを CSV 出力する

Oracle 表のデータを CSV ファイルに出力したい要望は多いが、まとめて出力するには良いツールが無い。SQL*Plus の spool で csv のカンマ区切り形式で出力するのも、1行のサイズを調整したり汎用的に使いにくい。

MSAccess から ODBC 経由で Oracle のテーブルにリンクして、このテーブルをCSVファイルに出力してやると、比較的楽に csv の様式で出力できる。

少量ならリンクテーブルでも良いが、今回はまとめて出力するために SQL の編集だけでテーブルを変更できるように、パススルークエリを使用する。
(※注意)ただしVBAの言語仕様では、パススルークエリから csv ファイルをエクスポートできないので、クエリでラップしてやる。

■ 準備するもの
  • パススルークエリ(Name:="PassThrough", SQLText:="SELECT * FROM DUAL")
    接続文字列を指定(ODBC;DSN=xxx;UID=xxx;PWD=xxx;DBQ=xxx)
    ※SQLはこの後で CSV の Export 毎に編集するので、仮でよい
  • クエリ(Name:="Wrapper", SQLText:="SELECT * FROM PassThrough")
    これはパススルークエリを SELECT するだけ
  • [CSV出力対象一覧]テーブル
    まとめて出力する対象を、表を作って登録しておく

■ VBA 実装例
' 参照設定[Microsoft Scripting Runtime]が必要です。
Dim Fso As New Scripting.FileSystemObject

Dim TabName As String
Dim QueryDef As DAO.QueryDef
Dim Recordset As DAO.Recordset
' [CSV出力対象一覧].[テーブル名] に出力対象を登録済み
Set Recordset = CurrentDb.TableDefs("CSV出力対象一覧").OpenRecordset

Do While Recordset.EOF = False
  TabName = Recordset("テーブル名").Value

  ' パススルークエリを変更(※必要ならば、ここでソート順を付ける)
  Set QueryDef = CurrentDb.QueryDefs("PassThrough")
  QueryDef.SQL = "SELECT * FROM " & TabName

  ' クエリ(Wrapper)から、CSV ファイルを出力
  DoCmd.TransferText _
    TransferType:=AcTextTransferType.acExportDelim, _
    TableName:="Wrapper", _
    FileName:=TabName & ".csv"

  Recordset.MoveNext
Loop

2017年3月18日土曜日

[Excel] 全ての文字列の数だけをカウント

数値を除いて、全ての文字列のセル数をカウントする場合は、COUNTIF 関数でシンプルに実現できる。

=COUNTIF(A1:D10, "*")

これでカウントされるもの
  • 文字列
  • 表示形式が文字列のセル
  • 関数の戻り値が文字列のもの

(例)
文字列abc ○ 
数値123×
表示形式が文字列abc
123
表示形式が数値123×
関数の戻り値が文字列=IF(TRUE, "abc")
=IF(TRUE, "123")
関数の戻り値が数値=IF(TRUE, 123)×

[VBA] 保存すると「ファイルが既にあります。置き換えますか」

例えば、VBAでExcelファイルにパスワードを付けて保存したいときに、
「**という名前のファイルが既にあります。置き換えますか?」
というダイアログが出てしまうという件について。

これは恐らく SaveAs というメソッドで保存しようとしているのでは?

SaveAs というのは、「名前を付けて保存」と同じですから、
「上書き保存」にすれば、こういった問題は起こりません。

Dim WB As Workbook
(中略)
WB.Password = "password"
WB.Save


DisplayAlerts を操作してダイアログを抑えるように、暫定対応しようとする人が多いのですが・・・
Application.DisplayAlerts = False
Call WB.SaveAs(Filename:=WB.Name, Password:="password")
Application.DisplayAlerts = True



(参考)
Workbook.SaveAs メソッド (Excel)
https://msdn.microsoft.com/ja-jp/library/office/ff841185.aspx