ACCESSとExcelの連携 在庫管理 その5
こんにちは。伊川(@naonaoke)です。
http://keiyu.xyz/2020/03/03/zaiko4/
今回は、上の記事の続きになります。
Excelから、ACCESSへ転送する魔法のコードを紹介します。
ACCESSへの転送コード
動画でも、ブログでも、何度か紹介していますが、カンタンです。
コードがやや複雑なだけです。
特定の場所を変更するだけで、今回紹介するコードは普通に作動します。
この転送コードを取得しただけで、人生がすこし、幸福になりました。
ACCESSへの転送コード 作成手順その1 転送する前の仕掛け
コードを音読する
ボタンをクリックしたら、
セルB20に本日の日時を入力する。
セルB11がなにも入力されていなかったら、空白ありをMsgBoxに表示させて、条件から抜ける。
条件が抜けることが重要ではなく、入力漏れをなくすることが重要です。
このひと手間加えることで、業務時間は驚くほど短縮されます。
セルB11は、契約番号を入力する場所です。
契約書番号は主キーに設定していますので、空白は許されません。
このコードで、業務時間が40％されたと統計が出ています。（当社比：嘘ですけど）
また、本日の日時は、必ず入力します。
日時がないデータベースは、クリープを入れないコーヒーと同じです。
ACCESSへの転送コード 作成手順その2 転送コード
変更するコード その１
上の図の書き方を相対パスといいます。
相対パスのメリットは、このファルダのドライブを変更しても転送が可能ということです。
変更するコード その２
ACCESSのフィールドとExcelのセルを一致させます。
この点を変更するだけで、みなさんの業務にチューニングできます。
余談
データ転送完了の文言は必ず表示させましょう。
すべてのコード
Sub ボタン3_Click()
Range("B20") = Date
If Range("B11").Value = "" Then
MsgBox "空白あり"
Exit Sub
End If
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim conStr As String
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " _
& ActiveWorkbook.Path & "\在庫管理.accdb"
con.Open ConnectionString:=conStr
rs.Open Source:="MT_申込", ActiveConnection:=con, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic
With rs
.AddNew
.Fields("社員氏名").Value = Range("B2")
.Fields("社員ＩＤ").Value = Range("B3")
.Fields("契約日").Value = Range("B4")
.Fields("機種名").Value = Range("B5")
.Fields("ギガ数").Value = Range("B6")
.Fields("色").Value = Range("B7")
.Fields("端末価格").Value = Range("B8")
.Fields("商品ＩＤ").Value = Range("B9")
.Fields("キャンペーン").Value = Range("B10")
.Fields("契約書番号").Value = Range("B11")
.Fields("顧客名字").Value = Range("B12")
.Fields("顧客氏名").Value = Range("B13")
.Fields("顧客郵便番号").Value = Range("B14")
.Fields("顧客都道府県").Value = Range("B15")
.Fields("顧客住所").Value = Range("B16")
.Fields("顧客電話番号").Value = Range("B17")
.Fields("顧客生年月日").Value = Range("B18")
.Fields("備考").Value = Range("B19")
.Fields("入力日").Value = Range("B20")
.Update
End With
rs.Close
con.Close
MsgBox "データ転送完了"
End Sub
ここがポイント
動画の中では、二重転送が起きた際のエラーも表示しています。主キーが設定されていれば、重複しません。ACCESSは優秀です。
まとめ
今回は、転送コードを紹介しただけです。
次回は、転送コードを解説します。
今回のサンプルファイルは、ありません。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
