エクセルデータをアクセステーブルにコピーするVBA

質問

エクセルファイル　E.xlsxにおいて
セルA1＝ID　A2＝氏名　A3＝性別　A4＝住所
というデータがあるとしまして
これをアクセスファイル　F.accdbにおける
テーブルの　table1
その項目が　ID,　氏名,　性別,　住所,　卒業校,　旧住所
があります。
このエクセルファイル　E.xlsxにおける
セルA1＝ID　A2＝氏名　A3＝性別　A4＝住所
というデータを上記F.accdbにおける
テーブルの　table1
その項目が　ID,　氏名,　性別,　住所,に（卒業校,　旧住所は　新規入力はないこととなります）
コピー　追加するVBAを御教示願えますか
E.xlsxにおいては１行だけのデータですが、
table1には　すでに数行のデータが入力済であり、
IDが新規の場合と、すでにtable1に登録済みのIDが存在する場合に
上書きする場合のそれぞれのVBAを
御教示くださりますと助かる次第です
よろしくお願い致します

win10 office365

回答No.7

chayamati

回答№３,４,５,６です。
回答№６では解説を省きましたが
　W_名簿はエクセルファイルをインポートしたテーブルですが
　日付、テキスト等自由に書き込みが出来ますが、
アクセスでは各項目毎に定義します
　M_名簿
　・登録日：主キー　日付/時刻型
　・担当者：主キー　テキスト型　S_担当者をルックアップ
　・氏名　：主キー　テキスト型　インデクスで重複なし
　　　　　　システム全体で重複なし
　・性別　：規定値＝男　ルックアップでリスト（男,女）
　・住所　；値要求　テキスト型
　・卒業校：テキスト型　S_学校をルックアップ
　・旧住所：テキスト型
以上、最初の入力窓口がエクセルと云う事でデザインしてみましたが
運用上不都合がありそうでお薦め出来ません

回答No.6

chayamati

回答№３,４,５です。
　名簿システムとして一応完成しました
　詳しい説明を省くため添付図を不明な質問は補足でお願いします
　図中、左でモジュールの一覧があります
　テーブル名の先頭の記号は
　　M_:マスタテーブル
　　S_:参照テーブル
　　T_トランザクションテーブル
　　W_ワーキングテーブル

　質問にIDがありますが、値はどのように管理されてますか
　担当者と登録日と氏名名を主キーにすることによりIDは不要です
　削除します
　添付図右側は名簿システムを開くと最初に表示されます。
　ここにはテキストボックス、コンボボックス、ラベルボックスが配置しています。
　これらのボックスのイベントからVBAが起動します
　以下は目次のVBAです
---------------
Option Compare Database
Option Explicit

Private Sub M_名簿登録_Click()
DoCmd.OpenForm "M_名簿登録"
End Sub

Private Sub W_名簿登載_Click()
DoCmd.OpenForm "W_名簿登録", , , "担当者 = Forms!目次!担当者"
End Sub

Private Sub M_名簿リスト_Click()
DoCmd.OpenReport "M_名簿リスト", acViewPreview
End Sub

Private Sub 終了_Click()
DoCmd.Quit
End Sub

Private Sub 担当者_DblClick(Cancel As Integer)
DoCmd.RunSQL ("delete from M_名簿 where 登録日 = forms!目次!登録日 and 担当者 = forms!目次!担当者 ;")
DoCmd.RunSQL ("insert into M_名簿(登録日,担当者,氏名,性別,住所,卒業校,旧住所) select 登録日,担当者,氏名,性別,住所,卒業校,旧住所 from W_名簿 ;")
End Sub

Private Sub 閉じる_Click()
DoCmd.Close
End Sub
------------------------------------------------------------------
Private Sub 担当者_DblClick(Cancel As Integer)で
担当者単位のW_名簿よりM_名簿へ更新及び挿入がされます

回答No.5

chayamati

回答№３，４です。
如何ですか？
VBAでは解決出来そうに無いですね
複数人でエクセルファイルへ入力しているなら
入力者と登録日の列を追加して、W_テーブルへインポートして
これをテーブル1にインサートします
1.インサート手順、添付図をご覧ください。
　《外部データ》《新しいデータソース》《ファイルから》ここで画面がかわります　
　《参照》《エクセルファイル》《OK》《sheet名》、《この手順を保存をする手順が最後
2.インポートVBA
2-1　テーブルデザイン
　・ID
　・入力担当：主キー
　・登録日　：主キー
　・氏名
　・性別；
　・住所：
　・卒業校
　・旧住所
2.1　目次フォーム作成
　・基準日；テキストボックス 日付/時刻　規定値=date()
　・入力担当：テキストボックス 非連結
　イベントからVBAへ
Private Sub 入力担当_Exit(Cancel As Integer)
DoCmd.RunSQL ("delete from テーブル1 where 登録日=基準日 and 担当者=入力担当;")
DoCmd.RunSQL ("insert into テーブル1 (担当者,登録日,氏名,性別,住所,卒業校,旧住所) select 入力担当,登録日,氏名,性別,住所,卒業校,旧住所 from W_テーブル1 ;")
DoCmd.OpenReport "R_テーブル1", acPreview
End Sub

解説：入力担当と登録日を主キーにする事でレコードがユニークになり、新規のレコードだけが挿入される
　　　delete from テーブル1 where 登録日=基準日 and 担当者=入力担当で該当レコードが削除され最新のエクセルファイル
　　　レコードが挿入されるため、挿入及び更新になる

お礼

詳細なご解説有り難うございました。活用させて頂きます

Powered by GRATICA

回答No.4

chayamati

コピー　追加するVBAを御教示願えますか
E.xlsxにおいては１行だけのデータですが、
table1には　すでに数行のデータが入力済であり、
IDが新規の場合と、すでにtable1に登録済みのIDが存在する場合に
上書きする場合のそれぞれのVBAを
-----------------------------------------------------------
IDの頭にに??マークが付いていると主キーと呼び重複は許されません
数行のデータはどのようにして入力しましたか
　1.テーブルに直接入力
　2.table1をレコードソースとしたフォームに入力
　どちらでもよいですが入力済みのレコードと同じものを追加入力出来ますか
　IDが主キー（🔑マーク）なら出来ません
　またデータ型が日付/時刻型の項目に文字列、数値は入力できません
　このようにあなたがやりたいVBAでは解決できません

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.3

chayamati

＞エクセルファイル　E.xlsxにおいて
　セルA1＝ID　A2＝氏名　A3＝性別　A4＝住所というデータがあるとしまして
--------------------------------------------
　エクセルファイルをアクセスのテーブルにインポートするには
　先頭行が項目名でその下に情報行である必要があります。
従って以下のように訂正します

　セルA1＝"ID" B1＝"氏名"C1＝"性別"D1＝"住所"とします
詳しくは以下のURAを参照ください
https://support.microsoft.com/ja-jp/office/excel-%E3%81%8B%E3%82%89-access-%E3%81%AB%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E7%A7%BB%E5%8B%95%E3%81%99%E3%82%8B-90c35a40-bcc3-46d9-aa7f-4106f78850b4?nochrome

回答No.2

kkkkkm

No.1のコードで
IDは数値型と考えています。
テキスト型の場合は
values (" & XlsxData(1, 1) & "," & _
を
values ('" & XlsxData(1, 1) & "'," & _
に変更してください。

回答No.1

kkkkkm

AccessのVBAとして

セルA1＝ID　A2＝氏名　A3＝性別　A4＝住所
エクセルデータは上記一列として
.Range("A1:A4").Value
で取得

T_TMPという一時的なテーブルを作成してそこにエクセルデータを取り込んで新規追加及び既存更新をSQLで行います

エクセルで
1行目が項目で
ID,氏名,性別,住所
2行目からデータがあるという表になっていれば
DoCmd.TransferSpreadsheet acImport, , "T_TMP", "C:\Ok\E.xlsx", True
とか
データが
A1＝ID　B1＝氏名　C1＝性別　D1＝住所
のようになっていれば
DoCmd.TransferSpreadsheet acImport, , "T_TMP", "C:\Ok\E.xlsx", False

という1行でT_TMPにデータが取り込めますがそうではないので以下のようなコードになっています。

Private Sub TestE()
Dim Wb As Object
Dim Ws As Object
Dim XlsxData As Variant
Dim StrSQL As String
Dim FilePath As String

FilePath = "C:\Ok\E.xlsx" 'ここにファイルの場所ファイル名を記載
Set Wb = GetObject(FilePath)
Set Ws = Wb.Worksheets("Sheet1") 'ここにシートを記載する

XlsxData = Ws.Range("A1:A4").Value

Wb.Close
Set Ws = Nothing
Set Wb = Nothing
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT * INTO T_TMP FROM table1;"
DoCmd.RunSQL "DELETE* from T_TMP;"
DoCmd.RunSQL "insert into T_TMP (ID,氏名,性別,住所) values (" & XlsxData(1, 1) & "," & _
"'" & XlsxData(2, 1) & "'," & _
"'" & XlsxData(3, 1) & "'," & _
"'" & XlsxData(4, 1) & "');"
StrSQL = "UPDATE T_TMP LEFT JOIN table1 ON T_TMP.ID = table1.ID SET " & _
"table1.ID = [T_TMP]![ID], " & _
"table1.氏名 = [T_TMP]![氏名], " & _
"table1.性別 = [T_TMP]![性別], " & _
"table1.住所 = [T_TMP]![住所];"
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
End Sub

お礼

有り難うございました。試用してみます　いつもすみません

Powered by GRATICA

補足

table1にエクセルのデータの移行が、ようやく出来ました。ここまでが、出来ますと、あとはなんとかなりそうです　有り難うございました。