Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


55770 / 76732 ←次へ | 前へ→

【25729】Re:参照先Bookを開かないでVlookup
発言  ichinose  - 05/6/12(日) 18:19 -

引用なし
パスワード
   じぇっと さん、kobasanさん、こんにちは。


>できれば、「Application.VLookup」を使用したいのです。
>参照先の他のブック(C:\Book2.xls)は数万KBという大きなサイズのファイルなので、Excel関数の「VLookup」を使用した場合、表示まで(処理)にかなりの時間を要してしまうのです。
>ですので、「Application.VLookup」を使用してVBA内で処理を行えば少しは処理時間も早くなるかなと思ったのですが、もしかしてExcel関数の「VLookup」を使用しても「Application.VLookup」を使用しても、処理時間は全く変わらないのでしょうか?
Application.Vlookupでは、開いていないブックの参照は出来ないですね!!
それと、対照ブック(ここではC:\Book2.Xls)を開かないで参照する方法は、
このブックを開いて参照するよりリソースを消費するようですよ!!
ですから、Workbooks.Openで開いた後に参照するという手順をお奨めしますが・・。

他に何か方法はないかと考えるとADOを使用した方法です。
(あくまでもWorkbooks.Openと言う形式で開いてないという意味で
ADOであろうが、Formula="='c:\[Book2.xls]Sheet1'!$A$1"にしても
どこかでOpenされています。ここでは、ExcelのWorkbookオブジェクトとして
開かないと言う意味で模索しています)

仮に「c:\Book2.xls」のSheet1といシートに

     A   B
 1  NO  名称
 2   1    a
 3   2    b
 4   3    d
 5   4    e
 6   5    f
 7   6    g
 8   ・
 9   ・


なんていうデータが入っていたとします。
(もっともこれだとADOなんて使うまでもなさそうですが、
 簡単な例題として)

以下のマクロを含むブックを「c:\book1.xls」だとすると、このブックの
標準モジュールに

'===================================================
Sub main()
  Dim rs As Object
  Dim sql_str As String
  Dim retcode As Long
  retcode = open_ado_excel(ThisWorkbook.Path & "\book2.xls")
'       ADOでExcelブックBook2.Xlsに接続
  If retcode = 0 Then
    f_num = Application.InputBox("input find number")
    '↑ 検索するナンバーを入力
    If TypeName(f_num) <> "Boolean" Then
     sql_str = "select [名称] from [Sheet1$] where [NO] = " & f_num & ";"
     'ナンバーを検索するSQLの作成
     Set rs = exec_sql(sql_str, retcode) 'SQLの実行
     If retcode = 0 Then
       If rs.EOF <> True Then ' 見つかった
        MsgBox rs![名称]
       Else '見つからない
        MsgBox "not found"
        End If
       rs.Close
       Set rs = Nothing
     Else
       MsgBox Error$(retcode)
       End If
     End If
    call close_ado()
  Else
    MsgBox Error(retcode)
    End If
End Sub

別の標準モジュールに
'=============================================================
Public cn As Object 'コネクションオブジェクト
'=============================================================
Function open_ado_excel(book_fullname As String) As Long
'ADOでExcelブックに接続する
'in book_fullname -- 接続するブックのフルパス
'ot open_ado_excel-- リターンコード 0−正常 その他--エラー
  On Error Resume Next
  Set cn = CreateObject("ADODB.Connection")
  link_opt = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & book_fullname & ";" & _
       "Extended Properties=Excel 8.0;"
  cn.Open link_opt
  open_ado_excel = Err.Number
  On Error GoTo 0
End Function
'=================================================
Sub close_ado()
'接続したExcelブックの切断
  On Error Resume Next
  cn.Close
  Set cn = Nothing
  On Error GoTo 0
End Sub
'=================================================
Function exec_sql(sql_str, retcode) As Variant
'SQLの実行
'in : sql_str --- 実行するSQL
'ot : retcode ---リターンコード 0−正常 その他--エラー
'exec_sql--------SQLを実行した結果
'        今回は、Recordsetオブジェクトを返す
On Error Resume Next
  Set exec_sql = Nothing
  Set exec_sql = cn.Execute(sql_str)
  retcode = Err.Number
  On Error GoTo 0
End Function

これでBook2.XlsのSheet1のA列のNOを検索し、
結果として、名称を返すコードです。


それとそんなに大きいデータなら、本格的なDBにデータを移行する事も
検討されても良いと思いますが・・・。

まっ、試してみて下さい。
0 hits

【25700】参照先Bookを開かないでVlookup じぇっと 05/6/11(土) 2:44 質問
【25702】Re:参照先Bookを開かないでVlookup kobasan 05/6/11(土) 7:48 回答
【25703】Re:参照先Bookを開かないでVlookup kobasan 05/6/11(土) 7:57 発言
【25705】Re:参照先Bookを開かないでVlookup じぇっと 05/6/11(土) 10:19 質問
【25706】Re:参照先Bookを開かないでVlookup kobasan 05/6/11(土) 12:14 回答
【25710】Re:参照先Bookを開かないでVlookup kobasan 05/6/11(土) 14:39 発言
【25722】Re:参照先Bookを開かないでVlookup kobasan 05/6/11(土) 22:09 発言
【25729】Re:参照先Bookを開かないでVlookup ichinose 05/6/12(日) 18:19 発言
【25730】Re:参照先Bookを開かないでVlookup kobasan 05/6/12(日) 21:41 発言
【25731】Re:参照先Bookを開かないでVlookup じぇっと 05/6/12(日) 23:21 お礼

55770 / 76732 ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free