リストボックスにExecuteExcel4Macroで取得したデータを表示したいのですが

質問

インターネットと参考書を利用して独学で仕事用ＶＢＡを作成している者です。

素人なりにいろいろ試してはいるのですがうまくいかないのでご教授願えたらと思い投稿しました。
質問は２つ＋α　で、
１．そもそも表題にあることが可能なのか？不可能なのか？
２．可能であるならどの辺りを修正したら良いのか？
です。
＋α　というのは、恥ずかしながら具体的な提示です。
よろしくおねがいします。
Private Sub CommandButton3_Click()

TextBox1 = Sheets("Sheet1").Range("A1").Value

Dim DYear
Dim mypath
Dim dateFpath

DYear = TextBox1.Text
mypath = ActiveWorkbook.Path
dateFpath = mypath & "\Date" & DYear
ChDir mypath & "\Date" & DYear 'dateFpath 'カレントフォルダ

Dim FCnt As Integer
FCnt = 0
Dim dateName
dateName = Dir(DYear & "証票*.xls")
Dim sx As Long 'シートの列番号

ListBox1.ColumnCount = 6 '3列表示
ListBox1.ColumnWidths = "100 pt;35 pt;35 pt;35 pt;35 pt;35 pt;35 pt;35 pt;35 pt" '表示する列の幅
Dim nen
nen = "年月日"
Dim ji
ji = "時"
Dim fun
fun = "分"
Do While dateName <> ""
FCnt = FCnt + 1
sx = 4
dateName = Dir()
With ListBox1
.AddItem

.List(FCnt, 0) = dateName '１列目

.List(FCnt, 1) = nen '２列目
sx = sx + 1
.List(FCnt, 2) = Application.ExecuteExcel4Macro("'" & dateFpath & "\[" & dateName & "]Sheet1'!R4C" & sx).Value '３列目
sx = sx + 1
.List(FCnt, 3) = Application.ExecuteExcel4Macro("'" & dateFpath & "\[" & dateName & "]Sheet1'!R4C" & sx).Value '４列目
sx = sx + 1
.List(FCnt, 4) = Application.ExecuteExcel4Macro("'" & dateFpath & "\[" & dateName & "]Sheet1'!R4C" & sx).Value '５列目
sx = sx + 2
.List(FCnt, 5) = Application.ExecuteExcel4Macro("'" & dateFpath & "\[" & dateName & "]Sheet1'!R4C" & sx).Value '６列目
sx = sx + 1
.List(FCnt, 6).Value = ji '７列目

.List(FCnt, 7) = Application.ExecuteExcel4Macro("'" & dateFpath & "\[" & dateName & "]Sheet1'!R2C" & sx).Value '８列目
sx = sx + 1
.List(FCnt, 8).Value = fun '９列目
End With
Loop
MsgBox FCnt & "個のデータが見つかりました"
End Sub

以上です。
ループ１順目の３列目で停止してしまいます。
変数の値は確認しています。
よろしくお願いします。

回答No.2

onlyrom · ベストアンサー

一見しただけですが、基本的な間違いがいくつかあるようです。

'--------------------------------------------------

●Application.ExecuteExcel4Macro　の最後のVALUEは不要。

それが、
＞ループ１順目の３列目で停止してしまいます。
の原因です。
ということは、４，５，６，８列目でもエラーがでるということです。

'--------------------------------------------------

●dateName = Dir()　の位置が違う

＞.List(FCnt, 8).Value = fun '９列目
＞End With
　　▲dateName = Dir()
＞Loop

▲の位置でないといけない
理由は実行してみれば分かります。

'------------------------------------------------

●コードは９列まであるにも拘わらず、
＞ListBox1.ColumnCount = 6　　'3列表示
と、列は６に設定されている
これも修正しておいた方がいいでしょう。

'-------------------------------------------------

この３つを修正して実行してみてください。
そのうえでまだ疑問点があれば再質問のこと。
以上。

お礼

onlyrom様　ご回答ありがとうございます。
エラーで止まってしまうために　本当にいろいろ試しに試しすぎた結果このように余計なものがいろいろ付いてしまったと気が付きました。
が、ご指摘のとおりループが止まらずにとりあえず
if dateName = ""
exit sub
end if
こんなものを咬ませていたしだいです。
ありがとうございました。

回答No.1

imogasi

>ExecuteExcel4Macro
（１）ExecuteExcel4Macroを使う人が出てくるのは、エクセルの関数の中にそういう関数が無い場合にやむを得ずこれで回答する人が居る。
例
http://www.relief.jp/itnote/archives/001522.php
ブック名を表示する－4.0マクロ関数
（２）ファイルを閉じたまま、データを取り出すこと、をしたい。
マクロは一種のプログラムですからＭＳが作ればなんとでもできるわけです。
http://okwave.jp/qa1120469.html
に紹介の
http://www.officetanaka.net/excel/vba/tips/tips28.htm
http://homepage1.nifty.com/gak/MSTips/getfromclosedfile.html
ＧｏｏｇｌｅでExecuteExcel4Macroで紹介すれば相当数記事が出る。
ーー
本件は（２）の利用か？
ブックを開いてはダメなのか？
4.0マクロは、きわどいものだと思うが。
ーーーー
忙しい回答者にこんな長いコードを解読させるのでなく、文章でポイントを表現した上で質問されることを希望する。
質問用に別途関係ない部分は省く例を作るぐらいの、気配りがほしい。エラーに関係ないこと、列挙型の複数繰り返しなど１つで良いではないですか。例　List(FCnt, 3) =以下３，４回の繰り返し
＞ループ１順目の３列目で停止してしまいます
どこのことか探すのも大変。
どういうエラーコードかも参考に載せるべき。

お礼

imogasi様　お忙しい中ありがとうございました。
回答の方を頂きながらも返事が遅くなったことをお詫び申しあげます。
（１）ブック名を表示することに関しては４マクロではなともできることは学ばせていただきました。リストボックスへ表示することもできるようになりました。そこで少し欲が出てしまったことに端を発します。
（２）初めはオープンブックでデータの抽出をしていました。
３００近くあるブックブックを開いては閉じ、また開いては閉じを繰り返す時間は以外に長く”シートの検索”のように瞬間で終わるものとは雲泥の差（手作業に比べたら比較するほどの差はないのでしょうが）。
提示いただいたリンク先を見させていただきました。

＞「ファイルを閉じたまま、データを取り出すこと」はこれ以外ないのか、載ってますね
​＞http://www.officetanaka.net/excel/vba/tips/tips28.htm​
開いたＨＰのなかの構文のなかの１行ですが
　MsgBox ExecuteExcel4Macro("'C:\[Book1.xls]Sheet1'!R1C1")
　これ↑が
　MsgBox Application.ExecuteExcel4MacroExecuteExcel4Macro("'C:\[Book1.xls]Sheet1'!R1C1")
　これ↑とが　どう違うのかは勉強不足でわかりません。
　それと
Sub Test()
For i = 1 To 900
　Range("A" & i).Value = _
　　　　"='" & ThisWorkbook.Path & _
　　　　"\[Book" & i & ".xls]Sheet1'!B3"
　Range("A" & i).Value = Range("A" & i).Value
Next i
End Sub
上記のものも途中の文は結局は'C:\[Book1.xls]Sheet1'!R1C3")こんな感じで（イコールが被っているのが理解できませんが）４マクロ以前の文字があるかないか程度の違いしかわかりません。
ついでに付け足させていただくと、
＞4.0マクロ関数じゃないけど、似たような処理の回答をさっきもしました。
という内容に、
q = " '" & DrPh & "\[" & s & "]sheet1'!" & ITI
Da = Application.ExecuteExcel4Macro(q)
こんなものもありました。
これらは結局４マクロではないんですか？理解に苦しみます。