企業等では、注文、発注、製造指示など、いろいろな場面で、注文ナンバーや製造指示ナンバーなど、様々な採番を利用しています。
データーベースを、構築する上でも、採番は大事ですね。
採番のルールは、色々あるのでしょうけど、簡単な採番ルールをエクセルのVBAで構築して、自動生成させる一例を取り上げて見ました。
簡単な採番のルールは、以下のようにします。
| T | 2 | 0 | 0 | 6 | 0 | 4 | 1 | 8 | 0 | 0 | 1 |
赤 区分 発注、受注、手配等の記号
水色 西暦
緑色 月
黄色 日
白 999以下の重複しない番号
以上のルールとします。同じ区分が、一日に1000件を超えないと言う条件も加わります
まず、採番のエクセルファイルを作ります。
採番ファイルとして、空のエクセルファイルを作成して、シート1にカラム名として、A1番地にdate,B1番地にnum二つを入力して、ファイルを閉じます。
その後、このファイルを元に、プログラミングで、採番を生成させます。
ネットワークを意識して、採番ファイルは、サーバに作ります。
次にODBCの接続を、設定します。
ODBCの設定は、トップページのエクセルでデーターベースを,参考に、設定してください。
ODBCのDSNの名前は、適当につけます。今回は、saibanとします。
最後にローカルのパソコンのエクセルのファイルに、ADOを利用した、VBAのソースコードを作成します。
次に、ソースコードは、以下の様になります。
（ADOの参照設定をお忘れなく！！！）
|
|
Public Function counter() As String
Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim sqlin As String
Dim sqlupdate As String
Dim d As Date
Dim n As Long
Dim dn As Date
'本日の日付
dn = Date
'本日の日付と件数のSQL
sql = "select date,num from `Sheet1$` " _
& " where date =#" & dn & "#"
'データベースを開いたら、閉じるまで、
'他から、アクセスをできないうにする。
'採番が重複しないように
con.Mode = adModeShareExclusive
'ODBCのDSNの名前
con.ConnectionString = "saiban"
con.Open
Set rs = con.Execute(sql)
'もし本日はじめての採番なら、
'本日の日付と1番を設定する。
If rs.EOF Then
sqlin = "insert into `Sheet1$`(`date`,num) values ('" & dn & "', 1 )"
Set rs = con.Execute(sqlin)
End If
'採番ファイルを読み込む
Set rs = con.Execute(sql)
Do While rs.EOF = False
d = rs.Fields(0).Value
n = rs.Fields(1).Value
rs.MoveNext
Loop
'採番をフォーマットにしたがって、つくる
counter = "T" & Format(d, "yyyymmdd") & Format(n, "000")
'新たな採番の設定
sqlupdate = "update `Sheet1$` set num = num + 1 where date = #" & dn & "#"
Set rs = con.Execute(sqlupdate)
con.Close
End Function
|
いろいろな手配を、ネットワークで作る時に、便利になると思います。
| 追加 2007年2月14日 | |||||
上記のファンションだけだと、これだけで、エクセルのシートに利用すると、 エクセルで、再計算が、あると、いろいろと、一度、生成させた採番が、変わってしまいますね。そこで、 以下のコードを追加して、下のマクロを事項させるとエクセルが、再計算しても、採番は、変わりませんね。 採番は、アクティブセルに生成させ、生成後、すぐ下のセルに、カーソルが移ります。 |
|||||
| Public Sub saiban() ActiveCell = counter() Cells(ActiveCell.Row + 1, ActiveCell.Column).Activate End Sub |
|||||
|
