こんにちは。VBA初心者、の段階から脱出を試みているものです。
ここやその他サイトを調べて、EXCELファイルを閉じたまま外部参照するには、Application.ExecuteExcel4Macroを使えばいいことがわかったのですが、参照先の最終行などの情報を取得するにはどうしたらいでしょうか。
私が調べた限りでは、以下のような単純に値を取得する方法しか説明されておらず、VBEのヘルプにも詳しい説明がありませんでした。
Cells(1, 1) = Application.ExecuteExcel4Macro _
("'C:\My Documents\[Book2.xls]Sheet1'!R1C1")
参照先から値を取得する際に、必要な範囲だけ見るようにしてできるだけ速く処理できるようにしたいと思っています。
(したがって、 65535行目までループするとか、OPENメソッドで一旦ファイルを開くような手法は避けたいと思っています)
なにかいい方法がありましたらご教示いただけないでしょうか。
よろしくお願いします。
No.2ベストアンサー
- 回答日時:
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
- 回答日時:
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
- 回答日時:
#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
- 回答日時:
'C:\My Documents\[Book2.xls]Sheet1'のA列には空白の行がなければ
(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でなくてもよいので、なにか方法はないでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) マクロVBA 1シートをまとめる 閉じ方 初心者 SOS! 1 2022/06/17 14:54
- Visual Basic(VBA) 貼り付けた値が消えていく 以下はソースファイルの2番目のシートのB6から最終行を取得 ターゲットファ 2 2023/07/27 12:23
- Visual Basic(VBA) 別ブックの列同士の値が一致したときの処理 1 2022/09/03 08:27
- Excel(エクセル) 【マクロ】同じフォルダ内にある複数ブックから1つのブック内の1シートにデータを集めたい 6 2022/09/28 18:16
- Visual Basic(VBA) 複数csvを横に追加していくマクロについて 2 2023/04/25 09:19
- Excel(エクセル) エクセルでのアクセス履歴をとりたいです 1 2022/06/13 17:30
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Visual Basic(VBA) ExcelからAccessのテーブルに書き込む時に時間がかかる 1 2022/10/14 20:38
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
WorkBooksをオープンさせずにシートにコピーしたい【EXCEL VBA】
Excel(エクセル)
-
VBA ブックを開かずにブック内のデータを取得する方法
Visual Basic(VBA)
-
VBA ExecuteExcel4Macro 型が一致しません
Excel(エクセル)
-
-
4
参照先のブックを開かずに内容をコピーしたい
Excel(エクセル)
-
5
VBA 別ブックからの転記の高速化について VBA 別ブックからの転記の高速化についてご教授下さい。
Visual Basic(VBA)
-
6
VBAで複数のブックを開かずに処理する方法
Visual Basic(VBA)
-
7
VBA EXCEL 開かずにデータ操作
その他(Microsoft Office)
-
8
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
9
Excel VBA、 別ブックの最終行セルへのコピー&値ペースト
Visual Basic(VBA)
-
10
【VBA】EXCELブックを開かずにシート名を取得したい
Visual Basic(VBA)
-
11
Excel VBA ExecuteExcel4Macroについて
その他(Microsoft Office)
-
12
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
13
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
14
エクセルVBA 配列からセルに「関数式」を一気代入したい
Visual Basic(VBA)
-
15
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
16
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
-
17
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
18
エクセルVBAで5行目からオートフィルタモードに設定したいたい
Excel(エクセル)
-
19
csvファイルを開かずに文字を検索し行を抽出したい
Visual Basic(VBA)
-
20
【Excel VBA】CSV取込時、数字の先頭の0を消えないようにするには?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Vba SelStart、SelLen教えてく...
-
【VBA】マクロの入ったファイル...
-
A列に記載されているフォルダ...
-
VBA listBoxから
-
ExcelVBA シート名を複数セルか...
-
現在のブックを閉じないで、マ...
-
Outlookの「受信日時」「送信者...
-
ユーザーフォームに別シートか...
-
VBAコンボボックスで選択した値...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
Outlookの「受信日時」「件名」...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
Excelのマクロでワードのテキス...
-
【ExcelVBA】インデックスが有...
-
VBA 別ブックからコピペしたい...
-
VBA実行後に元のセルに戻りたい
-
VBAに詳しい方教えてください。
-
エクセルのマクロについて教え...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAのコードを教えてください
-
【ExcelVBA】インデックスが有...
-
ExcelVBA シート名を複数セルか...
-
エクセルvbaについて
-
エクセルのマクロについて教え...
-
【VBA】マクロの入ったファイル...
-
VBA UserFormからの転記で
-
エクセルVBAの配列について
-
Excelで「Ctrl+c」、「Ctrl+v...
-
VBAコードについて教えてくださ...
-
ExcelのVBAコードについて教え...
-
Excel マクロについての相談
-
VBAで質問があります
-
VBAコードについて
-
【ExcelVBA】VBA実行でダイアロ...
-
Excel関数またはVBAでの質問に...
-
ExcelのVBAコードについて教え...
-
ExcelのVBAコードについて教え...
-
ExcelのVBAコードについて教え...
-
Outlookの「受信日時」「件名」...
おすすめ情報