[VBA] MSAccessテーブルのレコード数を確認する

MSAccessテーブルのレコード数を、Excelにまとめて出力するサンプルです。

Dim Engine As DAO.DBEngine
Dim Database As DAO.Database
Dim TableDef As DAO.TableDef

' 3343 データベースの形式を認識できません エラー対策
Set Engine = CreateObject("DAO.DBEngine.120")
Set Database = Engine.OpenDatabase("データベース.accdb")

Dim RowNum As Long
RowNum = 1

For Each TableDef In Database.TableDefs
  If Left(TableDef.Name, 4) = "MSys" Then GoTo CONTINUE
  ' リンクテーブルは除外
  If Len(TableDef.Connect) > 0 Then GoTo CONTINUE

  Debug.Print TableDef.Name
  Cells(RowNum, 1).Value = TableDef.Name
  Cells(RowNum, 2).Value = TableDef.RecordCount
  RowNum = RowNum + 1

CONTINUE:
Next

Excelの表をMSAccessテーブルに貼り付けすると、欠落する

Excelで作った表をMSAccessテーブルにコピペすると、欠落するケースがあります。
状況によって事情が異なります。

• １行目が欠落するケース
• 特定のカラムがカットされるケース

■ １行目が欠落するケース

１行分が消失します。これは見出しチェックが働いて、１行目を見出しとしてカットしてしまうためです。

Access2010にてExcelからAccessテーブルへのコピー＋ペーストで登録されないレコードがある。
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_access-mso_other/access2010%E3%81%AB%E3%81%A6excel%E3%81%8B/6192e312-a292-49e3-9ce5-65e15e5238d0

■ 特定のカラムがカットされるケース

１行目以降でも発生するものです。

１．テキスト型なのに、数値だけで構成されることが多いケース

（例）テーブル定義
フィールド１ （テキスト型）
フィールド２ （テキスト型）
フィールド３ （テキスト型）

コピペするExcelのデータ

フィールド１	フィールド２	フィールド３
id1	name1	123456
id2	name2	123456
id3	name3	123ABCD

これを、MSAccessテーブルに貼り付け

フィールド１	フィールド２	フィールド３
id1	name1	123456
id2	name2	123456
id3	name3

フィールド３の最後の行だけ、消失しています。


２．数値だけで構成されるが、関数で出力したものを含むケース

コピペするExcelのデータ

フィールド１	フィールド２	フィールド３
id1	name1	123456
id2	name2	123456
id3	name3	123456

数値に見えるが、実際は関数が出力した文字列であったケースです。

これを、MSAccessテーブルに貼り付け

フィールド１	フィールド２	フィールド３
id1	name1	123456
id2	name2	123456
id3	name3

フィールド３最後の行が消失ます。

上記から、テーブルのカラム型ではなく、Excel側でコピーしたデータの型を推測している様子です。

[VBA] CSVファイルの中身をソートする

CSVファイルの内容をソートする方法について

例えば、Excelシートに移してから、Excelの機能で並び替える。
（少量ならプログラム無くても可能）

これをVBAで繰り返しやるとしたら、例えば
Dim Fso As New Scripting.FileSystemObject
Dim List As Object
Set List = CreateObject("System.Collections.ArrayList")

Dim Fin As TextStream
Set Fin = Fso.OpenTextFile(Filename:="csvファイル1.csv")
Do Until Fin.AtEndOfLine
  List.Add (Fin.ReadLine)
Loop
Fin.Close

List.Sort

Dim Fout As TextStream
Set Fout = Fso.CreateTextFile(Filename:="csvファイル2.csv")
Dim Value As Variant
For Each Value In List
  Debug.Print Value
  Fout.WriteLine Value
Next
Fout.Close

[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

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

[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

[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

[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