アクセスの外部データ(エクセル)の取り込み。VBA

このQ&Aのポイント
  • アクセスの外部データ(エクセル)をVBAで取り込む方法を教えてください。
  • アクセス2003とエクセル2003を使用しています。不要な行や列がある場合、VBAを使って外部データを取り込む方法を教えてください。
  • 現在、エクセルで行や列を削除してからデータを取り込んでいますが、VBAを使用して簡単に外部データを取り込む方法を知りたいです。
回答を見る
  • ベストアンサー

アクセスの外部データ(エクセル)の取り込み。VBA

アクセスの外部データ(エクセル)の取り込み。VBAの質問です。 アクセス2003 エクセル2003 不要な行・列がある場合の外部データ取り込みVBAを教えて下さい。 今は、エクセルで削除して取り込んでいます。 アクセスに「得意先リスト」というテーブルを作っています。 コード(テキスト型)主キー 名称(テキスト型) フリガナ(テキスト型) 郵便番号(テキスト型) 住所1(テキスト型) 住所2(テキスト型) TEL(テキスト型) FAX(テキスト型) エクセルブック[得意先リスト.xls] シートは「リスト形式」のみです。 1~4行は不要。 5行目が見出しです。 A~AZ列までデータがあります。 必要な列は、B・C・D・F・G・H・M・N列です。 セルの書式設定は「文字列」です。 [アクセス エクセル インポート 行 列 削除]などでサンプルVBAを探しましたが見つからなかったので質問させて頂きました。 申し訳ありませんが、教えて下さい。

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

  • ベストアンサー
  • nicotinism
  • ベストアンサー率70% (1019/1452)
回答No.2

手作業でやっていることを、そのまま置き換えるのが一番簡単そうです。 Access からオートメーションでExcelのBookを開き Access で取り出しやすいように整形してから取り込む。 こんな感じ ※MDB ファイルと XLS ファイルは同じフォルダにあると仮定しています。 Sub test()   Dim oXL As Object   Dim oBK As Object   Dim oSH As Object   Dim tmpXls As String   Set oXL = CreateObject("excel.application")   Set oBK = oXL.Workbooks.Open(CurrentProject.Path & "\得意先リスト.xls")   Set oSH = oBK.Sheets("リスト形式")   tmpXls = CurrentProject.Path & "\得意先リストtmp.xls"   With oSH    .Rows("1:4").Delete Shift:=xlUp    .Columns("O:AZ").Delete Shift:=xlToLeft    .Columns("I:L").Delete Shift:=xlToLeft    .Columns("E:E").Delete Shift:=xlToLeft    .Columns("A:A").Delete Shift:=xlToLeft   End With   If Dir(tmpXls) <> "" Then    Kill tmpXls   End If   oBK.saveas filename:=tmpXls   oBK.Close SaveChanges:=False   oXL.Quit   Set oXL = Nothing   'CurrentDb.Execute ("delete * from 得意先リスト")   '↑既存レコード削除ですので必要に応じて!!   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _   "得意先リスト", tmpXls, hasFieldNames:=True   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _   "得意先リスト参考までに", tmpXls, hasFieldNames:=True   MsgBox "終了" End Sub 参考になるかと思われるところ(私も以前どっぷりとハマリましたorz) http://hanatyan.sakura.ne.jp/vbhlp/ExcelErr.htm http://homepage1.nifty.com/rucio/main/technique/teq_15.htm

wai-girl
質問者

お礼

nicotinism様、 基本的な起動・終了処理のサイトのご紹介ありがとうございます。 作成して頂いたVBAにコメントを追加して使わせて頂きました。   Set oSH = oBK.Sheets("リスト形式")   tmpXls = CurrentProject.Path & "\得意先リストtmp.xls" リスト形式を開き、行・列の削除処理 得意先リストtmp.xlsに名前を変えて保存 得意先リストtmp.xlsをTransferSpreadsheetメソッドを使いインポートするのですね。 完成しました。ありがとうございました。

その他の回答 (1)

  • layy
  • ベストアンサー率23% (292/1222)
回答No.1

『エクセルのシート全体』を取りこむ、機能であって、融通は利かないでしょう。 『CSVファイル』は1レコード目、2レコード目や、各項目の定義はあるから対応可能。 案1) このままシート全体を取りこんで、 クエリ実行で、テーブルの先頭4レコードを削除する、フィールドを削除する。 案2) シートをCSV形式にしておいて、VBAでレコード、項目を判断して取り込む。 案3) エクセル側でエクスポート用の別シートを編集する

wai-girl
質問者

お礼

layy 様 アドバイスをありがとうございます。 クエリ実行で、テーブルの先頭4レコードを削除する、フィールドを削除する。 CSV形式にする。 という方法もありのですね。

関連するQ&A

  • EXCELの[データ]→[外部データの取り込み]→[新しいWebクエリ

    EXCELの[データ]→[外部データの取り込み]→[新しいWebクエリ]でテキストデータを取り込みたいのですが、ハイパーリンクなどのデータも含め、列に挿入されて取り込まれてしまい、取り込み先の関数データを記載されている列が右に異動されて反映されません。 取り込み先のEXCELの関数を反映させたまま目的のテキストデータを取り込むにはどのようにすればいいのでしょう。

  • エクセル2000 外部データの取り込み

    お世話になります。 これまで、エクセル2003を使用してアクセスのデータを「データ→外部データの取り込み」で取り込んでいました。 今回使用するエクセルが2000になったのですが、2000には「外部データの取り込み」がありません。 「保存したクエリの実行」では、基となるアクセスのデータを選択できません。 「テキストファイルのインポート」では、基となるアクセスのデータを選択できるのですが、2003とは手順が違う上、文字化けになってしまいました。 過去の質問も検索しましたが、解決できませんでした。 エクセル2000でも、2003のようにアクセスのデータを取り込めるのでしょうか? また、取り込めるとしたら、どのような手順を踏めばよろしいのでしょうか? ご回答のほど、よろしくお願いいたします。

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

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

  • Excelへのテキストデータの取り込み

    VB2005でテキストファイルを作成し、それをExcelに取り込もうとしています。 123,234,120,200 200,300,400,500 111,222,333,444 のようなファイルを作成し、これをExcelに取り込むと 3行で取り込まれます。 次に 123,234,120,200 200,300,400,500 111,222,333,444 のように文字列の行間に改行文字(chr$(13))だけの行を作成し、 これを取り込もうとすると、 2行おきに読み込まれてしまいます。 メモ帳でファイルの中身を確認すると1行おきになっているのに、 Excelのデータ→外部データの取り込みでテキストデータウィザードを見ると すでに2行おきになっているのです。 1行おきに作成した文字列ファイルを1行おきに取り込むにはどうしたらいいのでしょうか?

  • エクセルに外部データの取り込みができない

    エクセルの外部データ取り込みで、アクセスで作ったクエリを取り込んでいましたが、取り込みができないクエリがあります。 クエリ名を変えても、取り込みできるクエリと同じように作っても、 取り込めません。 出来るクエリとできないクエリは何が違うのかわかりません… 何か理由があるのでしょうか?

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

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

  • 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件挿入していくしか方法はないでしょうか? 今のところ、私が思い浮かぶのは上記の方法なのですが、何かもっと簡単にやれるような方法はあるでしょうか?よろしくお願いします。

  • 〔VBA・マクロ〕EXCELからACCESSデータをひっぱるには?

    当方のレベルはアクセス初級者(基本的な操作)、 エクセルはごく簡単なVBAをいじる中級者です。 アクセスにて顧客マスター、商品マスターを作成しています。 エクセルで納品書を作成しています。 現状では外部データの取り込みをつかって エクセルシートに顧客データ(シート1)と商品データ(シート2)を 取り込んだ後に、納品書(シート3)でシート1、2に VLOOKUP関数をかけて、納入先と商品情報を 伝票におとしこんでいます。 こんなまどろっこしいことをせずに、 エクセルからアクセスのテーブルへ直接データを取り込む方法は ないものでしょうか? どなたかよろしくお願いいたします。

  • VBAでの100万行以上のデータの取り込み

    どなたかご教示お願いいたします。 VBAで100万行以上のCSVデータの取り込みは可能でしょうか? 初心者なのでファイルを開いてセルに入れてから範囲を指定し配列に 取り込み処理しておりましたが100万行以上だとデータがシートから出てしまいます。 なのでエクセルに展開せずに配列に取り込むなどということはできるのでしょうか? もし可能であればあつかましいのですが、A列の120万行データを配列に取り込むサンプルをご教示いただけるとありがたいのですが・・・ よろしくお願いいたします。

  • AccessのクエリとExcelの外部データの取り込み機能について

    AccessのクエリとExcelの外部データの取り込み機能について 質問させてください。 Access初心者です。 クエリの抽出条件でnot likeを使うと Accessで実行したときには正常に動作するんですが、 Excelの外部データの取り込みのAccessデータベースを選択してExcelへ出力すると 正常に出力されません。 具体例ですが、 ---------------------------------- 住所フィールドには東京都の自治体の名前 個人フィールドには担当者の名前 ---------------------------------- が入ってるとします。 クエリの抽出条件は以下のような感じです。 ---------------------------------- 個人フィールド,住所フィールド 鈴木,not like "*八王子市*" 斉藤, 田中, ---------------------------------- そうするとAccessのクエリの実行結果では 鈴木さんの八王子市を抜いたデータ 斉藤さんの全データ 田中さんの全データ が出力されると思います。 しかし、Excelの外部データ取り込みを使うと 鈴木さんの全データ 斉藤さんの全データ 田中さんの全データ が出力されてしまいます。 どうすれば正常出力できるのでしょうか? Accessからコピー&ペーストしてExcelに貼り付けるしかないのでしょうか? (参考) ・使っているOfficeは2007SP2です。