Option Explicit Dim shet, cnt '------------------------------------------------- - ‘For each shet in array("Sheet1", "Sheet2", "Sheet3"...) '------------------------------------------------- - Dim ind For ind = 2 To 5 ‘modify according to the number of Sheets in the workbook, Sheet2~Sheet5 shet = "Sheet" & ind Dim mdl ‘the current model Set mdl = ActiveModel If (mdl Is Nothing) Then MsgBox "There is no Active Model" End If Dim HaveExcel Dim RQ Dim x1sApp,xlsWorkBook,xlsSheet RQ = vbYes ‘MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation") If RQ = vbYes Then HaveExcel = True ‘Open & Create Excel Document Set x1sApp = CreateObject("Excel.Application") set xlsWorkBook = x1sApp.Workbooks.Open("D:\CMSDB.xls") ‘modify to the corresponding Excel path, note that the suffix must be the same set xlsSheet = x1sApp.Workbooks(1).Worksheets(shet) ‘specify the name of the sheet to be opened Else HaveExcel = False End If a mdl,x1sApp,xlsWorkBook,xlsSheet cnt = cnt + 1 Next MsgBox "Generate total data table structure" + CStr(cnt), vbOK + vbInformation, "table" sub a(mdl,x1sApp,xlsWorkBook,xlsSheet) dim rwIndex dim table dim col dim rowCount rowCount = xlsSheet.usedRange.Rows.Count on error Resume Next For rwIndex = 2 To rowCount ‘specify the Excel row index to be traversed. The first row is the header With xlsSheet If .Cells(rwIndex, 2).Value = "" Then ‘if traverse to the second column is empty, then exit Exit For End If If .Cells(rwIndex, 3).Value = "" Then ‘If the third column is empty, then this line is the table name set table = mdl.Tables.CreateNew ‘Create table table.Name = .Cells(rwIndex, 1).Value ‘specify the table name, the value of the first column table.Code = .Cells(rwIndex, 2).Value table.Comment = .Cells(rwIndex, 1).Value ‘specify the table comment, the value of the first column Else set col = table.Columns.CreateNew ‘Create a column/field ‘MsgBox .Cells(rwIndex, 1).Value, vbOK + vbInformation, "Column" col.Name = .Cells(rwIndex, 1).Value ‘specify column: field name ‘MsgBox col.Name, vbOK + vbInformation, "Column" col.Code = .Cells(rwIndex, 2).Value ‘specify column: field code col.DataType = .Cells(rwIndex, 3).Value ‘specify column: field data type ‘MsgBox col.DataType, vbOK + vbInformation, "Column Type" If .Cells(rwIndex, 4).Value = "" Then col.Comment = .Cells(rwIndex, 1).Value Else col.Comment = .Cells(rwIndex, 4).Value ‘specified column: field description End If if col.Code = "id" Then col.Identity = true End If if col.Code = "ID" Then col.Identity = true End If if .Cells(rwIndex, 4).Value = "Primary Key" Then col.Primary = true End If ‘If .Cells(rwIndex, 5).Value = "NOT NULL" Then ‘Col.Mandatory =true ‘End If End If End With Next xlsWorkBook.Close x1sApp.Quit set x1sApp = nothing set xlsWorkBook = nothing Exit Sub End sub
Previous article: PowerDesigner exports the structure of the data table to Excel One table and one Sheet with link directory
< p>
Option Explicit Dim shet, cnt '------------------------------------------------- - ‘For each shet in array("Sheet1", "Sheet2", "Sheet3"...) '------------------------------------------------- - Dim ind For ind = 2 To 5 ‘modify according to the number of Sheets in the workbook, Sheet2~Sheet5 shet = "Sheet" & ind Dim mdl ‘the current model Set mdl = ActiveModel If (mdl Is Nothing) Then MsgBox "There is no Active Model" End If Dim HaveExcel Dim RQ Dim x1sApp,xlsWorkBook,xlsSheet RQ = vbYes ‘MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation") If RQ = vbYes Then HaveExcel = True ‘Open & Create Excel Document Set x1sApp = CreateObject("Excel.Application") set xlsWorkBook = x1sApp.Workbooks.Open("D:\CMSDB.xls") ‘modify to the corresponding Excel path, note that the suffix must be the same set xlsSheet = x1sApp.Workbooks(1).Worksheets(shet) ‘specify the name of the sheet to be opened Else HaveExcel = False End If a mdl,x1sApp,xlsWorkBook,xlsSheet cnt = cnt + 1 Next MsgBox "Generate total data table structure" + CStr(cnt), vbOK + vbInformation, "table" sub a(mdl,x1sApp,xlsWorkBook,xlsSheet) dim rwIndex dim table dim col dim rowCount rowCount = xlsSheet.usedRange.Rows.Count on error Resume Next For rwIndex = 2 To rowCount ‘specify the Excel row index to be traversed. The first row is the header With xlsSheet If .Cells(rwIndex, 2).Value = "" Then ‘if traverse to the second column is empty, then exit Exit For End If If .Cells(rwIndex, 3).Value = "" Then ‘If the third column is empty, then this line is the table name set table = mdl.Tables.CreateNew ‘Create table table.Name = .Cells(rwIndex, 1).Value ‘specify the table name, the value of the first column table.Code = .Cells(rwIndex, 2).Value table.Comment = .Cells(rwIndex, 1).Value ‘specify the table comment, the value of the first column Else set col = table.Columns.CreateNew ‘Create a column/field ‘MsgBox .Cells(rwIndex, 1).Value, vbOK + vbInformation, "Column" col.Name = .Cells(rwIndex, 1).Value ‘specify column: field name ‘MsgBox col.Name, vbOK + vbInformation, "Column" col.Code = .Cells(rwIndex, 2).Value ‘specify column: field code col.DataType = .Cells(rwIndex, 3).Value ‘specify column: field data type ‘MsgBox col.DataType, vbOK + vbInformation, "Column Type" If .Cells(rwIndex, 4).Value = "" Then col.Comment = .Cells(rwIndex, 1).Value Else col.Comment = .Cells(rwIndex, 4).Value ‘specified column: field description End If if col.Code = "id" Then col.Identity = true End If if col.Code = "ID" Then col.Identity = true End If if .Cells(rwIndex, 4).Value = "Primary Key" Then col.Primary = true End If ‘If .Cells(rwIndex, 5).Value = "NOT NULL" Then ‘Col.Mandatory =true ‘End If End If End With Next xlsWorkBook.Close x1sApp.Quit set x1sApp = nothing set xlsWorkBook = nothing Exit Sub End sub