Excel VBA ExecuteExcel4Macroについて
こんにちは。よろしくお願いします。
あるフォルダ"D:\test"のなかに、４つのxlsファイル"o.xls"、"a.xls"、"b.xls"、"c.xls"があるとします。
使用するシート名は、それぞれo,a,b,c（ファイル名から".xls"を除いたもの）とします。
このとき"o.xls"を開いて、下記のマクロを実行すると、１行目にパス名、２行名にファイル名、３行目以下に（１列目は"a.xls"の、２列目は"b.xls"の、３列目は"c.xls"の）セルA3以下が読み込まれます。
たとえば、結果は添付の図のようになります。図がうまくアップできなかったらごめんなさい。
Sub sample1()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("o").Cells.Clear
Dim p As String, fn As String, fc As Long, i As Long, j As Long, d, e
p = ActiveWorkbook.Path
fn = Dir(p & "\" & "*.xls", 0)
fc = 0
If fn <> "" Then
fc = fc + 1
For j = 3 To 6
With Worksheets("o")
.Cells(1, fc).Value = p & "\" & fn
.Cells(2, fc).Value = fn
d = ExecuteExcel4Macro("'" & p & "\[" & fn & "]" & Mid(fn, 1, Len(fn) - 4) & "'!R" & j & "C1")
If d = 0 Or IsError(d) Then
Exit For
Else
.Cells(j, fc) = d
End If
End With
Next j
End If
Do
fn = Dir()
If fn <> "" Then
fc = fc + 1
For i = 3 To 6
With Worksheets("o")
.Cells(1, fc).Value = p & "\" & fn
.Cells(2, fc).Value = fn
e = ExecuteExcel4Macro("'" & p & "\[" & fn & "]" & Mid(fn, 1, Len(fn) - 4) & "'!R" & i & "C1")
If e = 0 Or IsError(d) Then
Exit For
Else
.Cells(i, fc) = e
End If
End With
Next i
Else
Exit Do
End If
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
上記の例は変数iとjが3から6までしか動きませんし、読み込むxlsファイルも３つしかありませんのですぐに終わりますが、実際には行やファイルがもっとたくさんあり、非常に時間がかかっています。そこで、
ExecuteExcel4Macro("'" & p & "\[" & fn & "]" & Mid(fn, 1, Len(fn) - 4) & "'!R" & i & "C1")
を
e = ExecuteExcel4Macro("'" & p & "\[" & fn & "]" & Mid(fn, 1, Len(fn) - 4) & "'!R3C1:R6C1")
というような風にして、For～Nextも使用せず
.range(Cells(3, fc),cells(6, fc)) = e
というふうに範囲で読み込もうとしたのですがうまくいきません。
ExecuteExcel4Macroは範囲を読み込むことはできないのでしょうか？
何とかして処理速度を上げたいのですが、どうすればよいでしょうか。
お礼
処理スピードを確認したかったため作成してみました。 処理件数が少ない成果それほど変化が無いようですので さくっと諦めて開きて閉じる方法で作成いたします。 参考資料有難うございました。