☰

スプレッドシートとデータベースを比較してドキュメントを生成する

tag-icon • tag-icon microsoft-excel microsoft-word microsoft-office vba microsoft-access-2007
スプレッドシートとデータベースを比較してドキュメントを生成する

定期的に作成する必要があるドキュメントの作成を効率化しようとしています。タスクには、完了方法に関する詳細な手順が関連付けられています。これらのタスクにはそれぞれ、さまざまなプロジェクトで再利用される特定の ID 番号があります。私は、これらのタスク ID をできるだけ多く Access データベースに取り込みました。
各プロジェクトでは、他の誰かが、私がアクセスできない別のデータベースから必要なすべてのタスクのリストを取得し、標準形式の Excel ブックに入力します。各プロジェクトでは、次のことを行う必要があります。

  • ID番号で関連するすべてのタスクを取得します
  • タスクを完了するための手順やその他の情報が記載されているデータベース内の標準リストと比較します。
  • タスクに関する特定の情報を使用して、特定の形式のドキュメントを生成します。

Excel でマクロを書くのは大変な作業のように思えますが、これが最善策です。ただし、Access データベースが常に同じ場所に留まるかどうかはわかりません。データベースはすべてのプロジェクトで一貫した項目であるため、Access でマクロを書いて、スプレッドシートの場所を尋ねるダイアログを表示することを考えましたが、テーブルを使用して 2 つのリストを比較するのは、Excel 内で比較シートを生成するだけよりも複雑に思えました。Word 内でマクロを書くことはあまり考えませんでしたが、それも可能です。

このセットアップを作成する最も論理的な方法を見つけようとしています。3 つの異なるオフィス プログラムを使用するのはかなり複雑に思えますが、これを実現するために利用できるソフトウェアには限りがあります。

  • オフィス2007
  • 比較を超える
  • インストールされている他のプログラム（追加のソフトウェアをインストールすることはできません）は、タスクとはまったく関係がないようです。

これについてどうしたらよいか、誰か知っていますか?

答え1

Crystal Reports をお持ちの場合は、Excel テーブルを Access テーブルに結合して、ID でデータが一致するレポートと、ID が一致しないレコードを一覧表示するレポートの 2 つのレポートを印刷できます。書式設定はすばらしいです。私は、レポート用に Excel ファイルを Oracle データベースに結合するレポートを作成します。Excel ファイルを ACCESS に結合して、両方に一致する ID フィールドがあると仮定すると、同じタイプのレポートを作成できるはずです。

答え2

ODBC 接続を使用して 2 つのデータセットをリンクすることを検討しましたか?

ODBC と Excel の Google 検索結果

本来の作業環境がデータベースであり、新しいデータ フィールドの入力を自動化できる場合は、単純なクエリを使用して一致しない (新しい) データを選択し、そこから作業することができます。最初のデータベースから自分のデータベースに単純にパンチアウトしたり、最初のデータ セットをエクスポートして新しいデータまたは変更されたデータの CSV を単純にエクスポートしたりできないのは残念です。

どちらの側でも、新しいデータを追加して、既存のデータと比較できます (Access のクエリ、または Excel のピボット テーブルを使用)。単純なブール フラグとして機能する新しい列/フィールドを作成すると、データセットを区別して、同じテーブル/ドキュメントにマージできます。または、毎週 (または任意の期間) のインポート用のコードを適用して、この「フラグ」フィールドを将来のクエリに使用できるようにします。

ピボット テーブルは、データベース クエリよりもスコープ設定の段階で作業を速くでき、レポート/比較の要件を 1 つのビューで表示できるため、最初に使用するツールです。

データを「ウォッシュ」して匿名化できる場合は、より具体的な解決策の例をいくつか投稿してください。

答え3

Excelデータをレコードセットとして取得すると、簡単に比較できるようになります。

Dim xldb As New adodb.Connection
Dim xlcmd As New adodb.Command
Dim XL As New adodb.Recordset

With xldb
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;ReadOnly=True"";"
    .Open
End With

Set xlcmd.ActiveConnection = xldb
xlcmd.CommandType = adCmdText
xlcmd.CommandText = "Select * from [Sheet1$]"
XL.CursorLocation = adUseClient
XL.CursorType = adOpenDynamic
XL.LockType = adLockOptimistic
XL.Open xlcmd

これで、XLレコードセットを使用して検索/ステップ実行し、情報をデータベースに配置できるようになりました。

関連情報