Excel管理の座席表をLeafletでWeb化した話をExcelで作ってみたサンプル

なんでそれをExcelやめてしまうのん？

という丁度よさげなお題を発見。

https://engineering.linecorp.com/ja/blog/detail/216engineering.linecorp.com

engineering.linecorp.com

この手の話を小耳に挟むと、サンプル出来たら送りますね－

って担当者に報告。

30分くらいでできるのがこちら。

Sub UpdateData()
    
    If MsgBox("座席表を更新します。よろしいですか？", vbYesNoCancel, "メッセージ") <> vbYes Then
        Exit Sub
    End If
    
    
    
    Dim sh As Worksheet
    Dim rng As Range
    
    Set sh = ThisWorkbook.Worksheets("座席表")
    
    Set rng = sh.Range("a1:BB100")
    
    
    Dim r As Range
    Dim str As String
    
    
    str = "座席番号,氏名" & vbCrLf
    
    For Each r In rng
        If Left(r, 1) = "Q" Then
            If r.Offset(1, 0).Value <> "" Then
        
                str = str & r.Value & "," & r.Offset(1, 0).Value & vbCrLf
            
            End If
        End If
    Next
    
    
    Dim fso As New Scripting.FileSystemObject

    fso.OpenTextFile(ThisWorkbook.Path & "\data.txt", ForWriting, True).Write (str)
    
    
    MsgBox "更新完了", , "メッセージ"

End Sub



Sub ReadData()
    Dim sh As Worksheet
    Dim rng As Range
    
    Set sh = ThisWorkbook.Worksheets("座席表")
    
    Set rng = sh.Range("a1:BB100")
    
    Dim r As Range
    Dim dic As New Scripting.Dictionary
    
    
    '座席表の位置取得
    For Each r In rng
        If Left(r, 1) = "Q" Then
            
            dic.Add r.Value, r.Offset(1, 0).Address
            
            'ついでに削除処理
            r.Offset(1, 0).Value = ""
            
        End If
    Next
    
    Dim fso As New Scripting.FileSystemObject
    
    Dim dataTxt As String
    
    If Dir(ThisWorkbook.Path & "\data.txt") = "" Then Exit Sub
    
    dataTxt = fso.OpenTextFile(ThisWorkbook.Path & "\data.txt", ForReading, False).ReadAll
    
    
    Dim lines
    
    lines = Split(dataTxt, vbCrLf)
    
    
    If UBound(lines) < 2 Then Exit Sub
    
    Dim i As Long
    Dim line
    
    For i = 1 To UBound(lines) - 1
        line = Split(lines(i), ",")
        
        If dic.Exists(line(0)) Then
            sh.Range(dic(line(0))).Value = line(1)
        End If
    Next
    
    
End Sub

データを外のテキストファイルで管理しておくのがコツといえばコツ。