VBAにてアクティブでは無いシートの値が参照されてしまいます。

質問

こんばんは、以前二回程質問させていただいた物です。
過去のアドバイスから少しずつ疑問をつぶしていった所再び問題が発生してしまいました。
同じプログラムを何度も載せるのは大変恐縮ですが、どうしても解決出来ない為(私の努力不足は重々承知です)皆様の力を貸して頂きたいと思います。

以下のようなループの際、途中にMsgBox(strFILENAME)を入れたり、Active.sheetでウオッチ式で見ても参照してほしいシート名を表示するにも関わらず、計算結果を書き込むシートのセルを参照してしまいます。
なぜ、WS1のセルの値を参照してしまうのかわからず困っています。
確実にMsgBox(strFILENAME)で表示されるファイル名のシートのセルを参照する方法を教えて頂きたく、よろしくお願いいたします。(Workbook.Worksheet.のように明示する方法を教えていただいたのですがエラーが発生してしまいうまく使いこなすことが出来ませんでした)

どうか、宜しくお願いいたします。

Option Explicit
Sub syoutotumen()
Dim i As Long
Dim j As Long
Dim k As Long
Dim kyori As Long
Dim n As Integer
n = 1
i = 1
j = 1
k = 1
Const cnsYEN = "\"
Dim swESC As Boolean
Dim ws1 As Worksheet
Dim xlAPP As Application
Dim objWBK As Workbook
Dim strPATHNAME As String
Dim strFILENAME As String
strPATHNAME = "C:\Documents and Settings\tata41\デスクトップ\画像処理２\"
If strPATHNAME = "" Then Exit Sub
strFILENAME = Dir(strPATHNAME & "demo******", vbNormal)

If strFILENAME = "" Then
MsgBox "このフォルダにはExcelワークブックは存在しません"
Exit Sub
End If
Set xlAPP = Application
With xlAPP
.ScreenUpdating = False
.EnableEvents = False
.EnableCancelKey = xlErrorHandler
.Cursor = xlWait
End With
Set ws1 = Worksheets("sheet1")
Range("A1") = "0"
Range("A2") = "1"
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A1022")
Do While strFILENAME <> ""
DoEvents
If swESC = True Then
If MsgBox("ESCが押されました。ここで終了しますか？", vbInformation + vbYesNo) = vbYes Then
GoTo Button1_Click_Exit
Else
swESC = False
End If
End If
xlAPP.StatusBar = strFILENAME & "処理中・・・"

Set objWBK = Workbooks.Open(Filename:=strPATHNAME & cnsYEN & strFILENAME, UpdateLinks:=False, ReadOnly:=True)

Do

If Cells(i, 2) = 0 Then Exit Do
i = i + 1
Loop

Do
If Cells(j, 3) = 0 Then Exit Do
j = j + 1
Loop

Do
If Cells(k, 4) = 0 Then Exit Do
k = k + 1
Loop

kyori = (i + j + k - 21) / 3
ws1.Cells(n, 2) = kyori
n = n + 1
i = 1
j = 1
k = 1
objWBK.Close savechanges:=False
strFILENAME = Dir
Loop
GoTo Button1_Click_Exit
Button1_Click_ESC:
If Err.Number = 18 Then
swESC = True
Resume
ElseIf Err.Number = 1004 Then
Resume Next
Else
MsgBox Err.Description
End If

Button1_Click_Exit:
With xlAPP
.StatusBar = False
.ScreenUpdating = True
.EnableEvents = True
.EnableCancelKey = xlInterrupt
.Cursur = xlDefault
Set objWBK = Nothing
Set xlAPP = Nothing
End With
End Sub

回答No.2

fujillin · ベストアンサー

huhuhuhuiさんこんばんは
苦戦していますね。

こちらの環境（EXEL2000）で確認する限りでは、そのままでも一応、動作しているようですが・・・
開いたワークブック上で0が入力されている行番号を調べ、それから計算した結果をws１のB列に記入してくれます。
ので、残念ながら明確に問題点は、指摘できません。

ちと疑問なのは、最初にws1に指定しているのが、アクティブブックのSheet1なのですが、その後でA列に「0、1、2・・・」書き込んでいるのはアクティブシートが対象になっています。
マクロの実行するブックもシートが1枚（Sheet1のみ)ならば、結局は同じシートになりますが、複数ある場合は、Sheet1以外からマクロを実行すると、「0、1、2・・・」を書き込むシートと結果を書き込むシートが別物になってしまいます。
ここでも、できるだけシートを明示しておいた方が良いですね。（将来のために）
＜例＞
　Range("A1") = "0"　→　ws1.Range("A1") = "0"

さて、開いたブックでのシートの指定の方法ですが、各ブックで開くシート名がみな同じならば「シート名」で指定すればよいですが、そうとも限らないでしょうから、シートが1枚しかないことを利用して・・・
　If Cells(i, 2) = 0 Then Exit Do
　　　→　objWBK.Worksheets(1).Cells(i, 2) = 0 Then Exit Do
のような指定方法でいかがでしょうか？
（上の記述方法はシート名でなく、一番目のシートという指定方法です）
（objWBKにはopenメソッドで開いたブックが代入されています）

ただし、こちらの環境では動作していますので、これを指定したから動くと言うものでもないかも・・・

お礼

こんにちは！

開いたブックのシートの指定方法を試したところうまくいきました！！
前回に続き詳しく解説して頂きありがとうございました！
本当にありがとうございました！

回答No.4

Wendy02

こんばんは。

個々のテクニックは、ご自身が書いたものではないのかもしれませんが、コードには無理があるように思います。コードとしては、テクニックの寄せ集めのようです。どこを直したらようというようなレベルのコードではないように思います。応急措置は、親オブジェクトの指定をすればよいのですが、それぞれのテクニックには適材適所があります。それを無視しても意味がなくなります。もう、これ以上は、全体的に書き直したほうがよいと思います。たぶん、Dir でCSVファイルを読み取って計算をさせる初歩的な内容なのに、コードは複雑にしすぎているように思います。

>Set xlAPP = Application
>With xlAPP
これは、オートメーション・オブジェクトではないのですから、VBE内では無意味です。

> .EnableCancelKey = xlErrorHandler
　　↓
>If swESC = True Then
>If MsgBox("ESCが押されました。ここで終了しますか？", vbInformation + vbYesNo) = vbYes Then

ここは、ESC Key に対する　ErrorHandler のトラップが抜けていませんか？
そもそも、ユーザーオプションでマクロを止めるような必要性があるのか分かりませんし、CSVファイルがの処理が長引くようなら、最初から、ファイル数をカウントして、離脱させたほうがよいです。

それに、#3さんでご指摘にありますが、
>strFILENAME = Dir(strPATHNAME & "demo******", vbNormal)

.csv の拡張子ないなら、そのフォルダにあるものをすべて拾うことになり、エラーの発生が高くなります。

>Range("A1") = "0"
>Range("A2") = "1"
>Range("A1:A2").Select
>Selection.AutoFill Destination:=Range("A1:A1022")

これは、
With Range("A1:A1022")
　 .FormulaLocal = "=ROW()-1"
　 .Value = Range("A1:A1022").Value
End With

のほうが自然です。AutoFill メソッドはあくまでも、ワークシートのテクニックです。
文字列の数字をCell に入力しても、型のキャスティングが働いて、数値に変わってしまいます。

>Set objWBK = Workbooks.Open(Filename:=strPATHNAME & cnsYEN & strFILENAME, _
UpdateLinks:=False, ReadOnly:=True)

単に、objWBK に入れるだけでなく、objWBK.Worksheets(1) などして

>　Do
>　If Cells(i, 2) = 0 Then Exit Do
>　i = i + 1
>　Loop

Cells の親オブジェクトの指定をしたほうがよいです。

>Button1_Click_ESC:
>If Err.Number = 18 Then
> swESC = True
> Resume
>ElseIf Err.Number = 1004 Then
> Resume Next
> Else
> MsgBox Err.Description
> End If

Esc で、Resume, '1004'で、Resume Next では、無理があります。
'1004( というのは、ワークシート側からのエラーですから、それは回復できないことが多いので、Resume Next で戻しても役に立たないことが多いです。エラートラップは、そのエラーがわかっていれば、Resume Next で戻れますが、1004では、復旧出来ません。それを次に渡してもあまり意味がないように思います。そのResume Nextにはっきりした確信があるなら、別ですが、エラーが特定できるなら、On Error Resume Next ～ On Error Goto 0 ではさむことですね。

お礼

こんにちは。
このプログラムは仰る通りで、昔誰かが作ったものをベースとしているため、私自身が作ったものではありません。(私自身、VBAの存在をしったのも触ったのもつい最近のことです・・・)
マクロを止める必要はないのでいらない部分は削除しAutofillメソッドの部分も代えさせて頂きました。
どうやら無意味なコードが多いようなので改善します！
本当にありがとうございました。

回答No.3

mt2008

No.1です。
あぁ、CSVを開くんですね。でしたらシート名はファイル名から「.csv」を取ったものになりますが、あえて指定する必要は有りませんね。

少しコードを読んでみました。
開くファイルは「demo…」で始まる事だけが条件ですが、CSVファイル以外は無いのですか？もし、他のファイルもあるのでしたら
"demo******"
↓
"demo*.csv"

と、した方がよろしいかと。
また、『計算結果を書き込むシートのセルを参照してしまいます』との事ですが、少なくともそのような動きをしているようには思えません。
ブレイクポイントを設定して１行づつコードを実行してみましたか？

ブレイクポイントは、コードの左にある灰色の帯をクリックすると設定できます。
最初の　n = 1　の左にブレイクポイントを設定するとコードを最初から確認できます。
この状態でマクロを動かすとブレイクポイントを設定した箇所でマクロが一時停止します。変数にカーソルを合わせれば変数の値を確認することが出来ます。
先に進めるにはF8です。一回押すと1ステップ進みます。

お礼

回答ありがとうございます！
csvファイル以外はありません。
皆さまの助言により解決することができました。
F8により進めてみたりはしましたが、ブレイクポイントは使ってませんでした・・
ともあれ、本当にありがとうございました！

回答No.1

mt2008

あまりちゃんとコードは読んでいませんが、参照して欲しいシートを明示的に指定すればよいのでは？

If Cells(i, 2) = 0 Then Exit Do
↓
If Sheets("シート名").Cells(i, 2) = 0 Then Exit Do

2～4列目に初めて0が入っている行を調べる為だけにループを回すのもどうかと思います。ループを回さなくても、ワークシート関数で0の位置が取得できますよ。
２列目の場合：
i = Application.WorksheetFunction.Match(0, Range("シート名!B:B"), 0)

お礼

補足ではなくお礼のほうに書くべきでした！！
補足にも目を通して頂けると幸いです。

補足

お早い回答ありがとうございます。
さっそく試してみましたが『シート名』の部分に何と記述すればよいかわかりません。(strFILENAMEやdemo00001などを記入してみました)demo00001.CSV～demo01022.CSVを次々に参照したいのですが、何を記述すればよい教えて頂くことは出来ないでしょうか？
よろしくお願いいたします。