excelファイルからaccessへのデータ移管

質問

Accessのフィールド名がＡ、Ｂ、Ｃ、Ｄ、Ｅ、Ｆ、ＧとなっているものにExcelのヘッダがＡ、Ｂ、Ｃ、Ｄ、Ｅ、Ｅ、Ｇとなっている物を強制的に読み込ませたいです。Excel側のデータは弄る事が出来ません。Access側を弄って読み込ませる方法はありませんか？

回答No.5

piroin654 · ベストアンサー

たとえばエクセルのデータがこのようなもの

会員番号　　氏名　　職業　　　年齢　性別　　住所　　年齢
236　　　豊臣秀吉　会社経営　　52　　男　　大阪　　15
269　　　徳川家康　会社経営　　48　　男　　東京　　20
759　　　織田信長　造園業　　　35　　男　　名古屋　12

そしてAccessのテーブルが
ID 　名前　　住所　生業　性別　年齢　年齢2

とします。
エクセルの場合はシート上に設定したヘッダに重複があっても
問題はありませんが、Accessではフィールドの名前の重複は
エラーになります。したがって上記のようにエクセルでは
年齢が二つありますが、アクセスでは同じ名前のフィールド名が
設定できないので、年齢と年齢2というフィールドで設定しています。
もしエクセルに重複したヘッダがあるならばアクセス側では
別名にする必要があります。そのうえで、同じフォルダにファイルが
あるとしてエクセルの標準モジュールに以下のように.
OfficeのバージョンによってはADOをつかったほうがいい場合もあります。
そのあたりは現状に合わせてください。また参照設定も実情に合わせて。
古いバージョンでのコードなのでそのあたりはご勘弁を。

Sub test()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPath As String
Dim i As Long

strPath = ThisWorkbook.Path & "\sample1.accdb"

Set db = OpenDatabase(strPath)
Set rs = db.OpenRecordset("アクセステーブル名")

With Sheets("Sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
rs.AddNew

rs.Fields("ID") = .Cells(i, 1)
rs.Fields("名前") = .Cells(i, 2)
rs.Fields("住所") = .Cells(i, 6)
rs.Fields("生業") = .Cells(i, 3)
rs.Fields("性別") = .Cells(i, 5)
rs.Fields("年齢") = .Cells(i, 4)
rs.Fields("年齢2") = .Cells(i, 7)

rs.Update
Next i
End With

rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

このようにするとエクセルでどのようなヘッダが設定されて
いてもアクセス側でテーブルのフィールド名を重複なく
設定できればエクセルのデータをデータ型のエラーが
起こらなければ任意のエクセルの列データを任意のアクセスの
テーブルのフィールドに入れることができます。

お礼

ありがとうございます。この方法でできました。
他の方々も教えていただきありがとうございました。

回答No.6

chayamati

回答№3に誤りがありました。申し訳ございません。
　✖　因みに、Excelでもテーブルが使えますが列名変更されます
　◎　因みに、Excelでもテーブルが使えますが
　　　【テーブルとして書式設定】は聞きません
★【シート名右クリック】→【移動またはコピー】
　→【コピーを作成する】でSheetをコピーし、
　ユニークな項目名に書き換えて保存
　このSheetをAccessでインポートする

回答No.4

m3_maki

最初に思いつく方法は、エクセルシートからリンクテーブルを作り
リンクテーブルのデータを 追加クエリで目的のテーブルに追加する、
というところでしょうか。

NSERT INTO アクセステーブル ( A, B, C, D, E, F, G )
SELECT A, B, C, D, E, E1, G
FROM リンクテーブルデータ;

という感じのクエリになります。

回答No.3

chayamati

＞同じものが２つある為
★Accessのフィールド名がユニークであるは必要な要件です
　Excel側で対応することになります
　因みに、Excelでもテーブルが使えますが列名変更されます

回答No.2

chayamati

【外部データ】→【新しいﾃﾞｰﾀｿｰｽ】→【ファイルから】
→【Excel】と進んで【参照】からExcelファイルを選びます。添付図
この後Sheet選択に進みます
--------------------------------------------
結果はsheet名がテーブル名、1行目の項目名がフィールド名になります

補足

回答ありがとうございます。
その部分はクリアしています。質問が分かりにくくてすみません。
Access、Excelのフィールド名が違うため、テーブルに追加しようとするとエラーになってしまいます。Excelのフィールド名には同じものが２つある為、それにAccessのフィールド名を合わせる事もできません。
VBAでもかまいません。なにかいい方法はありませんでしょうか？

回答No.1

tamu1129

ありますよ

外部データ→（インポートとリンク）の部分にあるExcelマーククリックして対象になるエクセルファイル選択すればインポートウィザードになるので取り込みすればいいです