End Sub




Private Sub cmdNext2_Click()

'    If Trim(cmbName.Text) = "" Then
'        Message "請選擇名稱!"
'        Exit Sub
'    End If
    
    If Trim(cmbCode.Text) = "" Then
        Message "請選擇編號!"
        Exit Sub
    End If
    
    If Option1 = True Then
        If Trim(cmbCard.Text) = "" Then
            Message "請選擇卡號!"
            Exit Sub
        End If
    Else
        If Len(Trim(txtBaseVal.Text)) <> 8 Then
            Message "基值輸入有誤,卡號由8位數字組成!"
            Exit Sub
        End If
    End If
    
'    If Trim(cmbOnClass.Text) = "" Then
'        Message "請選擇默認排班!"
'        Exit Sub
'    End If
'
'    If Trim(cmbVac.Text) = "" Then
'        Message "請選擇默認休假!"
'        Exit Sub
'    End If
    
    
    HideFrame
    Frame(3).Visible = True
    
    cmdFinish.Enabled = False

    Dim lName As Long
    Dim lCode As Long
    Dim lCard As Long
    Dim lOnClassID As Long
    Dim lVacID As Long
    
    Dim sName As String
    Dim sCode As String
    Dim sCard As String
    Dim tmpSQL As String
    Dim Index As Long
    
    Dim blnOpt As Boolean
    
    blnOpt = Option1
    lCard = Val(txtBaseVal.Text)
    
    
    Dim rst As New Recordset
    
    
    If LCase(Right(Trim(m_FilePath), 3)) = "xls" Then
            
            If rst.State = adStateOpen Then rst.Close
            Set rst = Nothing
            rst.CursorLocation = adUseClient
            rst.Open "select * from Employee ", con, adOpenStatic, adLockBatchOptimistic
            
            If cmbName.ListIndex <> -1 Then lName = cmbName.GetItemData(cmbName.ListIndex)
            If cmbCode.ListIndex <> -1 Then lCode = cmbCode.GetItemData(cmbCode.ListIndex)

            lOnClassID = cmbOnClass.ID
            lVacID = cmbVac.ID
            
'            Set ExcelSheet = ExcelApp.ActiveWorkbook.Sheets(1)
            
            ProgressBar.Value = 0
            ProgressBar.Max = ExcelSheet.UsedRange.Rows.Count
            
            For Index = 2 To ExcelSheet.UsedRange.Rows.Count
            
                If blnOpt = True Then
                
                    If cmbCard.ListIndex <> -1 Then lCard = cmbCard.GetItemData(cmbCard.GetCurSel)
                    
                    sCard = Trim(ExcelSheet.Cells(Index, lCard))
                    If sCard = "" Then GoTo goNext
                    If Trim(ExcelSheet.Cells(Index, lCode)) = "" Then GoTo goNext
                    
                    If rst.RecordCount > 0 Then rst.MoveFirst
                    rst.Find "Code='" & ExcelSheet.Cells(Index, lCode) & "'"
                    If rst.EOF = False Then GoTo goNext
            
                    If rst.RecordCount > 0 Then rst.MoveFirst
                    rst.Find "Card='" & ExcelSheet.Cells(Index, lCard) & "'"
                    If rst.EOF = False Then GoTo goNext
                    
                    If Len(sCard) < 8 Then
                        sCard = Right("00000000" & sCard, 8)
                    End If
                    
                    If Len(sCard) > 8 Then
                    
                        If Option4 Then
                            sCard = Right(sCard, 8)
                        Else
                            sCard = Left(sCard, 8)
                        End If
                        
                    End If
                
                Else
                    
                    sCard = Right("00000000" & CStr(lCard), 8)
                    
                    If sCard = "" Then GoTo goNext
                    If Trim(ExcelSheet.Cells(Index, lCode)) = "" Then GoTo goNext
                    
                    If rst.RecordCount > 0 Then rst.MoveFirst
                    rst.Find "Code='" & ExcelSheet.Cells(Index, lCode) & "'"
                    If rst.EOF = False Then GoTo goNext
                    
                    lCard = lCard + 1
                End If

                rst.AddNew
                If Trim(cmbCode.Text) <> "" Then rst.Fields("Code") = ExcelSheet.Cells(Index, lCode)
                If Trim(cmbName.Text) <> "" Then rst.Fields("Name") = ExcelSheet.Cells(Index, lName)
                If sCard <> "" Then rst.Fields("Card") = sCard
                If Trim(cmbOnClass.Text) <> "" Then rst.Fields("OnClassID") = lOnClassID
                If Trim(cmbVac.Text) <> "" Then rst.Fields("VacID") = lVacID
                rst.Update
                
goNext:
                ProgressBar.Value = ProgressBar.Value + 1
                
            Next
            rst.UpdateBatch

    ElseIf LCase(Right(Trim(m_FilePath), 3)) = "mdb" Then
    
            If rst.State = adStateOpen Then rst.Close
            Set rst = Nothing
            rst.CursorLocation = adUseClient
            rst.Open "select * from " & cmbTable.Text, tmpCon, adOpenStatic, adLockBatchOptimistic
            
            
            If rstExec.State = adStateOpen Then rstExec.Close
            Set rstExec = Nothing
            rstExec.CursorLocation = adUseClient
            rstExec.Open "select * from Employee", con, adOpenStatic, adLockBatchOptimistic
            
            If rst.RecordCount > 0 Then
                rst.MoveFirst
                ProgressBar.Value = 0
                ProgressBar.Max = rst.RecordCount
            End If
            While Not rst.EOF
                
                If Trim(cmbName.Text) <> "" Then
                    If IsNull(rst.Fields(cmbName.Text)) Then sName = "" Else sName = Trim(rst.Fields(cmbName.Text))
                End If
                
                If Trim(cmbCode.Text) <> "" Then
                    If IsNull(rst.Fields(cmbCode.Text)) Then sCode = "" Else sCode = Trim(rst.Fields(cmbCode.Text))
                End If
                lOnClassID = cmbOnClass.ID
                lVacID = cmbVac.ID
                
                If blnOpt Then
                    sCard = Trim(rst.Fields(cmbCard.Text))
                    If sCode = "" Then GoTo toNext
                    If sCard = "" Then GoTo toNext
                    
                    If rstExec.RecordCount > 0 Then rstExec.MoveFirst
                    rstExec.Find "Code='" & sCode & "'"
                    If rstExec.EOF = False Then GoTo toNext
            
                    If rstExec.RecordCount > 0 Then rstExec.MoveFirst
                    rstExec.Find "Card='" & sCard & "'"
                    If rstExec.EOF = False Then GoTo toNext
                    
                    If Len(sCard) < 8 Then
                        sCard = Right("00000000" & sCard, 8)
                    End If
                    
                    If Len(sCard) > 8 Then
                    
                        If Option4 Then
                            sCard = Right(sCard, 8)
                        Else
                            sCard = Left(sCard, 8)
                        End If
                        
                    End If
                    
                Else
                    
                    sCard = Right("00000000" & CStr(lCard), 8)
                    
                    If sCode = "" Then GoTo toNext
                    If sCard = "" Then GoTo toNext
                    
                    If rstExec.RecordCount > 0 Then rstExec.MoveFirst
                    rstExec.Find "Code='" & sCode & "'"
                    If rstExec.EOF = False Then GoTo toNext
                            
                    lCard = lCard + 1
                End If
                
                rstExec.AddNew
                If Trim(cmbCode.Text) <> "" Then rstExec.Fields("Code") = sCode
                If Trim(cmbName.Text) <> "" Then rstExec.Fields("Name") = sName
                If sCard <> "" Then rstExec.Fields("Card") = sCard
                If Trim(cmbOnClass.Text) <> "" Then rstExec.Fields("OnClassID") = lOnClassID
                If Trim(cmbVac.Text) <> "" Then rstExec.Fields("VacID") = lVacID
                rstExec.Update
               
toNext:
                rst.MoveNext
                ProgressBar.Value = ProgressBar.Value + 1
            Wend
            rstExec.UpdateBatch
            
    End If
    
    If rst.State = adStateOpen Then rst.Close
    Set rst = Nothing
    
    cmdFinish.Enabled = True
End Sub

Private Sub cmdFinish_Click()
    Unload Me
End Sub


Private Sub cmdSelect_Click()
On Error GoTo IsErr:

    ComDlg.CancelError = True
    ComDlg.Filter = "Excel File(*.xls)|*.xls|Access File(*.mdb)|*.mdb|All File(*.xls;*.mdb)|*.xls;*.mdb"
    ComDlg.InitDir = App.Path
    ComDlg.DialogTitle = "導入"
    ComDlg.ShowOpen
    txtPath.Text = ComDlg.FileName
    
IsErr:
End Sub

Private Sub HideFrame()
    Dim i As Integer
    
    For i = 1 To Frame.Count
        Frame(i).Visible = False
    Next

End Sub





Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next

    If rstGrid.State = 1 Then rstGrid.Close
    Set rstGrid = Nothing
    If rstExec.State = 1 Then rstExec.Close
    Set rstExec = Nothing

    If tmpCon.State = 1 Then tmpCon.Close
    Set tmpCon = Nothing
    
    If Not (ExcelSheet Is Nothing) Then Set ExcelSheet = Nothing
    If Not (ExcelWorkbook Is Nothing) Then Set ExcelWorkbook = Nothing
    ExcelApp.Quit
    If Not (ExcelApp Is Nothing) Then Set ExcelApp = Nothing
    
End Sub

Private Sub Option1_Click()
    Option1 = True
    Option2 = False
    Option3.Enabled = True
    Option4.Enabled = True
    txtBaseVal.Enabled = False
    cmbCard.Enabled = True
    
End Sub

Private Sub Option2_Click()
    Option1 = False
    Option2 = True
    Option3.Enabled = False
    Option4.Enabled = False
    txtBaseVal.Enabled = True
    cmbCard.Enabled = False
   
End Sub

Private Sub Option3_Click()
    Option3 = True
    Option4 = False
End Sub

Private Sub Option4_Click()
    Option3 = False
    Option4 = True
End Sub