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

アクセスの外部データ（エクセル）の取り込み。VBAの質問です。

アクセス2003　エクセル2003

不要な行・列がある場合の外部データ取り込みVBAを教えて下さい。
今は、エクセルで削除して取り込んでいます。

アクセスに「得意先リスト」というテーブルを作っています。

コード（テキスト型）主キー
名称（テキスト型）
フリガナ（テキスト型）
郵便番号（テキスト型）
住所１（テキスト型）
住所２（テキスト型）
TEL（テキスト型）
FAX（テキスト型）

エクセルブック[得意先リスト.xls]
シートは「リスト形式」のみです。

１～４行は不要。
5行目が見出しです。

A～AZ列までデータがあります。
必要な列は、B・C・D・F・G・H・M・N列です。
セルの書式設定は「文字列」です。

[アクセス　エクセル　インポート　行　列　削除]などでサンプルVBAを探しましたが見つからなかったので質問させて頂きました。
申し訳ありませんが、教えて下さい。

No.2 ベストアンサー 回答者： nicotinism 回答日時： 2011/02/11 10:01

手作業でやっていることを、そのまま置き換えるのが一番簡単そうです。

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/ …

この回答へのお礼

nicotinism様、

基本的な起動・終了処理のサイトのご紹介ありがとうございます。
作成して頂いたVBAにコメントを追加して使わせて頂きました。

　　Set oSH = oBK.Sheets("リスト形式")
　　tmpXls = CurrentProject.Path & "\得意先リストtmp.xls"

リスト形式を開き、行・列の削除処理
得意先リストtmp.xlsに名前を変えて保存
得意先リストtmp.xlsをTransferSpreadsheetメソッドを使いインポートするのですね。

完成しました。ありがとうございました。

お礼日時：2011/02/11 16:56

No.1 回答者： layy 回答日時： 2011/02/10 21:22

『エクセルのシート全体』を取りこむ、機能であって、融通は利かないでしょう。

『ＣＳＶファイル』は１レコード目、２レコード目や、各項目の定義はあるから対応可能。


案１）
このままシート全体を取りこんで、
クエリ実行で、テーブルの先頭４レコードを削除する、フィールドを削除する。

案２）
シートをＣＳＶ形式にしておいて、ＶＢＡでレコード、項目を判断して取り込む。

案３）
エクセル側でエクスポート用の別シートを編集する

この回答へのお礼

layy 様
アドバイスをありがとうございます。

クエリ実行で、テーブルの先頭４レコードを削除する、フィールドを削除する。
ＣＳＶ形式にする。
という方法もありのですね。

お礼日時：2011/02/11 16:55