Excel VBA質問箱 IV

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

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


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

9737 / 13644 ﾂﾘｰ

←次へ　｜　前へ→

▼

【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


	【25700】参照先Bookを開かないでVlookup

じぇっと - 05/6/11(土) 2:44 -


	引用なし

	パスワード

いつも参考にさせて頂いています。
どなたかご存知の方がいらっしゃいましたらご教授願います。

OS:WindowsNT4.0
Ver:Excel97

下記の様なコード（Excel関数）を使用すると、カレントブックから他のブックを開かないで「Vlookup」を使用することができます。

Range("B1") = "=VLOOKUP(A1,'C:\[Book2.xls]Sheet1'!A:B,2)"

上記と同じ様に「Application.VLookup」を使用して、カレントブックから他のブックを開かないで「Vlookup」を実行することはできないでしょうか？

Range("B1") = Application.VLookup(Range("A1"), Workbooks("C:\Book2.xls").Worksheets("Sheet2").Range("A:B"), 2)　　　　　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ここがおかしいと睨んでます。


	【25702】Re:参照先Bookを開かないでVlookup

kobasan - 05/6/11(土) 7:48 -


	引用なし

	パスワード

▼じぇっとさん：
おはようございます。

>下記の様なコード（Excel関数）を使用すると、カレントブックから他のブックを開かないで「Vlookup」を使用することができます。
>
>Range("B1") = "=VLOOKUP(A1,'C:\[Book2.xls]Sheet1'!A:B,2)"
>
>上記と同じ様に「Application.VLookup」を使用して、カレントブックから他のブックを開かないで「Vlookup」を実行することはできないでしょうか？
>
>Range("B1") = Application.VLookup(Range("A1"), Workbooks("C:\Book2.xls").Worksheets("Sheet2").Range("A:B"), 2)　　　　　　　　　　

　　　　　　　　　　　　↑
FormulaR1C1 形式ですが、

　　Range("B1").FormulaR1C1 = "= VLookup(R1C1,'D:\[book2.xls]Sheet2'!R1C1:R10C2,2,false)"
　　Range("B1").Value = Range("B1").Value


	【25703】Re:参照先Bookを開かないでVlookup

kobasan - 05/6/11(土) 7:57 -


	引用なし

	パスワード

修正してください。
>
R1C1:R10C2
を
R1C1:R65536C2
にしてください。


	【25705】Re:参照先Bookを開かないでVlookup

じぇっと - 05/6/11(土) 10:19 -


	引用なし

	パスワード

すいません。もう一度質問させて頂きます。
意味の分からないことを質問していた場合は、ご容赦願います。

できれば、「Application.VLookup」を使用したいのです。
参照先の他のブック（C:\Book2.xls）は数万KBという大きなサイズのファイルなので、Excel関数の「VLookup」を使用した場合、表示まで（処理）にかなりの時間を要してしまうのです。
ですので、「Application.VLookup」を使用してVBA内で処理を行えば少しは処理時間も早くなるかなと思ったのですが、もしかしてExcel関数の「VLookup」を使用しても「Application.VLookup」を使用しても、処理時間は全く変わらないのでしょうか？


	【25706】Re:参照先Bookを開かないでVlookup

kobasan - 05/6/11(土) 12:14 -


	引用なし

	パスワード

▼じぇっとさん：
>すいません。もう一度質問させて頂きます。
>意味の分からないことを質問していた場合は、ご容赦願います。
>
>できれば、「Application.VLookup」を使用したいのです。
>参照先の他のブック（C:\Book2.xls）は数万KBという大きなサイズのファイルなので、Excel関数の「VLookup」を使用した場合、表示まで（処理）にかなりの時間を要してしまうのです。
>ですので、「Application.VLookup」を使用してVBA内で処理を行えば少しは処理時間も早くなるかなと思ったのですが、もしかしてExcel関数の「VLookup」を使用しても「Application.VLookup」を使用しても、処理時間は全く変わらないのでしょうか？
Application.VLookup　で検討してみましたが、book2をopenする方法しか私にはできませんでした。
セル1つ1つに書き込むなら、関数の「VLookup」を使用を使用したほうが速いと思います。
私にできることは下記の代案です。かなり速いと思います。
Sub test()
　　Range("B1").FormulaR1C1 = "= VLookup(RC[-1],'D:\[book2.xls]Sheet2'!R1C1:R1000C2,2,false)"
　　Range("B1").AutoFill Destination:=Range("B1:B60000"), Type:=xlFillDefault

　　Range("B1:b1000").Value = Range("B1:b6000").Value
End Sub


	【25710】Re:参照先Bookを開かないでVlookup

kobasan - 05/6/11(土) 14:39 -


	引用なし

	パスワード

画面更新を止めて、
AutoFillを1行カットしました。
Sub test()
　　Application.ScreenUpdating = False
　　Range("B1:b20000").FormulaR1C1 = "= VLookup(RC[-1],'D:\[book2.xls]Sheet2'!R1C1:R2000C2,2,false)"
　　Range("B1:b20000") = Range("B1:b20000").Value
End Sub

2万件で5秒くらいです。


	【25722】Re:参照先Bookを開かないでVlookup

kobasan - 05/6/11(土) 22:09 -


	引用なし

	パスワード

こんばんは

>「Application.VLookup」を使用してVBA内で処理を行えば少しは処理時間も早くなるかなと
>思ったのですが、もしかしてExcel関数の「VLookup」を使用しても「Application.VLookup」
>を使用しても、処理時間は全く変わらないのでしょうか？

過去ログも検索して勉強してみたのですが、
ブックを開かずに、Application.VLookupを使って、高速に処理できる方法
を見つけることはできませんでした。
もっと力のある方を待ちたいと思います。

参考までに、検索したもののうち、２例を載せておきます。参考にしてください。

【9086】Re:ファイルを開かずにデータを取得
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=9079;id=excel

【17858】Re:ファイルを開かずにセルの値を取得　
http://www.vbalab.net/vbaqa/c-board.cgi?page=&no=17858&mode=tre&id=excel&cmd=jmp


	【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といシートに

　　　　　Ａ　　　Ｂ
　１　　ＮＯ　　名称
　２　　　1　　　　a
　３　　　2　　　　b
　４　　　3　　　　d
　５　　　4　　　　e
　６　　　5　　　　f
　７　　　6　　　　g
　８　　　・
　９　　　・

なんていうデータが入っていたとします。
（もっともこれだと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　--- 実行するＳＱＬ
'ot : retcode ---リターンコード　0－正常　その他--エラー
'exec_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のＡ列のＮＯを検索し、
結果として、名称を返すコードです。

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

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


	【25730】Re:参照先Bookを開かないでVlookup

kobasan - 05/6/12(日) 21:41 -


	引用なし

	パスワード

みなさん、こんばんは

>Application.Vlookupでは、開いていないブックの参照は出来ないですね！！
>

いつまでもVBAを勉強中のなので、これを言えるのは遙か彼方です。
安堵しました。


	【25731】Re:参照先Bookを開かないでVlookup

じぇっと - 05/6/12(日) 23:21 -


	引用なし

	パスワード

▼ichinose様･kobasan様：

お忙しい中、様々なご回答ありがとうございます。
お二人のご意見を参考にさせて頂きます！


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

9737 / 13644 ﾂﾘｰ

←次へ　｜　前へ→

ページ：

┃

記事番号：

(SS)C-BOARD v3.8 is Free