Excelから一つのテーブルにインポートしたい

質問

こんにちは。

ExcelではVBAが使えるレベルですが、Access初心者です。

エクセルのワークブックにデータがA～N列までデータが入っています。
１行目はタイトル(フィールド名)で２行目以下がデータ(レコード)になります。
ワークブックの中のシート数はさまざまです。
１行目のタイトル(フィールド名)はあってもデータがないものもありますし、
２行目以下のデータ(レコード)数もさまざまです。

ワークブック(とその中のシート)が多いので
VBAを使ってAccessに取り込みたいと思っています。

いろいろネットで検索して以下のVBAまでたどりついたのですが、
それぞれ「ワークブック名_シート名」というテーブルに取り込まれます。

これを例えば「総合」というようなテーブル一つに取り込むにはどうしたらいいでしょうか？

週末をつぶして試行錯誤しましたが、どうしても解決しないのでアドバイスいただけるとうれしいです。

Sub ImportFromExcel()

Dim tgtXLname As String
Dim tgtXLpath As String
Dim newTBLname As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim sCnt As Integer
Dim n As Integer

Application.Echo False

TargetFolder = "C:\work"

tgtXLname = Dir(TargetFolder & "\*.xls")
Do While tgtXLname <> ""
　　tgtXLpath = TargetFolder(a) & "\" & tgtXLname

　　Set xlApp = GetObject(, "Excel.Application")
　　xlApp.Application.Visible = True

　　xlApp.Workbooks.Open tgtXLpath
　　Set xlBook = xlApp.ActiveWorkbook

　　sCnt = xlBook.Worksheets.Count
　　For n = 1 To sCnt
　　　　Set xlSheet = xlBook.Worksheets(n)
　　　　newTBLname = Left(tgtXLname, Len(tgtXLname) - 4) & "_" & xlSheet.Name
　　　　DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, newTBLname, tgtXLpath, True, xlSheet.Name & "!" & "A:N"
　　　　Set xlSheet = Nothing
　　Next

　　xlBook.Close SaveChanges:=False
　　Set xlBook = Nothing
　　tgtXLname = Dir()
Loop

xlApp.Application.Quit
Set xlApp = Nothing

Application.Echo True

MsgBox "インポート終了"

End Sub

回答No.2

samtomsan · ベストアンサー

すみません、私も実際に動かしたわけではないので。
下記によると追記はできないようですが
http://okwave.jp/qa/q1706501.html
下記ではできるようです。
http://www.accessclub.jp/bbs3/0449/superbeg131890-2.html

ちょっと気になるのは「xlSheet.Name」ですが、これでどのシートか判りますか。
作業中のシート名ではないでしょうか。
先の二番目の例では
strrange = Format$(i, "00") & "!A4:E3000"
のようにシート番号を指定しています。

お礼

再度の回答ありがとうございます。

私自身がAccessのVBAはまだ勉強したてですので、
ご紹介いただいたVBAのコードを参考にしていきたいと思います。

レコードの追加が難しければ、シートごとにインポートしたテーブルを
一つにまとめるVBAを考えてみようと思います。

補足

とりあえずappendは難しそうなので、クエリを使って結合する方向で考えていきたいと思います。

解決の糸口がみつかり助かりました。ありがとうございました。

回答No.1

samtomsan

確認はしてありませんが、
newTBLname を For の前に出し
newTBLname = "総合"
でできませんか。

お礼

回答ありがとうございました。

私も同じ方法を試してみましたが、ダメでした。

追加してインポートされるのではなく、最後にExcelからインポートした(と思われる)データ(レコード)だけでした。