2010のエクスポート&インポートについて

Question

AccessクエリのExcelへのデータエクスポートについて、2003ではできたことが2010ではできなくなって、困っています。

2003でできていたことは、次のとおりです。

1.Access2003のクエリをExcel2003へデータエクスポートするのに、一行目の項目名を同じものにしておくと、既存の名前を付けたExcelブックの既存の名前を付けたワークシートにデータを書き込むことができていました。

2.Excel2003側に、Access2003のクエリ名と同じ名前のシートに項目列の同じ名前があれば、Access2003からのエクスポートデータが、Excel2003シートの項目名を参照して書き込んでくれました。

3.この書き込みは、Ｅｘｃｅｌシートの項目列の並び順を変えても新しい項目列を追加しても成功しました。

4.Excel2003シートの新しい項目列には、Book内の既存シートを参照する式が書いてあり、メンバーに配布するExcel帳票を即座に作ることができておりました。

5.Ａｃｃｅｓｓデータに変動があるたびに、このエクスポートでＥｘｃｅｌを上書きして定型業務を行っていたのです。尚、Access2003でもExcel2003でもマクロやＶＢは使用していません。コマンドレベルで実現していました。

同じことをAccess2010からExcel2010へのエクスポートで実行しましたが、上記のＥｘｃｅｌへの上書きができずに困っています。

1.「Excel にデータをエクスポートする」－Access－Office.com

http://office.microsoft.com/ja-jp/access-help/HA101819737.aspx

に、「データは必ず、新しいワークシートに追加されます。既存のワークシートや指定範囲にデータを付加することはできません。」という記述がある通りでした。

2.「・・・必ず、新しいワークシートに・・・既存の・・・付加することはできません」とわざわざ断っているのは、以前のバージョンでできた機能を削除した・・・ということかとも思っています。

3.項目参照上書ができなくなったために、データが書き込まれた新しいシートから、目視で項目名を参照しながら既存ワークシートに値複写する。という一歩後退した業務になってしまいました。

ところで、Ｑ＆Ａサイトへの投稿や検索をしているうちに、「Accessからエクスポートするのではなくて、Excel側からAccessクエリデータ取り込みをする」という記述を見つけました。

質問と回答の趣旨を見ると、質問は私と同じ発想のようで、回答は逆の発想のアドバイスでした。

既存のExcel2010ワークシートの書式他をそのまま生かしながら、項目名を参照してAccess2010クエリデータを同じ名前の項目列に取り込み上書きしてくれると思えたのですが・・・。

結果は列の挿入でした。既存の項目列は、押しのけられた格好になります。

そこで説明されている方法ですが、「5. そのまま、「プロパティ」をクリック」の中のコマンド遷移が私のExcel2010では見つからず、言われている細かい設定ができないのが要因と思われます。

もう少し詳しく教えていただけないでしょうか？それとも、以前のExcelではできた機能が、Excel2010になって削除されてしまった・・・ということでしょうか？

以下に、その質問と回答をそのまま引用させていただきます。

＜質問＞

QNo.6808941

Excel既存ファイルへのエクスポートについて

accessのクエリのデータをExcelファイルへエクスポートしようとしています。 

Excelファイルは既存のファイルで、書式が設定されています。 

上記のexcelファイルに設定されている書式はそのまま反映されたままで、Accessのクエリのデータのみをエクスポートする方法はありますか？ 

またaccessのフォームにコマンドボタンを設定し、ボタンを押下すると、エクスポート操作ができるようにしたいのですが、どうすればよいでしょうか？

質問者：pannnamama

投稿日時 - 2011-06-14 15:05:14

＜質問者が選んだベストアンサー＞

ANo.1

今回のケースでは、Accessからエクスポートするのではなく、Excel側で、Accessのクエリをインポート（正確には「データの取りこみ」）した方がスムーズです。

手順は、Excel側で

1.「データ」メニューから「外部データの取り込み」→「データの取り込み」

2.「データファイルの選択」画面で、該当するAccessのファイル（mdb)を選択。

3.「表の選択」画面で、該当のクエリを選択。

4.「データのインポート」画面で

　•データを返す先：既存のワークシート

　•データを挿入開始する（一番左上の）セルをクリック。

5.そのまま、「プロパティ」をクリック

　　＊今回は更新することなく必要なときに都度インポートするパターンです。

　•クエリの定義～～のチェックマークを外す。

　•フィールド名/行番号は状況で。

　•変更されたレコードの、、、、は「既存のセルを新規データで、、、」を選択。

　•[OK]を押す。

6.[OK]を押すと実行されます。

回答者：shut0325

投稿日時 - 2011-06-15 10:18:59

繰り返しの質問になりますが、以上の引用の中の

「5.・・プロパティ・・」「・・変更されたレコードの、、、、は「既存のセルを新規データで、、、」を選択」が、

私の欲しい機能と思われるのですが、

一連の「プロパティ」設定が私のExcel2010では見つからないのです。

Excel2010をよく見ると、［データ］［外部データの取り込み］［Ａｃｃｅｓｓデータベース］～の遷移に出てくる［プロパティ］とは別に、［データ］［接続］の中にも［プロパティ］があります。しかし［接続］の中の［プロパティ］は、グレー表示になっていていじれないようです。

できましたら画面遷移図とともに教えていただきたのです。

Answer 1

新バージョンが高機能だ合理的だと頭では理解できても、

手が覚えている永年に亘って慣れ親しんだ方法のほうがよい！ってガラパゴスに陥りがちでした。

何世代か前のＷｏｒｄ差し込み印刷の仕様変更やＷｏｒｄヘッダーフッターの仕様変更に困惑したことを思い出しましたが、今では前のやり方が古ぼけて見えます。

リンクを貼りっぱなしにするとＥｘｃｅｌが重くなって仕方がなかったので好みませんでしたが、 

外部データの接続という新しい発見がありましたので、使っていこうと思います。

たいへんていねいな検討と回答をいただき、ありがとうございました。

Answer 2

楠部 啓＠Microsoft Support　様

アドバイスありがとうございました。JackAmanoです。

私のAccess 2010 でのエクスポートの手順は、楠部　様の「・・・右クリックしてエクスポート > Excel・・・」ではなく「オブジェクトの一覧で対象のクエリをポイントして選択~リボンの［外部データ］［エクスポート］［Ｅｘｃｅｌ］アイコン」という違いはありますが、楠部様の手順と同じでした。

大きな違いは、「・・・既存の・・・」をいつ作成したか！だということに気付きました。

私は、Ｅｘｃｅｌ２００３で作ったもの（．ｘｌｓ）をそのままＥｘｃｅｌ２０１０（．ｘｌｓｘ）に移行。それを既存の Excel ブックの既存のシートであるとして、Ａｃｃｅｓｓ２０１０からエクスポート上書きしようとしてうまくいきませんでした。

前回の返信にも、２００３の世界で「ExcelのＳＨＥＥＴ名は、Accessのクエリ名称と同じです。はじめてＥｘｐｏｒｔしたときに生成されました。」と書きましたように、

２０１０の世界でも楠部様の手順のように、まず初回は、Ａｃｃｅｓｓからエクスポートして「新たに」Ｅｘｃｅｌを作成。それを「既存」として、エクスポート２回目以降を実行するということですね。

ところで、初回の投稿の後半で書いたことですが、試行錯誤を繰り返すうちに発想を変えて、Excel２０１３側からAccess２０１３データをインポートするほうが合理的ではないかと思うようになりました。

以下にＴｒｙ＆Ｅｒｒｏｒの過程を記述しますので、こちらにもアドバイスをいただけましたら嬉しいです。

書き込みをしたい既存のExcel２０１０ＢＯＯＫ（．ｘｌｓｘ）の既存のＳＨＥＥＴをひらいておき、（これはＥｘｃｅｌ２００３（．ｘｌｓ）から移行したものです）

[データ][外部データの取り込み][Ａｃｃｅｓｓデータベース]・・・・と画面指示とおりにコマンドを実行します。

既存のＳＨＥＥＴに書き込みをしてはくれるのですが、既存の列に上書きをするのではなく、書き込みの列が挿入されます。

先の例で言うと、Ｅｘｃｅｌ２００３ＢＯＯＫ＝顧客台帳更新書き出しｙｙｙｙｍｍｄｄのＳＨＥＥＴ＝顧客台帳更新書き出し（既存）の１列目から７列目を右に押しのけ、クエリの１列目から５列目が挿入される。既存の列と新たに挿入された列で、合計１２列・・・という格好になります。

この表は使い物にならないので、新しいＳＨＥＥＴに書き込む方法を採らなければなりません。値複写の手間が増えるのは、Ａｃｃｅｓｓ２０１０エクスポートのときと同じです。

２００３のときは、ＡｃｃｅｓｓからＥｘｃｅｌへのエクスポートで必要十分でありましたので、Ｅｘｃｅｌからのインポートは使ったことがありませんでした。

ただ改めてインポートの仕様についてＥｘｃｅｌ２００３とＥｘｃｅｌ２０１０とを比較しますと、［データのインポート］のプロパティで指定できることが大幅に削られているようです。Ｅｘｃｅｌ２０１０で「プロパティ」アイコンは、［データ］［接続］のところにもありますが、グレー表示になっていてこの時点ではいじれませんでした。

試行錯誤を繰り返しているうちに、若干不満が残りますが、Ｅｘｃｅｌ２０１０で以下のように実行すれば、期待に近いものができることが判りました。

1.      まず、Ｅｘｃｅｌ２００３で使っていたＳＨＥＥＴを２０１０の世界で既存のＳＨＥＥＴとしてそのまま使うのは諦めます。

2.      新たに、Ｅｘｃｅｌ２０１０の［データ］［外部データの取り込み］［Ａｃｃｅｓｓデータベース］で、対象の、Ａｃｃｅｓｓ２０１０クエリを［テーブルの選択］で選択して実行します。このとき［データのインポート］のプロパティが指定できますが、ＮｏＣｈｅｃｋです。

3.      一行おきに網掛けされてデータが書き込まれます。これを以降、既存のＥｘｃｅｌ２０１０ＳＨＥＥＴとして使います。

＊　但し、Ａｃｃｅｓｓ２００３からＥｘｃｅｌ２００３への初回エクスポートのときのようには、Ｅｘｃｅｌ２０１０ＳＨＥＥＴの名前を自動的にＡｃｃｅｓｓ２０１０のクエリ名と同じにしてはくれないので、手書きする必要がありました。

4.      ここでデータの接続が生成されたということでしょうか？Ｅｘｃｅｌ２０１０の［データ］［接続］［プロパティ］がいじれるようになりましたので、以下のように設定しました。

＊　［外部データのプロパティ］ダイアログで「データのレイアウト」は、「列の並べ替え/フィルタ/レイアウトを保持する」のみチェックし

＊　「変更されたレコード（行）のデータ更新時の処理」は、「既存のセルを新規データで上書きし、使用されていないセルはクリアする」を選択しました。

5.      後は、Ａｃｃｅｓｓ２０１０のデータに変動があったとき［データ］［接続］［更新］をするか、［データ］［接続］［接続］［接続のプロパティ］［定義］で接続ファイル［参照］で変更するか、状況に応じて実行すれば、「既存のＥｘｃｅｌＳＨＥＥＴに」新しいデータを書き込めるようです。

6.      しかし、「使用されていないセルはクリア」されてしまいました。

 というふうに、

２００３では繰り返し一発でできていたＡｃｃｅｓｓからＥｘｃｅｌへのエクスポートでしたがそれができなくなったかわりに、２０１０ではＥｘｃｅｌからＡｃｃｅｓｓに常時接続しておいて同期するようにした。

ということで、納得することにいたしました。

もっとよい方法がありましたら教えてくださいませ。但しスキルとして、ＶＢやマクロは、Ｅｘｃｅｌ印刷業務の簡単なものが書ける程度であります。

楠部 啓＠Microsoft Support　様には、たいへんていねいな検討と回答くださったことに御礼申上げます。

Answer 3

JackAmano さん、こんにちは。

手元の Access 2010 (Windows 7 SP1 PC) で、書き込んでもらった Access 2003 の手順を参考にしながらクエリのエクスポートを試してみました。

< 試した内容 >

1. オブジェクトの一覧で対象のクエリを右クリックしてエクスポート > Excel の手順でエクスポートを実行

※ 「エクスポートのオプションを指定してください」 はすべて未チェックです

2. エクスポートした Excel ブックを Excel 2010 で開いて列を追加、列幅と行の高さを変更して上書き保存
3. Access 2010 でデータを追加して、同じ Excel ブックにエクスポートを実行

※ 「エクスポートするオブジェクトで既存のオブジェクトを置き換えますか？」 は [はい] を選択しました

こちらで試した感じだと、既存の Excel ブックの既存のシートに上書きの状態でエクスポートされて、列幅、行の高さの設定も維持されていました。

Access 2003 との手順、設定を比較することで要因が見つかるかもしれないので、Access 2010 でのエクスポートの手順も詳しく書き込んでください。

あと、Access 2010 で簡単な Access ファイルを作成して、クエリのエクスポートを行うとどのようになるかも試してみてください。

返信、お待ちしています。

Answer 4

フォーラムモデレーター楠部 啓様のアドバイスありがとうございます。

２００３でできたのに２０１０では同じ結果にならないというのは、例えば以下の手順のエクスポートです。

Ａｃｃｅｓｓ２００３

クエリ名称＝顧客台帳更新書き出し

クエリ項目＝

<br>1	<br>2	<br>3	<br>4	<br>5
<br>顧客コード	<br>顧客名	<br>年月日	<br>状態変化	<br>担当者コード

これをＥｘｐｏｒｔして、既存のＥｘｃｅｌシートに書き込みます。

Ｅｘｃｅｌ２００３

ＢＯＯＫ名＝顧客台帳更新書き出しｙｙｙｙｍｍｄｄ（日付を新しくして使い回し）

ＳＨＥＥＴ名＝顧客台帳更新書き出し（既存）

<br>1	<br>2	<br>3	<br>4	<br>5	<br>6	<br>7
<br>行番号	<br>顧客コード	<br>顧客名	<br>年月日	<br>状態変化	<br>担当者コード	<br>担当者名
<br>Subtotal	<br>	<br>	<br>	<br>	<br>	<br>Vlookup
<br>Subtotal	<br>	<br>	<br>	<br>	<br>	<br>Vlookup

ExcelのＳＨＥＥＴ名は、Accessのクエリ名称と同じです。はじめてＥｘｐｏｒｔしたときに生成されました。

ExcelＳＨＥＥＴの項目名称は、２列目から６列目までがＡｃｃｅｓｓクエリの項目名と同じです。はじめてＥｘｐｏｒｔしたときに生成されました。

このＡｃｃｅｓｓクエリと同じＥｘｃｅｌシートに１列目と７列目を加えて、既存のＳＨＥＥＴとしました。

1列目「行番号」項目には、フィルタ対応の関数Subtotal（3，参照範囲）が書いてあります。データが書き込まれると連続番号を採番します。

7列目「担当者名」項目には、同BOOK内の別シートにある「担当者コード，担当者名」表を参照する関数Vlookup（担当者コード，担当者名，False）が書いてあります。担当者コードが書き込まれると担当者名が返ります。

Ａｃｃｅｓｓ２００３側のエクスポート手順は、

1.      [基のAccessファイルを開き][データベースウィンドウ]の[オブジェクトバー]から[クエリ]をクリックして選択

2.      その[オブジェクトリスト]から[エクスポートしたいクエリ]＝この場合は＜顧客台帳更新書き出し＞をクリックして選択

メニューバーからコマンドを以下のように実行

3.      [ファイル][エクスポート]‘顧客台帳更新書き出し’[クエリをエクスポート　ダイアログボックス]で

4.      ファイルの種類をMicrosoft　Excel 97-2003（＊.xls）として、

5.      [エクスポート先のファイル名]＝この場合は「顧客台帳更新書き出しｙｙｙｙｍｍｄｄ」を選択

6.      「データベースオブジェクト‘顧客台帳更新書き出し’は既に存在します。エクスポートするオブジェクトで既存のデータベースオブジェクトを置き換えますか？」と聞いてくるので

7.      [はい]をクリック

です。

結果は、クエリの項目1列目から5列目をＥｘｃｅｌ既存ＳＨＥＥＴの2列目から6列目に上書きしてくれました。Ｅｘｃｅｌ既存ＳＨＥＥＴの2列目から6列目に設定された書式や列幅・行の高さはそのまま生きていて、1列目と7列目に書かれている関数は値を返してくれます。

Answer 5

JackAmano さん、こんにちは。

マイクロソフト コミュニティ への投稿ありがとうございます。

Access 2003 の時に使っていた方法で Access 2010 からクエリの結果を既存の Excel ブックに書き出すと、意図しない結果になり困っているので情報を集めたい、という質問ですね。

Access 2003 ではどんな手順を使っていたのかをもう少し詳しく書き込んでみてはどうでしょうか。「コマンドレベルで実現していました」 の内容が詳しくわかるとよいと思います。

書き込んでもらった質問サイトの内容についてはやりとりの経緯がわからないため、このスレッドでベストアンサーの手順の画面遷移の情報を集めるのは難しいかもしれません。。。

返信、お待ちしています。