ExcelとDAOで販売管理ソフトを作る 自社情報 Step8

登録ボタンがクリックされると入力データをチェックし、OKならばデータを登録します。
自社情報のレコードは先頭の1件のみですので、既にレコードがある場合は修正登録し、なければ新規登録します。

修正登録のの方法は、レコードセット.Edit　～　レコードセット.Update になります。
新規登録のの方法は、レコードセット.Addnew　～　レコードセット.Update になります。

■実行画面


■登録ボタンクリックイベント
'登録ボタン
Private Sub CommandButton2_Click()
If ExDataCheck = False Then
Exit Sub
End If
'データの登録
ExDataSave
End Sub

■登録コード
'データの登録
Private Sub ExDataSave()
Dim rs As Recordset

'自社情報テーブルを開き、テキストボックスに表示する
Set rs = tDbHanbai.OpenRecordset("T_自社情報", dbOpenDynaset)
If rs.EOF Then
'新規追加
rs.AddNew
Else
'修正
rs.Edit
End If

rs("自社名") = TextBox1.Value
rs("郵便番号") = TextBox2.Value
rs("住所1") = TextBox3.Value
rs("住所2") = TextBox4.Value
rs("TEL") = TextBox5.Value
rs("FAX") = TextBox6.Value
rs("振込先") = TextBox7.Value
rs.Update
Set rs = Nothing

End Sub

ExcelとDAOで販売管理ソフトを作る 自社情報 Step7

登録ボタンが押された場合、まずデータが正常に入力されているかチェックします。

1）自社名は必ず入力されていることにします。
　入力されていない場合、エラーメッセージを表示します。

2）各データの入力文字数が、データベースのフィールド文字数以内かチェックします。
　フィールド範囲以上の場合、保存できないのでこのチェックは必ず必要です。

■自社名が入力されていない場合のエラー表示フォーム


■フィールドサイズ以上に入力された場合のエラー表示フォーム


'登録ボタン
Private Sub CommandButton2_Click()
If ExDataCheck = False Then
Exit Sub
End If
End Sub

'データのチェック
Private Function ExDataCheck() As Boolean
ExDataCheck = False
If TextBox1.Value = "" Then
MsgBox "自社名は必ず入力してください。"
Exit Function
End If

If Len(TextBox1.Value) > 30 Then
MsgBox "自社名は30文字以内で入力してください。"
Exit Function
End If
If Len(TextBox2.Value) > 20 Then
MsgBox "郵便番号は20文字以内で入力してください。"
Exit Function
End If
If Len(TextBox3.Value) > 50 Then
MsgBox "住所1は50文字以内で入力してください。"
Exit Function
End If
If Len(TextBox4.Value) > 50 Then
MsgBox "住所2は50文字以内で入力してください。"
Exit Function
End If
If Len(TextBox5.Value) > 30 Then
MsgBox "TELは30文字以内で入力してください。"
Exit Function
End If
If Len(TextBox6.Value) > 30 Then
MsgBox "FAXは30文字以内で入力してください。"
Exit Function
End If
If Len(TextBox7.Value) > 50 Then
MsgBox "振込先は50文字以内で入力してください。"
Exit Function
End If

ExDataCheck = True
End Function

ExcelとDAOで販売管理ソフトを作る 自社情報 Step6

自社情報入力用ユーザーフォームが開いた時に、自社情報テーブルを開きテキストボックスに登録済みデータを表示する。
登録されていなければ無視します。

■UserForm_Initializeイベントに入力します。
Private Sub UserForm_Initialize()
Dim rs As Recordset

'自社情報テーブルを開き、テキストボックスに表示する
Set rs = tDbHanbai.OpenRecordset("T_自社情報", dbOpenDynaset)
If Not rs.EOF Then
TextBox1.Value = rs("自社名")
TextBox2.Value = rs("郵便番号")
TextBox3.Value = rs("住所1")
TextBox4.Value = rs("住所2")
TextBox5.Value = rs("TEL")
TextBox6.Value = rs("FAX")
TextBox7.Value = rs("振込先")
End If
Set rs = Nothing
End Sub

ExcelとDAOで販売管理ソフトを作る 自社情報 Step5

ユーザーフォーム等でデータベースを使用する度に、データベースファイルを開くことはムダですので、ワークブックが開いた時点でデータベースファイルも開くようにします。
開くには、OpenDatabaseメソッドを使用します。
構文：　SET データベースオブジェクト = OpenDatabase(データベースファイル名)

そして、ワークブックが閉じる直前にデータベースファイルも終了処理を行います。
構文：　SET データベースオブジェクト = Nothing

1)標準モジュールに追加してください。
Public tDbHanbai As Database

2）Workbook_Openイベントを変更してください。
On Error GoTo ErrExit
'データファイルの存在確認
If ExFileExist(sExcelPath + "hanbai2009.mdb", vbNormal) = "" Then
'MsgBox "ここに、MDBファイルの新規作成処理を記入します。"
MyMakeDataBase
Else
Set tDbHanbai = OpenDatabase(sExcelPath + "hanbai2009.mdb")
End If
Exit Sub
ErrExit:
Beep
MsgBox "データベースのオープン時、エラーが発生しました。" & vbNewLine & Err.Description

以上で起動するとデータベースが開きます。

ExcelとDAOで販売管理ソフトを作る 自社情報 Step4

自社情報入力用のユーザーフォームを作成します。

■使用したコントロール
・入力用のテキストボックス　TextBox1 ～ TextBox7
・キャンセルボタン　CommandButton1
・登録ボタン　CommandButton2
フォントサイズは11にしています。


■キャンセルボタンのイベントコード
Option Explicit
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

ExcelとDAOで販売管理ソフトを作る 自社情報 Step3

Workbook_OpenイベントでMDBファイルが見つからない場合、新規にデータベースファイルを作成し、自社情報テーブルを作成します。

1） CreateDatabase でデータベースを作成します。
2） CreateField でフィールドを作成します。

作成するフィールドは下記にしました。
・　自社名　テキスト型　30文字
・　郵便番号　テキスト型　20文字
・　住所1　テキスト型　50文字
・　住所2　テキスト型　50文字
・　TEL　テキスト型　30文字
・　FAX　テキスト型　30文字
・　振込先　テキスト型　50文字


■ThisWorkbookイベント
Option Explicit
'ブックが開いた時のイベント
Private Sub Workbook_Open()

'このExcelファイルがあるフォルダー
sExcelPath = ActiveWorkbook.Path
'末にパスが付いているかどうか
If Right(sExcelPath, 1) <> "\" Then
'パスを付ける
sExcelPath = sExcelPath + "\"
End If

'データファイルの存在確認
If ExFileExist(sExcelPath + "hanbai2009.mdb", vbNormal) = "" Then
'MsgBox "ここに、MDBファイルの新規作成処理を記入します。"
MyMakeDataBase
End If

End Sub


■データベース作成プロシージャ
Private Sub MyMakeDataBase()
Dim db As Database
Dim bRet As Boolean

' データベース作成
Set db = DBEngine.Workspaces(0).CreateDatabase(sExcelPath + "hanbai2009.mdb", dbLangJapanese)

bRet = MyMakeJisyajyouhou(db)

Set db = Nothing
End Sub


■フィールド作成プロシージャ
Private Function MyMakeJisyajyouhou(tdb As Database) As Boolean
Dim tbdef As TableDef
Dim fld As Field

On Error GoTo ErrExit
'テーブル作成
Set tbdef = tdb.CreateTableDef("T_自社情報")

'フィールド作成
Set fld = tbdef.CreateField("自社名", dbText, 30)
tbdef.Fields.Append fld
Set fld = tbdef.CreateField("郵便番号", dbText, 20)
tbdef.Fields.Append fld
Set fld = tbdef.CreateField("住所1", dbText, 50)
tbdef.Fields.Append fld
Set fld = tbdef.CreateField("住所2", dbText, 50)
tbdef.Fields.Append fld
Set fld = tbdef.CreateField("TEL", dbText, 30)
tbdef.Fields.Append fld
Set fld = tbdef.CreateField("FAX", dbText, 30)
tbdef.Fields.Append fld
Set fld = tbdef.CreateField("振込先", dbText, 50)
tbdef.Fields.Append fld

tdb.TableDefs.Append tbdef

'終了処理
Set fld = Nothing
Set tbdef = Nothing

MyMakeJisyajyouhou = True
Exit Function
ErrExit:
MyMakeJisyajyouhou = False
MsgBox "自社情報テーブル作成中にエラーが発生しました。処理を中止します。" & vbNewLine & Err.Description
End Function

ExcelとDAOで販売管理ソフトを作る -はじめに- Step2

DAOをExcelでも使えるように、Microsoft DAO Object Library を追加します。
これをしないと、MDBデータベースファイルを使用できません。

VBEメニューの「ツール」-「参照設定」をクリックします。


Microsoft DAO 3.6 Object Library のチェックをONにし、「OK」ボタンをクリックします。
Excelのバージョンによって、3.6のバージョンは変わります。


これでDAOが使用できるようになります。

ExcelとDAOで販売管理ソフトを作る -はじめに- Step1

どこまでできるかどうか分かりませんがExcelで販売管理ソフトを作ってみます。
途中で挫折してしまう可能性がありますが、それぞれの段階でなんらかのお役にたてれば幸いです。

Excelで何らかの業務用ソフトを使う場合の問題は、誰でも簡単にフォームやデータを変更できてしまうことです。
又、コピーをいくつも作り、どれが実際に使っているファイルか分からなくなります。

DAOでAccessのデータベースを使い、データを別ファイルにすればいくらかこの問題も解消するのではないでしょうか。

・データファイルはExcelファイルと同じフォルダに存在することにします。
・データファイル名は、AccessのMDBファイルで「hanbai2009.mdb」にします。

Step1 ： データファイルの存在をチェックします。

■Module1（標準モジュールを追加してください）
Option Explicit
Public sExcelPath As String

'ファイルかフォルダの存在確認
Public Function ExFileExist(sFiName As String, nAttr As Integer) As String
If sFiName = "" Then
ExFileExist = ""
Exit Function
End If
On Error Resume Next
Err.Number = 0
ExFileExist = Dir(sFiName, nAttr)
If Err.Number <> 0 Then
ExFileExist = ""
End If
On Error GoTo 0
End Function

■ThisWorkbookイベント
Option Explicit
'ブックが開いた時のイベント
Private Sub Workbook_Open()
'このExcelファイルがあるフォルダー
sExcelPath = ActiveWorkbook.Path
'末にパスが付いているかどうか
If Right(sExcelPath, 1) <> "\" Then
'パスを付ける
sExcelPath = sExcelPath + "\"
End If
'データファイルの存在確認
If ExFileExist(sExcelPath + "hanbai2009.mdb", vbNormal) = "" Then
MsgBox "ここに、MDBファイルの新規作成処理を記入します。"
End If
End Sub

Tips200 - [Excel数値関数] AVERAGE

AVERAGE関数は指定した範囲の平均を計算します。
構文： =AVERAGE(範囲)

■実行例


空白や文字があれば無視されます。


■計算式の表示
=AVERAGE(C3:C8)

Tips199 - [Excel日付の関数] TIME

TIME関数は指定した時間の小数(シリアル値)を返します。

構文：TIME(時,分,秒)

■実行例
E4：E6は書式設定で時間表示にしています。
B5やC6のように、24時間や60分を越えていても換算されます。


■計算式の表示
=TIME(B3,C3,D3)