les_in.frm

这是一个非常 好的一个程序进行体会一下学生管理系统

VERSION 5.00
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Begin VB.Form Form16 
   BackColor       =   &H00FFC0C0&
   BorderStyle     =   1  'Fixed Single
   Caption         =   "选课信息导入"
   ClientHeight    =   3390
   ClientLeft      =   3885
   ClientTop       =   1275
   ClientWidth     =   4470
   Icon            =   "Les_in.frx":0000
   LinkTopic       =   "Form16"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3390
   ScaleWidth      =   4470
   Begin VB.CommandButton Command2 
      BackColor       =   &H00FFC0C0&
      Caption         =   "取  消"
      Height          =   375
      Left            =   2880
      Style           =   1  'Graphical
      TabIndex        =   9
      Top             =   2880
      Width           =   1095
   End
   Begin VB.CommandButton Command1 
      BackColor       =   &H00FFC0C0&
      Caption         =   "确  定"
      Height          =   375
      Left            =   480
      Style           =   1  'Graphical
      TabIndex        =   8
      Top             =   2880
      Width           =   1095
   End
   Begin VB.Frame Frame1 
      BackColor       =   &H00FFC0C0&
      Caption         =   "请先设置相关信息："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   10.5
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   2535
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   4215
      Begin MSComDlg.CommonDialog Dialog 
         Left            =   3120
         Top             =   1800
         _ExtentX        =   847
         _ExtentY        =   847
         _Version        =   393216
      End
      Begin VB.CheckBox Check1 
         BackColor       =   &H00FFC0C0&
         Caption         =   "是重修名单"
         Height          =   255
         Left            =   1200
         TabIndex        =   10
         Top             =   2160
         Width           =   1575
      End
      Begin VB.TextBox Text2 
         Height          =   270
         Left            =   1320
         TabIndex        =   7
         Top             =   1680
         Width           =   1335
      End
      Begin VB.ComboBox Combo1 
         Height          =   300
         Left            =   1320
         TabIndex        =   5
         Top             =   1080
         Width           =   1695
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Left            =   1320
         TabIndex        =   2
         Top             =   480
         Width           =   1215
      End
      Begin VB.Label Label4 
         BackColor       =   &H00FFC0C0&
         Caption         =   "授课教师："
         Height          =   255
         Left            =   360
         TabIndex        =   6
         Top             =   1680
         Width           =   1095
      End
      Begin VB.Label Label3 
         BackColor       =   &H00FFC0C0&
         Caption         =   "所选课程："
         Height          =   255
         Left            =   360
         TabIndex        =   4
         Top             =   1080
         Width           =   1095
      End
      Begin VB.Label Label2 
         BackColor       =   &H00FFC0C0&
         Caption         =   "（如：20001）"
         Height          =   255
         Left            =   2640
         TabIndex        =   3
         Top             =   480
         Width           =   1335
      End
      Begin VB.Label Label1 
         BackColor       =   &H00FFC0C0&
         Caption         =   "选课学期："
         Height          =   255
         Left            =   360
         TabIndex        =   1
         Top             =   480
         Width           =   1095
      End
   End
End
Attribute VB_Name = "Form16"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Attribute VB_Ext_KEY = "RVB_UniqueId" ,"41FF01FA036D"
Attribute VB_Ext_KEY = "RVB_ModelStereotype" ,"Form"
Dim mrc As ADODB.Recordset   '定义记录集
Dim txtSQL As String         '定义SQL字符串
Dim term As String           '用来存储学期
Dim courseno As String       '用来存储课程号
Dim tname As String          '用来存储授课教师名
Dim i As Integer             '定义循环变量

Private Sub Command1_Click() '确定按钮
On Error GoTo ErrInfo

    If Text1.Text = "" Then
        MsgBox "请填写学期名！", 0 + 48, "注意！"
        Text1.SetFocus
        Exit Sub
    End If
    If Combo1.Text = "" Then
        MsgBox "请选择课程名！", 0 + 48, "注意！"
        Combo1.SetFocus
        Exit Sub
    End If
    If Text2.Text = "" Then
        MsgBox "请填写授课教师姓名", 0 + 48, "注意！"
        Text2.SetFocus
        Exit Sub
    End If
    term = Trim(Text1.Text)
    txtSQL = "select Cou_no from Cou_info where Cou_name =" & "'" & Trim(Combo1.Text) & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    If mrc.EOF Then
        MsgBox "所设课程名未登记！", 0 + 48, "注意！"
        Combo1.SetFocus
        Exit Sub
    End If
    courseno = mrc.Fields(0)
    mrc.Close
    Set mrc = Nothing
    tname = Trim(Text2.Text)
    '开始导入
    Dim fname As String
    Dim strName As String
    Dim myApp As Excel.Application
    Dim myWorkbook As Excel.Workbook
    Dim mySheet As Excel.Worksheet
                 
        If myApp Is Nothing Then
            Set myApp = CreateObject("Excel.Application") '创建Excel类实例
        End If
        With Dialog
            .DefaultExt = "xls"
            .FileName = fname
            .DialogTitle = "选课信息信息导入"
            .CancelError = True
            .Filter = "Excel 文件(*.xls)|*.xls"
            .ShowOpen
        End With
        fname = Dialog.FileName
        Set myWorkbook = myApp.Workbooks.Open(fname) '打开导入文件
        Set mySheet = myWorkbook.Worksheets.Item(1)
        txtSQL = "select * from Les_info"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
        strName = mySheet.Cells(1, 1)
        i = 1
        Do Until strName = ""
            mrc.AddNew
            Select Case Len(mySheet.Cells(i, 1)) '加上在excel中自动省去的0
                Case 6:
                    mrc!Stu_no = "000" & mySheet.Cells(i, 1)
                Case 8:
                    mrc!Stu_no = "0" & mySheet.Cells(i, 1)
            End Select
            mrc!Cou_no = courseno
            mrc!Les_term = term
            mrc!Les_tna = tname
            If Check1.Value = 1 Then
                mrc!Les_cx = True
            End If
            mrc.Update
            i = i + 1
            strName = mySheet.Cells(i, 1)
        Loop
        mrc.Close
        Set mrc = Nothing
        MsgBox "导入完毕！", 0 + 64, "恭喜！"
        myApp.Workbooks.Close
        Set myadorstNew = Nothing
        Set myWorkbook = Nothing
        Set mySheet = Nothing
        Set myApp = Nothing
        Unload Me
        Exit Sub
ErrInfo:
        Select Case Err.Number
            Case 1004
                MsgBox "请选择正确的Excel文件！", vbInformation, "错误"
            Case 3265
                MsgBox "应用程序找不到对象！", vbInformation, "错误"
            Case -2147217887
                MsgBox "导入文件中存在未登记的学生信息，导入失败！", 0 + 48, "错误"
        End Select
        myApp.Workbooks.Close
        Set mrc = Nothing
        Set myWorkbook = Nothing
        Set mySheet = Nothing
        Set myApp = Nothing
        Unload Me
End Sub

Private Sub Command2_Click() '取消按钮
    Unload Me
End Sub

Private Sub Form_Load()
    '初始化课程名称选择框
    txtSQL = "select distinct Cou_name from Cou_info "
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    While Not mrc.EOF
        Combo1.AddItem mrc.Fields(0)
        mrc.MoveNext
    Wend
    mrc.Close
    Set mrc = Nothing
End Sub