ファイルを閉じたままの外部参照で最終行の行数取得

質問

こんにちは。VBA初心者、の段階から脱出を試みているものです。

ここやその他サイトを調べて、EXCELファイルを閉じたまま外部参照するには、Application.ExecuteExcel4Macroを使えばいいことがわかったのですが、参照先の最終行などの情報を取得するにはどうしたらいでしょうか。

私が調べた限りでは、以下のような単純に値を取得する方法しか説明されておらず、VBEのヘルプにも詳しい説明がありませんでした。

Cells(1, 1) = Application.ExecuteExcel4Macro _
("'C:\My Documents\[Book2.xls]Sheet1'!R1C1")

参照先から値を取得する際に、必要な範囲だけ見るようにしてできるだけ速く処理できるようにしたいと思っています。
（したがって、 65535行目までループするとか、OPENメソッドで一旦ファイルを開くような手法は避けたいと思っています）

なにかいい方法がありましたらご教示いただけないでしょうか。
よろしくお願いします。

回答No.2

tossy2011 · ベストアンサー

Application.ExecuteExcel4Macroに関して詳しくありませんが
メモリ上に展開してもよいなら下記の形で取得できると思います。

Sub test()
Dim appExcel As Object
Dim sh As Worksheet
Dim row As Integer

Set appExcel = GetObject("C:\Book1.xls")
row = appExcel.Worksheets(1).UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious).row
Set appExcel = Nothing

End Sub

お礼

ご回答ありがとうございます。

このメモリ上に展開する手法は、さまざまな場面で応用が効きそうですね！

実は最終行を取得するのは第1ステップで、その後応用していろいろと処理するつもりだったので助かります。

FINDメソッドの部分も、検索対象をワイルドカードにして、Afterの部分を省略、SearchDirection を xlPreviousにすると最終行の値にヒットするというのも自分では決して思い付きませんでした（ちなみにLookInの部分はXlValueでもちゃんと動いたので、どちらでもいいんですよね？）。

大変勉強になりました。

回答No.4

Wendy02

Sub Test1()
　Dim fn As String
　Dim a As Long, b As Long
　Dim ret As Variant
　Dim frml As String
　fn = "'C:\My Documents\Excel\[TEST1.xls]"
　frml = fn & "Sheet1'!C1"
　On Error Resume Next
　'数字検索
　a = Application.ExecuteExcel4Macro("MATCH(" & 10 ^ 10 & "," & frml & ",1)")
　'文字検索（ただし、"*"があればヒットしてしまいます。その場合は、"熙"のような文字が良い）
　b = Application.ExecuteExcel4Macro("MATCH(""*""," & frml & ",1)")
　On Error GoTo 0
　If a > b Then
　　ret = a
　Else
　　ret = b
　End If
　MsgBox ret
　'Cells(1,1).Value = ret
End Sub

私からの拙いアドバイス。

>VBA初心者、の段階から脱出を試みているものです。

こういう裏技から遠ざかることが、初心者からの脱却だと思います。しょせん、単発ワザは、裏技に過ぎません。もし、学ぶなら、Ver.4 マクロ関数、Ver.5 のテクニックを知っておくと良いのですが、今は、資料などはありません。また、それを学んでも、今では、あまり生産的ではありません。

初心者から離脱は、基本の積み重ねしかないと思います。それも、ほんのちょっとしたことで、暗黙のプロパティを使って、Cells(1, 1) ではなく、Cells(1, 1).Value とか、変数のデータ型の決め方とか、分かっているようで分からないことを覚えて、毎日、コードを書くことです。

お礼

ご回答、およびアドヴァイスありがとうございました。

プログラミング自体初心者みたいなところから始めて、参考書やWEBの情報を読みなら全くの独学でやっているので、結構基本的なところが抜けてしまっているかと思います。

たとえ意図したとおりに動いたとしても、わかってる人からみればよろしくない部分の多々あることでしょう。

#2のような回答をいただいても、ちょっと前の自分だったら、Set appExcel = GetObject("C:\Book1.xls")とオブジェクトを変数に格納せずに、いきなりGetObject("C:\Book1.xls").Worksheets(1).UsedRange.Findのようなかたちで応用していたかもしれません。

なぜオブジェクトを一旦変数に格納すべきなのかは、いまだにちゃんと理解していなのですが…
（「たまにエラーになることもあるから」というレベルです、正直なところ）

回答No.3

chibita_papa

#1です

>今調べているのは、外部参照のファイルを閉じたままで同じ結果を得るにはどうしたら良いのかということです。

Range("A1").Value = "=COUNTA('C:\My Documents\[Book2.xls]Sheet1'!$A$1:$A
$65536)"

これは、セルA1にBook2.XLSのレコード数を計算する式を入力すると言うコードです。
質問者さんが仰るように、Book2.XLSはオープンしていないですが。
的外れな回答でしたらすみません。

お礼

度々ありがとうございます。

A1セルに「ワークシート関数」を入力するというわけですね。ナルホドです。
ExecuteExcel4MacroはセルをR1CI形式で参照しなければならないと説明されていたので、質問の意図が伝わっていないかと思ってしまいました。

確かにワークシート関数COUNTAを書けば、ファイルを閉じたままでも（空白セルがない）という前提で最終行が取得できますね。

回答No.1

chibita_papa

'C:\My Documents\[Book2.xls]Sheet1'のＡ列には空白の行がなければ
(B列でもC列でも良いのですが)

Range("A1").Value = "=COUNTA('C:\My Documents\[Book2.xls]Sheet1'!$A$1:$A$65536)"
アクティブシートのA1 に　Book2.xls の　行数を書き込む

ではどうでしょうか

補足

早速のご回答ありがとうございます。

質問の仕方が良くなかったのかもしれませんが、普通に最終行を調べるのなら
Cells(Rows.Count, 1).End(xlUp).Row
や
Range("A1").SpecialCells(xlLastCell)
などの方法があるのは存じております。

今調べているのは、外部参照のファイルを閉じたままで同じ結果を得るにはどうしたら良いのかということです。

以下のようなコードを試してみたのですが、前者は結果が「#NAME?」となり、後者はコードの実行時エラーになってしまいます。

Cells(1, 1) = Application.ExecuteExcel4Macro _
("'D:\sample\[sample.xlsm]Sheet1'!R1C1.SpecialCells(xlLastCell)")

Cells(1, 2) = Application.ExecuteExcel4Macro _
"'D:\sample\[sample.xlsm]Sheet1'!R65535C1.End(xlUp).Row")

ExecuteExcel4Macroでなくてもよいので、なにか方法はないでしょうか。