编程往往与数据库密不可分,一个项目往往有很多的表,很多时候通过excel来维护表结构,记录表名,字段,类型,注释等等信息,将表结构整理到excel已经是件很累的事了,如果整理好的表结构只是用来查字段当字典用,那就太浪费了,excel提供vba编程,我们可以通过一段小脚本来实现一键创建表结构。使用脚本创建的语句需要标准化每个sheet页的格式,格式根据自己需要来做,这里给出我使用的格式和脚本。
EXCEL模板
VBA编程
打开excel vba编程工具,在ThisWorkbook中添加如下代码:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'关闭工作簿前,删除新创建的工具条先
Dim bar_name As String
bar_name = "HRBSJ"
Application.CommandBars(bar_name).Delete
On Error GoTo Exception
Exit Sub
Exception:
End Sub
Private Sub Workbook_Open()
'打开工作簿时创建工具条
Dim bar_name As String
Dim new_bar As Office.CommandBar
bar_name = "HRBSJ"
Set new_bar = Application.CommandBars.Add(bar_name)
new_bar.Visible = True
new_bar.Position = msoBarLeft
With new_bar.Controls.Add(Type:=msoControlButton, before:=1)
.BeginGroup = True
.Caption = "生成建表脚本"
.TooltipText = "生成建表脚本"
.Style = msoButtonCaption
.OnAction = "Create_HR_Table_Script"
End With
On Error GoTo Exception
Exit Sub
Exception:
End Sub
创建模块:
Private Sub ShowTable()
frmTable.Show
End Sub
Private Sub Create_HR_Table_Script()
Dim line_tablename As Integer, len_col_id As Integer, len_str_type As Integer, col_num As