VB学习之 按钮响应

2019独角兽企业重金招聘Python工程师标准>>>

Sub 按钮5_Click() Dim strPath As String '输出SQL文件的路径 Dim strFile As String '输出SQL文件的路径+文件名 Dim strPrint As String '输出内容

Dim i, j As Integer         '循环变量

Dim strTable As String      '表名
Dim strField As String      '字段名
Dim strFieldN As String      '字段名（汉字）
Dim strType As String       '字段类型
Dim strLength As String     '字段长度
Dim strNull As String       '非空
Dim strPrimaryKey As String '是否为主键
Dim strTmpPK As String      '是否为主键
'Dim strRemarks As String    '备注
Dim bFirst As Boolean


strPath = "E:\"
'从第个工作表开始循环

    strPrimaryKey = ""

    
    'If strTable = "" Then Exit Sub
    strFile = strPath & "database.sql"
     '以输出的格式打开文件strFileName
    Open strFile For Output As #1
    For i = 4 To Worksheets.Count
     '获得表名
    strTable = Worksheets(i).Cells(4, 3).Value
    strPrint = "USE CRMIS "
    strPrint = strPrint & vbNewLine & "CREATE TABLE " & strTable & "(" & vbNewLine
    '从第三开始循环至使用行
    bFirst = True
    
    For j = 6 To Worksheets(i).UsedRange.Rows.Count
        strLength = ""
        strNull = ""
        strDefault = ""
        strTmpPK = ""
    
        '字段名
        strField = Worksheets(i).Cells(j, 2).Value
        If strField = "" Then Exit For
        strFieldN = Worksheets(i).Cells(j, 6).Value
        '字段类型
        strType = Worksheets(i).Cells(j, 3).Value
        '长度
        strLength = Worksheets(i).Cells(j, 4).Value
        '非空
        strNull = Worksheets(i).Cells(j, 5).Value
        '主键
        strTmpPK = Worksheets(i).Cells(j, 7).Value
        If strTmpPK <> "" Then strPrimaryKey = "," & Worksheets(i).Cells(j, 2).Value
        '备注
      '  strRemarks = Worksheets(i).Cells(j, 8).Value

strPrint = strPrint & vbTab & strField

        Select Case strType
        Case "INTEGER"
        strType = "bigint"
        'strPrint = strPrint & " " & strType
        Case "NUMBER"
        strType = "decimal"
        'strPrint = strPrint & " " & strType
        Case "DATE"
        strType = "datetime"
        'strPrint = strPrint & " " & strType
        Case "NCBOLB"
        strType = "text"
        'strPrint = strPrint & " " & strType
        Case "VARCHAR2"
        strType = "varchar"
        'strPrint = strPrint & " " & strType
        Case "NVARCHAR2"
        strType = "varchar"
        Case "FLOAT"
        strType = "float"
        Case Else
        strType = "text"
        End Select
          
          If bFirst = True Then
        
       strPrint = strPrint & " " & strType & " IDENTITY(1,1)"
        bFirst = False
        Else
       strPrint = strPrint & " " & strType
        End If
        
       ' strPrint = strPrint & " " & strType
        
        If strLength <> "" And strLength <> "-" Then strPrint = strPrint & "(" & strLength & ")"
        If strNull <> "" Then strPrint = strPrint & " " & "NOT NULL"
       ' If strRemarks <> "" Then strPrint = strPrint & " " & strRemarks
        'strPrint = strPrint & " COMMENT '" & strFieldN & "'"
        strPrint = strPrint & "," & vbNewLine
    Next
    
    If strPrimaryKey <> "" Then
        strPrimaryKey = Right(strPrimaryKey, Len(strPrimaryKey) - 1)
        strPrint = strPrint & vbNewLine & vbTab & "PRIMARY KEY(" & strPrimaryKey & ")"
    Else
        strPrint = Left(strPrint, Len(strPrint))
    End If
    
    strPrint = strPrint & vbNewLine & ");"
    
    Print #1, strPrint
    Next
    Close #1

End Sub

转载于:https://my.oschina.net/yangxiaoguang/blog/84892