KB402329 - [XL]XLODBCアドイン関数(Charフィールドの数値データを文字列で取得する)

詳細

VBA で XLODBC アドイン関数を使用し、Char 型 (文字型) のフィールドの数値データを取得すると、Excel 5.0 は自動的に次のように変換を行います。

  0001        →  1
  1e1         →  1.00E+1
  1/2,94-0001 →  1 月 2 日,Jan-94

これは XLODBC.XLA がデータを取得する際に「数値データ」と認識するためで、SQL 文の中で、フィールド名を「=""」と「""」で囲むことにより文字列として取得することができます。

SQLServer の場合

  "SELECT '=""' + フィールド名 + '""' FROM テーブル名"

ORACLE の場合

  "SELECT '=""' || フィールド名 || '""' FROM テーブル名"

なお、文字列を連結する演算子 (SQL Server → +、ORACLE→ ||) はご使用のデータベースサーバアプリケーションにより異なります。詳細についてはマニュアルを参照していただくか、またはメーカー様へお問い合わせ下さい。

使用例

サンプルモジュールは「 SQL Server 」と「 ORACLE 」に対するものを用意しました。
それぞれのプログラム実行環境は次の通りです。

  [SQL Server]
    プログラム実行環境
        サーバー       SQLServer
        ODBC ドライバ  SQLServerODBC ドライバ (Excel5.0 添付)
        データソース名 TstSQL
    TstSQL の内容
        サーバー名     serversql1
        ユーザー ID    sa
        データベース   pubs
        パスワード     Excel
  [ORACLE]
    プログラム実行環境
        サーバー       ORACLE
        ODBC ドライバ  ORACLE Ver. 6 用 ODBC ドライバ (Access 添付)
        データソース名 TstORA
    TstORA の内容
        サーバー名     serverora1
        ユーザー ID    SCOTT
        パスワード     Tiger

XLODBC アドイン関数を使用したサンプルモジュールは次の通りです。
モジュールの記述で、次の記述で囲まれている部分は、紙面上では改行されていますが、実際のモジュールシートへの入力の際は改行してはいけないコードを示しています。ご注意ください。

    '以下の 2 行は改行せずにコードを入力してください。
    '上の 2 行は改行せずにコードを入力してください。

例

例えば、テーブル [売上] のフィールド [商品コード] に次のようなデータが入力されている場合、<サンプル 1> のマクロを実行すると Excel 5.0 は日付として認識できるデータについては「シリアル値」に変換してそれらのデータを扱います。これを「文字列」として認識させるには <サンプル 2> や<サンプル 3> で示すように SQL 文を定義して実行します。

  サーバー上のデータ
    95-0001
    10-0095
    94-0001
    1/2
  <サンプル 1> の実行結果
    Jan-95
    10-0095
    Jan-94
    1 月 2 日
  <サンプル 2>、<サンプル 3> の実行結果
    95-0001
    10-0095
    94-0001
    1/2

サンプル 1 (SQL Server)

  Sub MySQL()
  Dim Chan As Variant
  Dim NumRows, NumCols As Integer
      '接続と変数 Chan に接続 ID をセット
      '以下の 2 行は改行せずにコードを入力してください。
      Chan = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文作成
      sql2 = "SELECT 商品コード FROM 売上"
      'SQL 文実行
      SQLExecQuery Chan, sql2
      'Sheet1!A1 に結果を返す
      SQLRetrieve Chan, Range("Sheet1!A1")
      '切断
      SQLClose Chan
  End Sub

サンプル 2-1 フィールド名を含めない場合 (SQL Server)

  Sub CharValSQL()
  Dim Chan As Variant
  Dim NumRows, NumCols As Integer
      '接続と変数 Chan に接続 ID をセット
      '以下の 2 行は改行せずにコードを入力してください。
      Chan = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'フィールド名 [商品コード] のデータを =" "で囲まれた形で SQL 文定義
      sql2 = "SELECT '=""' + 商品コード + '""' FROM 売上"
      'SQL 文実行
      NumCols = SQLExecQuery(Chan, sql2)
      'Sheet1!B1 に結果を返す
      NumRows = SQLRetrieve(Chan, Range("Sheet1!A1"))
      '切断
      SQLClose Chan
      Range("Sheet1!A1").Resize(NumRows, NumCols).Copy
      '[形式を選択して貼り付け] で [文字数値] のみ貼り付け
      Range("Sheet1!A1").Resize(NumRows, NumCols).PasteSpecial xlValues
      Application.CutCopyMode = False
  End Sub

サンプル 2-2 フィールド名を含めない場合 (ORACLE)

  Sub CharValORA()
  Dim Chan As Variant
  Dim NumRows, NumCols As Integer
      '接続と変数 Chan に接続 ID をセット
      '以下の 2 行は改行せずにコードを入力してください。
      Chan = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;
      PWD=Tiger", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'フィールド名 [商品コード] のデータを =" "で囲まれた形で SQL 文定義
      sql2 = "SELECT '=""' || 商品コード || '""' FROM 売上"
      'SQL 文実行
      NumCols = SQLExecQuery(Chan, sql2)
      'Sheet1!B1 に結果を返す
      NumRows = SQLRetrieve(Chan, Range("Sheet1!A1"))
      '切断
      SQLClose Chan
      Range("Sheet1!A1").Resize(NumRows, NumCols).Copy
      '[形式を選択して貼り付け] で [文字数値] のみ貼り付け
      Range("Sheet1!A1").Resize(NumRows, NumCols).PasteSpecial xlValues
      Application.CutCopyMode = False
  End Sub

サンプル 3-1 フィールド名を含める場合 (SQL Server)

  Sub CharValFiSQL()
  Dim Chan As Variant
  Dim NumRows, NumCols As Integer
      '接続と変数 Chan に接続 ID をセット
      '以下の 2 行は改行せずにコードを入力してください。
  Chan = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'フィールド名 [商品コード] のデータを =" "で囲まれた形で SQL 文定義
      sql2 = "SELECT '=""' + 商品コード + '""' 商品コード FROM 売上"
      'SQL 文実行
      NumCols = SQLExecQuery(Chan, sql2)
      'Sheet1!B1 に結果を返す
      NumRows = SQLRetrieve(Chan, Range("Sheet1!A1"), , , True)
      '切断
      SQLClose Chan
      Range("Sheet1!A1").Resize(NumRows + 1, NumCols).Copy
      '形式を選択して貼り付けで文字数値のみ貼り付けます
      Range("Sheet1!A1").Resize(NumRows + 1, NumCols). _
          PasteSpecial xlValues
      Application.CutCopyMode = False
  End Sub

サンプル 3-2 フィールド名を含める場合 (ORACLE)

  Sub ChaValFiORA()
  Dim Chan As Variant
  Dim NumRows, NumCols As Integer
      '接続と変数 Chan に接続 ID をセット
      '以下の 2 行は改行せずにコードを入力してください。
      Chan = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;
      PWD=Tiger", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'フィールド名 [商品コード] のデータを =" "で囲まれた形で SQL 文定義
      sql2 = "SELECT '=""' || 商品コード || '""' 商品コード FROM 売上"
      'SQL 文実行
      NumCols = SQLExecQuery(Chan, sql2)
      'Sheet1!B1 に結果を返す
      NumRows = SQLRetrieve(Chan, Range("Sheet1!A1"), , , True)
      '切断
      SQLClose Chan
      Range("Sheet1!A1").Resize(NumRows + 1, NumCols).Copy
      '[形式を選択して貼り付け] で [文字数値] のみ貼り付け
      Range("Sheet1!A1").Resize(NumRows + 1, NumCols). _
      PasteSpecial xlValues
      Application.CutCopyMode = False
  End Sub