Ubuntu 上の SQL Server への接続を EXCEL VBA で記録する

　最近はとにもかくにも Power Query である．しかしながら，そのプロセスを VBA で記述したものを見ることが少ない．検索の仕方が悪いだけかも知れないが．

　今回は外部データベースへの接続として Ubuntu 上の SQL Server を選んでみた．今回，Windows 上の SQL Server Management Studio から Ubuntu 上の SQL Server にデータベースを作成し，テーブルを挿入するの記事で作成したデータベースがあったので，これに接続してみることにした．

SQL Server Management Studio から SQL Server へ接続してみる

　俺の環境では端末に割り振られた IP アドレスが不定期に切り替わる．そのため以前は接続できた IP アドレスが生きているかどうか，事前に確認する必要がある．

　Windows 端末から SQL Server Management Studio を起動する．サーバー名をプルダウンして IP アドレスの方を選択する．認証は SQL Server 認証である．ログイン名とパスワードは自動で入力されている．「接続」をクリックする．

SQL Server Management StudioからUbuntu上のSQL Serverに接続

　オブジェクトエクスプローラーにデータベースエンジンが表示されている．「データベース」直下に目的である STFC2015 が表示されている．IP アドレスは生きており，目的のデータベースも認識されていることが分かった．

SQL Server Management Studioのオブジェクトエクスプローラーでデータベースを確認

EXCEL の「データの取得と変換」から「データの取得」へ

　次は EXCEL を起動する．ここで「マクロの記録」を開始している．「データ」タブの「データの取得と変換」の「データの取得」から「データベースから」の「SQL Serverデータベースから」を選択する．

EXCELの「データ」タブの「データの取得」から「データベースから」の「SQL Serverデータベースから」を選択

最初にサーバー名を入力

　接続画面が表示される．味も素っ気もない画面である．ここではサーバー名，つまり先程の IP アドレスを入力する．OK をクリックする．

次にログイン名，パスワードを入力

　初回接続だと次にログイン名とパスワードを聞かれる．SQL Server Management Studio の時と同様にログイン名 SA とパスワードを入力する．

　スクリーンショットがないのだが，2回目以降の接続ではなぜか聞かれない．システムのどこかに接続情報が登録され，それを呼び出しているのかも知れない．

最後にテーブルを指定

　ナビゲーターが開く．この時点でデータベースには接続できているが，まだテーブルを指定していないため，プレビューには何も表示されていない．

ナビゲーターの初期画面．IPアドレスはサーバーを示しており，直下のSTFC2015がデータベース名

「読み込み」して実際にデータが抽出される

　データベースを展開してテーブルをクリックすると右側のパネルにテーブルのプレビューが表示される．ここまで来て「読み込み」をクリックすると実際に接続され，データベースから EXCEL 側にデータが抽出される．

データベース「STFC2015」を展開するとテーブル「STFC2015」が現れるので，それをクリックするとプレビューが見える

VBE を起動して記録されたマクロを確認

　マクロの記録をここで終了し，VBE を起動 (Alt + F11) して実際のコードを確認してみよう．

Option Explicit

Sub Macro1()
    ActiveWorkbook.Queries.Add Name:="STFC2015", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Sql.Databases(""192.168.0.101"")," & Chr(13) & "" & Chr(10) & "    STFC1 = ソース{[Name=""STFC2015""]}[Data]," & Chr(13) & "" & Chr(10) & "    dbo_STFC2015 = STFC1{[Schema=""dbo"",Item=""STFC2015""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    dbo_STFC2015"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=STFC2015;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [STFC2015]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "STFC2015"
        .Refresh BackgroundQuery:=False
    End With
End Sub

　上記サンプルコードから，オブジェクトブラウザーで調査すべき項目は以下であることが分かる．

Power Query を表現するオブジェクト

Queries コレクション

　WorkbookQuery オブジェクトのコレクションである．

CLASS	MEMBER	Type of Return
Queries	Add	WorkbookQuery
	Application	Application
	Count	Long
	Creator	XlCreator
	FastCombine	Boolean
	Item	WorkbookQuery
	Parent	Object

WorkbookQuery

　Power Query により作成されたクエリを表現するオブジェクトである．Office 2016 から導入された．公式の情報はWorkbookQuery オブジェクトにある．

CLASS	MEMBER	Type of Return
WorkbookQuery	Application	Application
	Creator	XlCreator
	Delete	Sub
	Description	String
	Formula	String
	Name	String
	Parent	Object

テーブルを表現するオブジェクト

ListObjects コレクション

CLASS	MEMBER	Type of Return
ListObjects	Add	ListObject
	Application	Application
	Count	Long
	Creator	XlCreator
	Item	ListObject
	Parent	Object

ListObject オブジェクト

CLASS	MEMBER	Type of Return
ListObject	Active	Boolean
	AlternativeText	String
	Application	Application
	AutoFilter	AutoFilter
	Comment	String
	Creator	XlCreator
	DataBodyRange	Range
	Delete	Sub
	DisplayName	String
	DisplayRightToLeft	Boolean
	ExportToVisio	Sub
	HeaderRowRange	Range
	InsertRowRange	Range
	ListColumns	ListColumns
	ListRows	ListRows
	Name	String
	Parent	Object
	Publish	String
	QueryTable	QueryTable
	Range	Range
	Refresh	Sub
	Resize	Sub
	SharePointURL	String
	ShowAutoFilter	Boolean
	ShowAutoFilterDropDown	Boolean
	ShowHeaders	Boolean
	ShowTableStyleColumnStripes	Boolean
	ShowTableStyleFirstColumn	Boolean
	ShowTableStyleLastColumn	Boolean
	ShowTableStyleRowStripes	Boolean
	ShowTotals	Boolean
	Slicers	Slicers
	Sort	Sort
	SourceType	XlListObjectSourceType
	Summary	String
	TableObject	TableObject
	TableStyle	Variant
	TotalsRowRange	Range
	Unlink	Sub
	Unlist	Sub
	XmlMap	XmlMap

ListObjects.Add メソッドの引数

　ListObjects.Add メソッドの戻り値は ListObject であるが，決まった引数を取る．SourceType, Source, LinkSource, XlListObjectHasHeaders, Destination, TableStyleName である．

SourceType

CLASS	MEMBER	CONST
XlListObjectSourceType	xlSrcExternal	0
	xlSrcRange	1
	xlSrcXml	2
	xlSrcQuery	3
	xlSrcModel	4

Source

Provider

Data Source

Location

Extended Properties

Destination

QueryTables コレクション

　次に述べる QueryTable オブジェクトの集合である．

QueryTable オブジェクト

　外部データソースから返されるデータに基づいて作成されたワークシートテーブルを表現するのが QueryTable オブジェクトである．外部データベースとは SQL Server または Microsoft Access などを指す．

　QueryTable オブジェクトのメンバーは膨大である．67 件あり，メソッドが 6 件，プロパティが 61 件あるが，その中でも重要なのが次に述べる CommandType プロパティと CommandText プロパティである．

ユーザーインターフェースか，オブジェクトモデルか

　外部データをインポートする際に，ユーザーインターフェース（手作業）を経由するかオブジェクトモデル (VBA) を経由するかで，QueryTable オブジェクトを生成するか ListObject オブジェクトを生成するかが決まる．

　文章にするとややこしいので，下表をみていただきたい．ユーザーインターフェースかオブジェクトモデルかで挙動が微妙に変化している．

	Web or Text Query	Other All
User Interface	QueryTable	ListObject
Object Model	QueryTable	QueryTable or ListObject

QueryTable.CommandType プロパティ

　公式情報はこちらである．取得または設定する定数によって CommandText プロパティの値を説明する．既定値は xlCmdSQL である．

CLASS	MEMBER	CONST
XlCmdType	xlCmdCube	1
	xlCmdSql	2
	xlCmdTable	3
	xlCmdDefault	4
	xlCmdList	5
	xlCmdTableCollection	6
	xlCmdExcel	7
	xlCmdDAX	8

QueryTable.CommandText プロパティ

　公式情報はこちらである．指定したデータソース用のコマンド文字列を返すか設定する．OLEDB データソースの場合は CommandTextプロパティの値を取得する．ODBC データソースの場合は設定するとデータが更新される．

　コマンド文字列は SQL ステートメントである．

　CommandTextプロパティには ListObject の QueryTable プロパティを使用してアクセスする必要がある．つまり，Web クエリとテキストクエリではこのプロパティは使用しない．

クエリプロパティ

クエリと接続

マウスポインタを載せるとプレビューがフロー表示される

　下図のように「クエリと接続」の上にマウスのポインタを持ってくるとプレビューがフロー表示される．

プレビューの下部にあるクエリプロパティ

　プレビュー下部の「…」をクリックするとプルダウン表示される「プロパティ」をクリックして，クエリプロパティに到達できる．

「読み込み先…」から「データのインポート」

　プレビュー下部の「…」をクリックするとプルダウン表示される「読み込み先…」をクリックすると「データのインポート」に到達する．

「読み込み先...」をクリックすると「データのインポート」に遷移する — 「読み込み先…」をクリックすると「データのインポート」に遷移する

Power Query エディター

　プレビューの下部「…」の左隣の「編集」をクリックすると Power Query エディター画面に遷移する．

クエリプロパティの変更のマクロ記録

「ファイルを開く時にデータを更新する」をオンにした場合

　.RefreshOnFileOpen プロパティの値が True となる．

    With ActiveWorkbook.Connections("クエリ - STFC2015").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("SELECT * FROM [STFC2015]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=STFC2015;Extended Properties="""""
        .RefreshOnFileOpen = True
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("クエリ - STFC2015")
        .Name = "クエリ - STFC2015"
        .Description = "ブック内の 'STFC2015' クエリへの接続です。"
    End With

「ブックを保存する前に外部データ範囲からデータを削除する」をオンにする

　このプロパティが見つからない．

    With ActiveWorkbook.Connections("クエリ - STFC2015").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("SELECT * FROM [STFC2015]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=STFC2015;Extended Properties="""""
        .RefreshOnFileOpen = True
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("クエリ - STFC2015")
        .Name = "クエリ - STFC2015"
        .Description = "ブック内の 'STFC2015' クエリへの接続です。"
    End With

「高速なデータ読み込みを有効にする」をオンにする

　このプロパティは Queries.FastCombine プロパティである．このプロパティは不思議である．通常，コレクションに付随するメンバーは Add メソッド，Item メソッド，Application プロパティ，Count プロパティ，Creator プロパティ，Parent プロパティくらいである．

　しかし，この FastCombine プロパティは WorkbookQuery オブジェクトに付随するのではなく，その集合である Queries コレクションに付随している．何か理由があるのかもしれない．

    With ActiveWorkbook.Connections("クエリ - STFC2015").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("SELECT * FROM [STFC2015]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=STFC2015;Extended Properties="""""
        .RefreshOnFileOpen = True
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("クエリ - STFC2015")
        .Name = "クエリ - STFC2015"
        .Description = "ブック内の 'STFC2015' クエリへの接続です。"
    End With

「定期的に更新する」をオンにする

　.RefreshPeriod = 60 のように更新周期を分で指定する．

    With ActiveWorkbook.Connections("クエリ - STFC2015").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("SELECT * FROM [STFC2015]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=STFC2015;Extended Properties="""""
        .RefreshOnFileOpen = True
        .RefreshPeriod = 60
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("クエリ - STFC2015")
        .Name = "クエリ - STFC2015"
        .Description = "ブック内の 'STFC2015' クエリへの接続です。"
    End With

.OLEDBConnection

　上記サンプルコードから，オブジェクトブラウザーで調査すべき項目は OLEDBConnection オブジェクトであることが分かる．

MEMBER	Property/Method	Type if Return
ADOConnection	Property	Object
AlwaysUseConnectionFile	Property	Boolean
Application	Property	Application
BackgroundQuery	Property	Boolean
CalculatedMembers	Property	CalculatedMembers
CancelRefresh	Method	Sub
CommandText	Property	Variant
CommandType	Property	XlCmdType
Connection	Property	Variant
Creator	Property	XlCreator
EnableRefresh	Property	Boolean
IsConnected	Property	Boolean
LocalConnection	Property	Variant
LocaleID	Property	Long
MaintainConnection	Property	Boolean
MakeConnection	Method	Sub
MaxDrillthroughRecords	Property	Long
OLAP	Property	Boolean
Parent	Property	Object
Reconnect	Method	Sub
Refresh	Method	Sub
RefreshDate	Property	Date
Refreshing	Property	Boolean
RefreshOnFileOpen	Property	Boolean
RefreshPeriod	Property	Long
RetrieveInOfficeUILang	Property	Boolean
RobustConnect	Property	XlRobustConnect
SaveAsODC	Method	Sub
SavePassword	Property	Boolean
ServerCredentialsMethod	Property	XlCredentialsMethod
ServerFillColor	Property	Boolean
ServerFontStyle	Property	Boolean
ServerNumberFormat	Property	Boolean
ServerSSOApplicationID	Property	String
ServerTextColor	Property	Boolean
SourceConnectionFile	Property	String
SourceDataFile	Property	String
UseLocalConnection	Property	Boolean