Excel Table Schema VBA

使用Excel將SQL Server 資料表變成資料表規格文件

田語錡 2020/12/28 18:29:36
305

1.      前言

本文介紹如何使用Excel的資料匯入及VBA,反向從SQL Server 將現有資料表變成資料表規格的文件。

2.      使用工具說明:

l   Excel版本:2019

l   SQL Server的資料庫。

l   Excel開啟「開發人員」的索引標籤:「檔案」→「選項」→「自訂功能區」→「主要索引標籤」,選取「開發人員」進行新增。


3.      ExcelSQL Server資料庫匯入資料

(1)      匯入資料表名稱

l   首先點選「資料」→「取得資料」→「從資料庫」→「從SQL Server資料庫」


l   接著輸入資料庫連結的相關資訊,並點選「進階選項」在SQL陳述式中,輸入以下SQL語法。

SQL語法:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

order by TABLE_NAME;


l   點選 確定 接著點選 載入


l   執行成功後,會將所有資料表名稱匯入新的Sheet中。


 

(2)      匯入各TableSchema資料

l   接著要匯入各TableSchema資料。作法同上「(1)匯入資料表名稱」,但輸入的SQL語法如下。

SQL語法:

SELECT

   tb.TABLE_NAME AS 'TableName'

  ,col.ORDINAL_POSITION AS 'No'

  ,col.COLUMN_NAME AS 'ColumnName'

  ,col.DATA_TYPE AS 'DataType'

  ,CASE

    WHEN col.CHARACTER_MAXIMUM_LENGTH = -1

      THEN 'MAX'

      ELSE LTRIM(STR(col.CHARACTER_MAXIMUM_LENGTH,10))

   END AS 'Length'

  ,col.COLUMN_DEFAULT AS 'Default'

  ,col.IS_NULLABLE AS 'Nullable'  

  ,CASE

    WHEN tbc.CONSTRAINT_NAME is not null 

      THEN 'V'

      ELSE ''

   END AS 'PK'

  ,tbc.CONSTRAINT_NAME AS ' PkConstraint'

  ,(SELECT value

      FROM sys.fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', tb.TABLE_NAME, 'column', DEFAULT)

      WHERE name = 'MS_Description'

      AND objtype = 'COLUMN'

      AND objname COLLATE Chinese_Taiwan_Stroke_CI_AS = col.COLUMN_NAME) AS 'Comments'

FROM

  INFORMATION_SCHEMA.TABLES tb

  LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON (tb.TABLE_NAME = col.TABLE_NAME)

  LEFT JOIN

  (

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu1

      ON kcu1.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG 

      AND kcu1.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA

      AND kcu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu2

      ON kcu2.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG 

      AND kcu2.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA

      AND kcu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME

      AND kcu2.ORDINAL_POSITION = kcu1.ORDINAL_POSITION

  ) ON (tb.TABLE_NAME = kcu1.TABLE_NAME AND col.COLUMN_NAME = kcu1.COLUMN_NAME)

  LEFT JOIN

  (

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tbc

    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS colc

    ON  colc.CONSTRAINT_NAME = tbc.CONSTRAINT_NAME

    AND colc.TABLE_NAME = tbc.TABLE_NAME

    AND tbc.CONSTRAINT_TYPE = 'PRIMARY KEY'

  )  ON (tb.TABLE_NAME = tbc.TABLE_NAME AND col.COLUMN_NAME = colc.COLUMN_NAME)

 

ORDER BY tb.TABLE_NAME, col.ORDINAL_POSITION;


l   最後匯出結果:


 

 

4.      修改匯入資料表的格式

(1)      將資料篩選功能取消

l   選擇隨意欄位→「資料」→「篩選」,資料表名稱及各TableSchema資料,皆要執行。



(2)      修改工作表名稱

l   資料表名稱,改為「TABLE_LIST

l   TableSchema資料,改為「DETAIL

l   並將其他未使用到的工作表刪除。


 

5.      建立巨集的程式

(1)      將各TableSchema資料,分開成各Sheet

l   點選「開發人員」→「巨集」,輸入巨集名稱「table_Schema」,點選 建立


l   VBA程式編輯器中,輸入以下程式碼,完成後存檔並關閉VBA程式編輯器。

程式碼:

'Ed為跑回圈次數、St為第一欄的資料、TableName為選擇的Table名稱

Dim Ed As Integer

Dim St As Integer

Dim TableName As String

 

 

   Ed = ActiveSheet.Range("A2").End(xlDown).Row

   St = 2

  

    Do While St <= Ed

      

            Sheets("TABLE_LIST").Select

   

            '設定TableName

            TableName = Range("A" & St).Value

   

            'Detail進行資料篩選的查詢

            Sheets("DETAIL").Select

           

            '選取所有detail的資料,lastAddress為最後一筆資料的位置

            Range("A1").Select

            DetailDown = ActiveSheet.Range("A1").End(xlDown).Row

            DetailRight = ActiveSheet.Range("A1").End(xlToRight).Address

            lastAddress = Left(DetailRight, 3) & DetailDown

            Selection.AutoFilter

            ActiveSheet.Range("$A$1:" & lastAddress).AutoFilter Field:=1, Criteria1:= _

                TableName

               

            '選取查詢後的資料

            Range("B1").Select

            Range(Selection, Selection.End(xlToRight)).Select

            Range(Selection, Selection.End(xlDown)).Select

            Selection.Copy

   

            '新增新的工作表

            Sheets.Add After:=ActiveSheet

           

            '重新命名工作表明稱為TableName

            ActiveSheet.Name = TableName

           

            'A4貼上資料、於A3輸入TableName

            Range("A4").Select

            ActiveSheet.Paste

            Range("A3").Value = TableName

            Selection.Columns.AutoFit

   

            'A1新增回TABLE_LIST的超連結

            Range("A1").Value = "TABLE_LIST"

            Range("A1").Select

                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _

                "TABLE_LIST!A1", TextToDisplay:="TABLE_LIST"

               

            '回到DETAIL將資料篩選關掉

            Sheets("DETAIL").Select

            Selection.AutoFilter

   

            '回到TABLE_LIST

            Sheets("TABLE_LIST").Select

    

        St = St + 1

        

    Loop


 

(2)      Table_List建立與各Table資料的超連結

l   點選「開發人員」→「巨集」,輸入巨集名稱「table_list_link」,點選 建立


l   VBA程式編輯器中,輸入以下程式碼,完成後存檔並關閉VBA程式編輯器。

程式碼:

'Ed為跑回圈次數、St為第一欄的資料、TableName為選擇的Table名稱

Dim Ed As Integer

Dim St As Integer

Dim TableName As String

 

   Ed = ActiveSheet.Range("A2").End(xlDown).Row

   St = 2

  

        Do While St <= Ed

       

               '選擇TABLE_LISTsheet

               Sheets("TABLE_LIST").Select

              

               '設定TableName

               TableName = Range("A" & St).Value

              

               '選取要設定超連結的欄位

               Range("A" & St).Select

              

               '設定超連結

               ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & St), Address:="", SubAddress:= _

                 Sheets(TableName).Name & "!A1", TextToDisplay:=Sheets(TableName).Name

                   

            St = St + 1

        Loop

 


 

6.      執行巨集結果

(1)      執行table_Schema的巨集

l   點選「開發人員」→「巨集」,點選「table_Schema」,點選 執行


l   執行結果:產出各Table SchemaSheet資料。


(2)      執行table_list_link的巨集

l   點選「開發人員」→「巨集」,點選「table_list_link」,點選 執行


l   執行結果:各Table 點選後,可以連結到相對應的Table Schema資料。


  

7.      測試結果

l   點選「County」連結到相對應的Table Schema資料。


l   點選後結果。


l   若點選「TABLE_LIST」會回到TABLE_LISTSheet


 

8.      參考來源

l   邊界與列數、行數、位址

http://cymail6.blogspot.com/2012/10/blog-post_1665.html

l  Excel VBA 尋找表格最後一欄、最後一列、最右下角教學與範例

https://officeguide.cc/excel-vba-find-row-column-cell-tutorial-examples/

l  [Tools] 自動化產出個人風格 SQL Server 資料表文件

https://dotblogs.com.tw/wasichris/2016/07/07/004356

田語錡