原文:https://blog.csdn.net/zutsoft/article/details/45441343
编程往往与数据库密不可分,一个项目往往有很多的表,很多时候通过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 Integer
- Dim do_column As Boolean, column_end As