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

質問

質問します。
Access初心者、VBA初中級者です。
VBAで、ExcelからAccessにデータを取り込んでいます。

ですが、Excelの表はAccessのDBの型と一致していません。取り込む場合、特定の列のデータをAccessの特定のフィールドに取り込むことはできますか？
（たとえば、ExcelのA列のデータは、Accessの「品番」フィールドに入れる、B列のデータはAccessの「商品名」フィールドに入れる、というように。）
一時エリアに保存して、それを取り込む方法でもいいです。

現在TransferSpreadsheetを使っていますが、Rangeを指定して一度に全部もってきたいわけではないので、できれば取り込み先を設定したいと思った次第です。

また、Excelは使用行が決まっていないため、取り込みも、できれば
「カラ行になるまで1行ずつ取り込む」というようなコードにしたいです。

お手数をおかけしますが宜しくお願いします。

回答No.2

30246kiku · ベストアンサー

＃１です

> 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する、とかできるのでしょうか。

甘えてしまってすみません。
どうぞよろしくお願いします。（＞＜）

回答No.3

30246kiku

＃２です

> こういう場合はどうしたらよいのでしょうか。

もっといい方法があると思いますが、私はタスクマネージャから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さま
ご回答ありがとうございました！
やはりタスクマネージャが早いですよね。。

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

回答No.1

30246kiku

処理は遅くなると思いますが、以下雰囲気で（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

※ エラー処理は省いてます。

※ ★では２行目から取り込みを設定

※ 空白行の判別は、正式な記述に

※ シートが複数あって選択する必要があるのなら、追加してください。

お礼

＞30246kiku様
ありがとうございます。
参考に使用してみたところ、

rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

のところで、
エラー「SQLステートメントが正しくありません。'DELETE','INSERT','PROCEDURE','SELECT',または'UPDATE'を使用してください」
が出てしまいました。

おそらくINSERTをしなければいけないのだと思いますが、
どこにどのように記述すればよいのでしょうか？
お手数おかけしますがお教え下さいますか（＞＜）

補足

たびたびすみません。
その後少しいじっていたら、出力されるようになりました！
ありがとうございます。

ちなみに、DBへの書き込みのロジック（？）が微妙にわかっていません。

re.AddNew

の部分が、書き込みますよ、という意味なのでしょうか？
ここがわからないので、先程のように「SQLでINSERTしなきゃいけないんじゃないのか？」と思ってしまいました。