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に接続
SQL Server Management StudioからUbuntu上のSQL Serverに接続

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

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

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

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

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

最初にサーバー名を入力

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

サーバーを指定.ここではIPアドレスで指定している
サーバーを指定.ここではIPアドレスで指定している

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

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

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

最後にテーブルを指定

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

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

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

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

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

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

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

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

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 エディター画面に遷移する.

「編集」をクリックするとPower Queryエディタに遷移する
「編集」をクリックするとPower Queryエディタに遷移する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

.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

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください