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

質問

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

アクセス2003　エクセル2003

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

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

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

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

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

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

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

回答No.2

nicotinism · ベストアンサー

手作業でやっていることを、そのまま置き換えるのが一番簡単そうです。
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

お礼

nicotinism様、

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

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

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

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

回答No.1

layy

『エクセルのシート全体』を取りこむ、機能であって、融通は利かないでしょう。
『ＣＳＶファイル』は１レコード目、２レコード目や、各項目の定義はあるから対応可能。

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

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

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

お礼

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

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