• ベストアンサー
  • 困ってます

ExcelからAccessにデータを取り込む方法

質問します。 Access初心者、VBA初中級者です。 VBAで、ExcelからAccessにデータを取り込んでいます。 ですが、Excelの表はAccessのDBの型と一致していません。取り込む場合、特定の列のデータをAccessの特定のフィールドに取り込むことはできますか? (たとえば、ExcelのA列のデータは、Accessの「品番」フィールドに入れる、B列のデータはAccessの「商品名」フィールドに入れる、というように。) 一時エリアに保存して、それを取り込む方法でもいいです。 現在TransferSpreadsheetを使っていますが、Rangeを指定して一度に全部もってきたいわけではないので、できれば取り込み先を設定したいと思った次第です。 また、Excelは使用行が決まっていないため、取り込みも、できれば 「カラ行になるまで1行ずつ取り込む」というようなコードにしたいです。 お手数をおかけしますが宜しくお願いします。

noname#105246

共感・応援の気持ちを伝えよう!

  • 回答数3
  • 閲覧数4471
  • ありがとう数3

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

  • ベストアンサー
  • 回答No.2

#1です > rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic ここの部分は、ただ単にテーブルをオープンしたでけになります。 その後、rs.MoveNext 等で、処理対象のレコードに移動したりします。 で、 >   rs.AddNew は、新規レコードを追加しますよ、っていうものになります。 >  rs("品番") = oApp.Cells(iRow, 1) >  rs("商品名") = oApp.Cells(iRow, 2)   でフィールドに値を代入(設定)し、 > rs.Update で、登録しています。 処理の流れで、rs.AddNew しないでフィールドに値を代入し rs.Update すると これは、更新、になります。(ADOの場合) DAOの場合は、rs.Edit を記述し、これから更新しますよ、って指定します。 更新されるものは、現在参照できているレコードになります。 レコードセットを操作して、更新/削除/新規登録する場合は、 テーブルをオープンする際に、INSERT 等は指定する必要がありません。 SQLを発行して完結するもの(INSERT,DELETE,UPDATEなど)は、 一般的にアクションクエリとか呼ばれています。(クエリでの話ですが) これらを実行する時には、レコードセットをオープンする操作ではなく Execute するもので実行します。 例えば "DELETE * FROM テーブルA" を実行する時には、ADOでは CurrentProject.Connection.Execute "DELETE * FROM テーブルA" とかにします。 話がずれていっているような気がしてきましたが、わかる内容になって?ました?、、、

共感・感謝の気持ちを伝えよう!

質問者からのお礼

>30246kikuさま おそくなりました!ありがとうございます! まさにそのようなことが知りたかったのです。 本当に勉強になりました。ありがとうございます。 最後に一つお聞きしてもよいでしょうか(><) 色々、修正中に途中でバグったりすると、ExcelがOpenになったままになるらしく、 対象のExcelが編集中になったり、Excelが(手動で)開けなくなったりしてしまいます。 こういう場合はどうしたらよいのでしょうか。 Openしたら変数かなにかにExcelの内容を一括コピーして元のファイルはさっさとCloseする、とかできるのでしょうか。 甘えてしまってすみません。 どうぞよろしくお願いします。(><)

関連するQ&A

  • ExcelデータをAccessに取得させる方法

           エクセル           Access    A列   B列  C列      フェールド名  人 りんご みかん ばなな キャベツ    リンゴ  3   木村               木村  3    2    みかん  2   木村             小林             4    ばなな  4  小林               青木                   2    キャベツ 2  青木 のようにエクセルのデータでA列をAccessのフィールド名にし、もし無ければ、その項目名(りんご等)をフィールド名に追加させ、上の右図のようにAccessにデータを取得させるコードを作りたいと考えてます。  取得させる場合はExcellではなく、Access VBAで取得させたいです。  どんなコードを書いたらよろしいのですか?  分かる方がおられましたら、よろしくご指導くだされとうれしいです。  私のVBAのレベルですが、Access VBAは今回初めてで、Excel VBAは中級くらいかと思います。  よろしくお願いします。

  • Excel VBA Accessでデータ取り込み

    Excel VBA、Access VBAについてお教え下さい。 「My_Excel.xlsm」というファイルがあります。このファイルにはボタンが1つあります。 このボタンを押すことにより、「Imp_Excel.xlsx」というファイルを取り込みたいと思います。 取り込み先ですが、「My_Excel.xlsm」に取込むのではなく、Accessの「My_Access.accdb」の「T_MyTable」というテーブルに取り込みたいと思っています。 Accessから直に「Imp_Excel.xlsx」をインポートするには、TransferSpreadsheetを用いれば比較的簡単にデータをAccessに取り込むことができるのですが、「My_Excel.xlsm」をまたいでAccessに取り込みたいので、TransferSpreadsheetは使えません。 やり方としては、 My_Excel.xlsxを開く。 レコードセット取得。 レコードセットがEOFになるまでまわして、SQLのINSERT、またはAddNewメソッドを使用して1件1件挿入していくしか方法はないでしょうか? 今のところ、私が思い浮かぶのは上記の方法なのですが、何かもっと簡単にやれるような方法はあるでしょうか?よろしくお願いします。

  • ExcelかAccessで二つのデータが合っているかどうかを調べる方法を教えてください。

    二つのセル(フィールド)を比較して、そのデータが同じか違うか結果を別のセルに返したいのですが、そんなことって可能だったでしょうか。 データはA列とB列にほぼ同じデータがあり、その中の完全に一致してないデータだけを抽出したいのです。 行は約1500あります。 Access、Excelのどちらでも構わないのですが、方法をご存知の方がいらっしゃいましたら教えてください。

その他の回答 (2)

  • 回答No.3

#2です > こういう場合はどうしたらよいのでしょうか。 もっといい方法があると思いますが、私はタスクマネージャからExcelを終了させてます。 VBAでやろうとすると、起動しているExcelは以下の方法で得ることができます。   Dim oApp As Object   On Error Resume Next   Set oApp = GetObject(, "Excel.Application")   If (Not oApp Is Nothing) Then     ' ここで、ワークブックをループするなりして閉じるとか     '   End If ただ、Excel起動中に   Set oApp = CreateObject("Excel.Application") でExcel起動すると、Excelが複数立ち上がることになります。 (タスクマネージャで見るとわかると思います) なので、   Set oApp = GetObject(, "Excel.Application") で処理するのもループさせ、Excelが無くなるまで処理するのでしょうか。 ※確認作業中はファイルが壊れてもいいようにコピーは取っておきます。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

>30246kikuさま ご回答ありがとうございました! やはりタスクマネージャが早いですよね。。 使う人が素人なのと、タスクマネージャに対象のものが乗ってこないのが気になるのですが とりあえずこちらに関しては、バグをださないこととOSの機能でなんとかしつつ、改善策を探そうと思います。 ありがとうございました!

  • 回答No.1

処理は遅くなると思いますが、以下雰囲気で(ADOでの例) インポートしたいエクセルファイルが、mdb と同じフォルダに、 hogehoge.xls 名であったとします。(シート1枚) Dim rs As New ADODB.Recordset Dim oApp As Object Dim iRow As Long Set oApp = CreateObject("Excel.Application") oApp.Workbooks.Open Filename:=CurrentProject.Path & "\hogehoge.xls" rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic iRow = 2 ' ★ While( 空白行の判別 )   rs.AddNew   rs("品番") = oApp.Cells(iRow, 1)   rs("商品名") = oApp.Cells(iRow, 2)   rs.Update   iRow = iRow + 1 Wend rs.Close oApp.Quit Set oApp = Nothing ※ エラー処理は省いてます。 ※ ★では2行目から取り込みを設定 ※ 空白行の判別は、正式な記述に ※ シートが複数あって選択する必要があるのなら、追加してください。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

>30246kiku様 ありがとうございます。 参考に使用してみたところ、 rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic のところで、 エラー「SQLステートメントが正しくありません。'DELETE','INSERT','PROCEDURE','SELECT',または'UPDATE'を使用してください」 が出てしまいました。 おそらくINSERTをしなければいけないのだと思いますが、 どこにどのように記述すればよいのでしょうか? お手数おかけしますがお教え下さいますか(><)

質問者からの補足

たびたびすみません。 その後少しいじっていたら、出力されるようになりました! ありがとうございます。 ちなみに、DBへの書き込みのロジック(?)が微妙にわかっていません。 re.AddNew の部分が、書き込みますよ、という意味なのでしょうか? ここがわからないので、先程のように「SQLでINSERTしなきゃいけないんじゃないのか?」と思ってしまいました。

関連するQ&A

  • access→excel2007エクスポート

    独学でaccess使用しています accessVBA でクエリ結果をexcel(xlsx形式)にエクスポートしようとしています。 DoCmd.TransferSpreadsheet acSpreadsheetTypeExcel12Xml … を使用していますが指定したテーブル(クエリ)の中にリストボックス(複数値可)形式のフィールドがあるとそのフィールドの値がエクスポートしたexcel上で文字化けしてしまいます。 何か設定(構文)の間違いがあるんでしょうか? ご教授お願いします。

  • ACCESSのDBをEXCELデータで更新する方法

    毎月、定型のEXCELデータが作成されます。 A列は部門A、B列は部門B、C列は部門Cです。(この部門名が100個あります) もし、その月に部門で退職者がいれば、人数の数値が入力されます。 (つまり 先月、部門Aに1名発生していれば、先月のデータは「A列に1がたち、B列とC列は0です」 今月、部門AとBに1名ずつ発生していれば、今月のデータは「A列に1が立ち、B列に1が立ち、C列は0です」) これらのデータをACCESSで管理していきたいと思います。 取り込むEXCELは定型フォーマットなので、ACCESSのテーブルは1つのレイアウトを準備予定です。 基本的なことなのですが、エクセルを取り込んで、テーブルに追加クエリ?(上のケースでいえば、今月分を取り込んだとき、A部門のフィールドについては先月に追加した「1」に今月新たに「1」を追加して2に変更したい)をするためには、どういう対応をすればよいのか、参考書を読んでもよく分かりません。 項目が100個あるためフォームを使って個別に更新するのは難しいです。 VBAなどを組まないといけないのでしょうか。 参考になる方法やヒントをおしえて頂ければ、その上で、参考書をもう一度見ていきたいと思います。 基本的なところからで申し訳ありませんが、詳しい方、お力をお貸しください。。。

  • AccessVBA Excelエクスポートについて

    AccessVBA Excelエクスポートで質問です。(DoCmd.TransferSpreadsheet acExport) 既存Excelファイルに名前定義し、そこにVBAを用いてエクスポートをしたいのですが、 名前定義したところにエクスポートされません。 ※PC環境:Win7 Access2007 Excel2007 ※出力されるデータ行は、抽出する際の条件により変動あり <Excel側で設定している名前定義> 名前:Data、範囲:ブック、参照範囲:データ!$B$5:$M$5 <Access VBAで書き込みしている内容> DoCmd.TransferSpreadsheet acExport, 8, "テーブル名","ファイル名", False,"Data" <Access側で実行し、実際にExcelにエクスポートされる1行目の位置> 名前定義したところからエクスポートされず、 名前定義をしているシート、列にはエクスポートされるのですが、データ1行目が名前定義していない箇所で、 シート名:データ B1:M1 が1行目にエクスポートされてしまいます。 今まで作っていたVBAでは、問題なくエクスポートできていたのですが、 現在作成しているものでは、なぜかできなくなってしまいました。 今までが運良く動いていただけでしょうか?→誤っている箇所を教えて下さい。 正しくVBAや名前定義ができているとして、このような現象を回避する術はありますでしょうか? おわかりになる方、どうぞご教示いただけるようよろしくお願い致します。

  • accessから、excelへ外部データを取り込んだのですが・・・。

    お世話になります。 accessのデータベースをエクセルのワークシートへ、外部データの取り込みを用いて取り込んだのですが、おかしな状況が起こってしまいました。 取り込んだデータの後ろに、ついていないはずの”・・・・・”という文字列が勝手についてしまっているのです。(ex、”東京都”というアクセスのデータをexcelにとりこんだら”東京都・・・・・”という文字列に勝手になってしまっている) この現象はいったいなぜなのでしょうか?またこの現象を発生させないためにはどうしたらよいにでしょうか? 分かりにくい質問内容でごめんなさい。 どなたか御指導よろしくお願いいたします。

  • Access データを複数行に貼り付ける方法

    Access2013 テーブルの フィールドで 1つのセルのデータ(例/商品名)をコピーし、 同じフィールドの連続した複数行に貼り付ける方法を教えて下さい。 Excelの様にやってみたのですが出来なかったので。 宜しくお願いします。

  • AccessでExcelデータをインポート

    お世話になります。  Access2003/2010  Excel2003/2010 10年程前より、Access2003で作成したツール(Excel2003形式のファイルを インポート)を利用しております。 Excelファイルは毎日追加/更新しているのですが、先日、とうとうExcel2003 の行の上限である65,536行を超えてしまいました。 今後はExcel2010形式のファイル(行の上限は1,048,546行)とし、かつ、 2010形式のファイルをインポートできるようツールの修正をしなければならなく なりました。 ツールの修正というよりは、今までAccess2003上で使用していたので、そのまま Access2010上で使用したいだけなので、修正が必要がどうかも分からない 状況です。 そこで2点質問ですが、  1.現状インポートする際は、   DoCmd.TransferSpreadsheet acImport, 8, "temp", strFile, True   としておりますが、「8」の箇所は2010形式のExcelをインポートする場合   どのように修正すればよいでしょうか。   ヘルプを見ても8(Excel2000形式)までの記載しかありません。   とりあえず8のままExce2010形式で、かつ2003の行の上限を超えている   ファイルを(66000行)インポートしてみましたが、行は削れることなく   インポートできました。  2.インポート時の文字数制限について   インポートするExcelファイルの「備考」フィールドは文字数が多いのですが、   Access2003上で使用すると全ての文字列(750文字程度)をインポート   できておりました。   しかしAccess2010上で使用すると、文字列が途中で切れる(255文字)   不具合が発生しました   インポートする際は、あらかじめテーブルを作成しておき、そこに対して   Excelのデータをインポートしております。なお、「備考」のデータ型は   メモ型です。   (255文字で切れることから)恐らくAccessがインポートする時点で「備考は   テキスト型」と判断しているから255で切れてしまうのではないか、で、切れた   文字列をメモ型に入れてるだけのように見受けられるのですが。。   2003から仕様が変わってしまったのでしょうか。   上記のことから、インポートするExcelの先頭行の「備考」に、適当な文字列   (750文字)を入力し、それをインポートすると途切れることなくインポート   できました。   ※Accessがメモ型と認識したから? ※いずれもツールをAccess2010上で使用した状況です。 ご教示の程、宜しくお願い致します。

  • AccessからExcelのパスワードを解除

    いつもお世話になっております。 結構切羽詰っている状況での質問です。 AccessからExcelのデータを取込むように VBAで作成しておりましたら、 困ったところで引っかかってしまいました。 Excelを開いている時はならないのですが、 そのExcelファイルそのものにパスワードがかかっている為、 直接Accessから開くと、エラーになってしまいます。 Access側からVBAを使って、パスワード解除は出来ませんでしょうか? どなたか教えて下さいm(_ _)m

  • ACCESS-VBAでEXCEL出力

    ACCESS-VBAで外部accdb内のテーブルデータをEXCEL出力するにはどうしたらできるのでしょうか? 現在、以下の方法で内部のテーブルデータをEXCEL内の名前に紐づけてエクスポートしています。 DoCmd.TransferSpreadsheet acExport, , "データ", "test.xlsx", True

  • Accessに取り込むデータの置き場

    Accessが社内LAN内の共有フォルダに置かれています。 このAccessにExcel(数百個)のデータを取り込みたいです。 この場合にExcelはどこに置けばいいでしょうか? シンプルに、Accessと同じLAN内の共有フォルダに置けば、取り込みはできるのですが、 不注意でExcelを削除・移動されないか、運用面で心配です。 できればShare Point OnlineのようなクラウドにExcelを置きたいのですが、 Accessから繋ぐ方法がわかりません。 もしかしてAccessをどこかのクラウドに置き、Excelも同じくクラウドに置いて、 VBAを使って自動で取り込めたりするでしょうか? よろしくお願いします。 Access、ExcelはOffice365使ってます。

  • AccessでクエリしたデータをExcelに貼り付け

    こんにちは。 Accessでクエリをして抽出したデータをExcelに貼り付けるにはどのような方法があるでしょうか? VBAを使用しないといけないのか、Access側でそのような機能があるのか。 現在は抽出したデータをレポートで印刷しています。 このデータをExcelに貼り付けて電子データとして残したいのです。 宜しくお願いします。