【Excel】マクロのエラー

質問

先日、ここで質問させていただきながら、複数のファイルの全シートから、会社名と金額を取り出して、一覧表を作成するマクロを作りました。(Excel2003使用)

この複数のファイルと一覧表は同じフォルダに入っているのですが、一覧表が４種類あるので、そのフォルダ内に『一覧表』という名前のフォルダを作って、その中に４つの一覧表を移動しました。
その後、マクロを実行すると時々「ファイルが見つからない」といった内容のエラーが出るようになったのですが、一旦、中止して一覧表の元になるファイルを１つ開いて何もせずにそのまま閉じた後、再度マクロを実行するとエラーは出ません。

このエラーは、やはり『一覧表』フォルダを作って移動させたことが原因でしょうか？また、このエラーが出ないようにする方法はあるのでしょうか？
コードは下記のようになっていて、“ファイル名”というシートに一覧表の元になる複数のファイル名が入力してあります。

Sub LISTOUT()
OUT_LINE = 1
MY_BOOK = ActiveWorkbook.Name
Sheets("ファイル名").Select
For FILE_GYO = 2 To Cells(2, 1).End(xlDown).Row
Sheets("ファイル名").Select
FILE_NAME = Cells(FILE_GYO, 1)
Workbooks.Open Filename:=FILE_NAME
OPEN_BOOK = ActiveWorkbook.Name
SHEET_SU = Sheets.Count
For i = 1 To SHEET_SU
Sheets(i).Select

CODE_NO = ActiveSheet.Name
KAISHAMEI = Cells(2, 3)
TSUKIKEI = Cells(1, 23)
RUIKEI = Cells(1, 25)
Workbooks(MY_BOOK).Activate
Sheets("一覧表").Select
OUT_LINE = OUT_LINE + 1
Cells(OUT_LINE, 1) = CODE_NO
Cells(OUT_LINE, 3) = KAISHAMEI
Cells(OUT_LINE, 4) = TSUKIKEI
Cells(OUT_LINE, 6) = RUIKEI
Workbooks(OPEN_BOOK).Activate

Next
ActiveWorkbook.Close
Next
End Sub

マクロに関しては初心者ですので、詳しく教えていただけると助かります。

回答No.3

Wendy02 · ベストアンサー

こんにちは。Wendy02です。

>hana-hana3さんだったら、この内容のマクロはどのように書かれるのでしょうか？もし可能でしたら、今後の参考に示していただけると嬉しいです。

しばらく遠慮して、ROMしていましたが、こちらから、私なりのコードを書かせていただきます。
OUT_LINE, OPEN_BOOK　こういう大文字は、定数に限ります。変数は、大文字、小文字交じりにします。また、ループカウンターのFILE_GYO などは、i,j,k などを使います。また、Rangeオブジェクトなどは、出来る限り、暗黙のプロパティではなく、明示的に、Value や Text を使います。

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

Sub ListOut_R()
　Dim MyBook As Workbook
　Dim MySheet As Worksheet
　Dim FileName As String
　Dim i As Integer 'シート数
　Dim j As Long
　Dim k As Long

　Dim mPath As String

　Set MyBook = ActiveWorkbook
　mPath = ActiveWorkbook.Path & "\" '違う場合は、適宜書き込む
　Set MySheet = MyBook.Sheets("一覧表")

　k = 1
　Application.ScreenUpdating = False
　For j = 2 To Cells(2, 1).End(xlDown).Row
　　FileName = MyBook.Sheets("ファイル名").Cells(j, 1).Value

　　'ファイルの存在のチェック
　　If Dir(mPath & FileName, vbNormal) <> "" Then
　　With Workbooks.Open(mPath & FileName)

　　　For i = 1 To .Worksheets.Count
　　　　k = k + 1
　　　　MySheet.Cells(k, 1).Value = .Worksheets(i).Name
　　　　MySheet.Cells(k, 3).Value = .Worksheets(i).Cells(2, 3).Value
　　　　MySheet.Cells(k, 4).Value = .Worksheets(i).Cells(1, 23).Value
　　　　MySheet.Cells(k, 6).Value = .Worksheets(i).Cells(1, 25).Value
　　　Next i
　　　.Close False
　　End With
　　End If
　Next j
　Application.ScreenUpdating = True
　Set MySheet = Nothing
　Set MyBook = Nothing

End Sub

お礼

アドバイスありがとうございます。
Wendy02さんの書かれたコードでも試してみたところ、そのまま使用するとエラーが出たので、一部変更したら一覧表はできたのですが、一覧表完成後、元になる複数のファイルを何も変更することなく、閉じるようにするにはどのようにしたらいいでしょうか？
(元々そういうふうにコードが書かれていたのならスミマセン！)

回答No.7

Wendy02

こんにちは。Wendy02です。

>ファイル名”シートに入力しているファイル名をフルパスで入力し直して
&
>コード上でフルパスを指定するのと、どちらがいいのでしょう？？

私自身は、コード上でフルパスにしなおすようにします。フルパスは、名前も長い上にミスが見つけにくいです。というよりも、面倒くさいですね。

最初から、読み直してみました。もともと、「一覧表」フォルダと書かれていたのを、私は、ActiveWorkbook に替えています。「適当に替えてください」として、そのままなっていますが、そこが間違いで、すっかり、見落としていました。それで、エラーに、振り回してしまいました。すみませんでした。

私のコードのここがヘンです。自分で最初#1で書いておきながら、すっかり忘れていました。

*　Set MyBook = ActiveWorkbook
*　mPath = ActiveWorkbook.Path & "\" '違う場合は、適宜書き込む
*　Set MySheet = MyBook.Sheets("一覧表")

　　　↓
'明示的にフォルダ名を入れるべきでした。
　mPath = "C:\○○○\○○○\一覧表\" '実際のパスを入れてください。
　Set MyBook = ActiveWorkbook 'データを入れるブックが、自ブックの場合は、ThisWorkbook でも可
　Set MySheet = MyBook.Sheets("一覧表")

また、以下のファイル名の書き方によって潜在的にエラーが出る可能性があります。

　　FileName = MyBook.Sheets("ファイル名").Cells(j, 1).Value

ただ、それに関しては、少し、様子を見させてください。今書くと混乱させてしまいます。

お手数掛けて、大変申し訳ありません。

お礼

こんにちは。
またまたアドバイスいただき、ありがとうございます。
>フルパスは、名前も長い上にミスが見つけにくいです。というよりも、面倒くさいですね。
質問文に記載しているコードを書いてくださった方から、ファイル名はフルパスで入力しておくと説明されていたのですが、ここの部分でエラーが出ないようになるまで、少々手間がかかりました。確かにミスが見つけにくくて面倒でした(^_^;)
再度、Wendy02さんが修正してくださったコードでマクロを実行してみようと思います。
こちらこそ、何度もお手間を取らせて申し訳ありません<(_ _)>

回答No.6

Wendy02

こんにちは。Wendy02です。

今、インターネット検索で、「構文エラー」を探してみました。

mPath = ActiveWorkbook.Path & "\"

この場合は、「=　(イコール)」と「＆　(アンパサンド)」が、そのエラーの原因になるのだそうです。
ただ、それは書き方の問題で、こちらからでは、ちょっと分かりませんね。^^;　VBAの場合は、文字列などと「演算子」とくっついてしまうことがたまにあり、エラーを出しますね。初歩的なことなのですが、あまり考えないでやっていると、そういうことになります。まあ、こういうことはすぐに修正が利くので、あまり考えたことはありませんが・・・。

もう一つおまけに、そのコードは、グローバルなコードの書き方ではなく、あくまでもローカルな書き方です。ただ、グローバルなコードの書き方って、ちょっと嫌味っぽいので書きません。

以下のように書くのが、正式な書き方です。

Dim PathSept As String
　PathSep = Application.PathSeparator　'バックスラッシュもありますから。
　mPath = ActiveWorkbook.Path & PathSep

お礼

Wendy02さん、おはようございます。
わざわざ「構文エラー」をインターネットで調べていただき、ありがとうございます。
最初に「構文エラー」が出たときに、どう修正すればいいのかわからなかったので、“ファイル名”シートに入力しているファイル名をフルパスで入力し直して、パスに関するコードをコメント化してみたんです。
(コードの前に『'』を入力すれば、その処理は無視されると聞いていたので)
エラーは出なくなりましたが、今回の場合“ファイル名”シートにフルパスでファイル名を入力しておくのと、コード上でフルパスを指定するのと、どちらがいいのでしょう？？
どちらにしても、正式な書き方をすれば、こういうエラーは避けられるのでしょうけど(^^ゞ

回答No.5

Wendy02

こんにちは。Wendy02です。

ActiveWorkbook.Path & "\" '違う場合は、適宜書き込む
> 'ファイルの存在のチェック

新規ブックなどを対象とする場合は、エラーは、ここが発生元になります。保存していない状態は、Excelのブックは、キャッシュの中にしか存在しないので、上手くいかない場合があります。

ちょうと、#2の　hana-hana3　さんのところで、ThisWorkbook という書き方がありました。本来は、自ブックにマクロを入れて処理するのが基本ですから、ThisWorkbook ですが、ここらあたりは、その状況にもよります。明示的にパス名を入れたほうが安全だというのは言うまでもありません。

お礼

再度のアドバイスありがとうございます。
確か一番最初にマクロを実行した際のエラーで
> mPath = ActiveWorkbook.Path & "\" '違う場合は、適宜書き込む
の部分が黄色になりました。
実は#2の　hana-hana3　さんが書かれていた『ThisWorkbook』 という書き方でも試してみたんです。
私はこのコードは何の処理かということがきちんと理解できている状態ではないのですが、いろいろ触ってみることで、少しずつわかるようになってきたような気がします。
今回も最後までお付き合いくださり、ありがとうございました。

回答No.4

Wendy02

こんばんは。Wendy02です。

どこを修正されたのかな？

>元になる複数のファイルを何も変更することなく、閉じるようにするにはどのようにしたらいいでしょうか？

閉じないのですか？
　.Close False
ここに、このように書いて閉じるようにしてありますし、開ける時は、ひとつずつ開けています。

お礼

少し時間を置いて、再度ファイル名をよく見直してみたところ、正しく実行されました。
お騒がせしてスミマセンでした<(_ _)>

補足

アドバイスありがとうございます。
エラーが出たときにコードを確認したら、一度に全部出たわけではありませんが、下記の３箇所が黄色になりました。
> mPath = ActiveWorkbook.Path & "\" '違う場合は、適宜書き込む
> 'ファイルの存在のチェック
　If Dir(mPath & FileName, vbNormal) <> "" Then
　With Workbooks.Open(mPath & FileName)
> .Close False
[.Close False]が黄色になったときは、“構文エラー”と出ていたような気がします。
あいまいな記憶で補足にならないかもしれませんが、よろしくお願いします。

回答No.2

hana-hana3

＞Workbooks.Open Filename:=FILE_NAME

ブックを開くと一時的にカレントフォルダが変化するので、マクロが正しく動くようになったのでしょう。
正しくファイルを開くには、フルパス指定する必要があります。
Workbooks.Open Filename:="C:\Data1\File_A\FILE_NAME.xls"
このデータにはどのように入っているのでしょうか？

ファイル名に
ブックと同じフォルダなら、「FILE_NAME1.xls」
サブフォルダなら、「一覧表\FILE_NAME1.xls」
と入れておいて、
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & FILE_NAME
などのように改善する方が良いでしょう。

＞ActiveWorkbook.Close

も、Workbooks(OPEN_BOOK).Close とした方が良いですね。
細かい部分での改善点は多々ありますが・・・^^;

お礼

回答ありがとうございます。
パスを入れることでエラーは出なくなりました。
>ActiveWorkbook.Close
も、Workbooks(OPEN_BOOK).Close とした方が良いですね。
とご指摘がありましたので、早速そのように修正しました。
>細かい部分での改善点は多々ありますが・・・
hana-hana3さんだったら、この内容のマクロはどのように書かれるのでしょうか？もし可能でしたら、今後の参考に示していただけると嬉しいです。

回答No.1

Wendy02

こんにちは。

>マクロを実行すると時々「ファイルが見つからない」

はっきりとは言えませんが、パスを入れたほうが安全ですね。

問題はここの部分です。
MyPath = C:\○○○\○○○\一覧表\
Workbooks.Open Filename:=MyPath & FILE_NAME

後、コードをみると、Activate とか、Select がありますが、必要ないように書くと、もう少しわかりやすく、スッキリするでしょうね。

お礼

Wendy02さん、こんにちは。
パスを入れたところ、エラーは出なくなりました。
エラーが出たときにコードを見てみたら、
“Workbooks.Open Filename:=FILE_NAME”の部分が黄色になっていたので、ここに何か問題があるのだろうとは思ったのですが、まだ自分でコードを作れる状態ではないので。。。
このマクロも例を示していただいたもので、セルやシートの設定等を変更したぐらいで、そのまま使わせてもらっていて、コードを見ても内容はなんとな～く想像できるかなっていう感じです。
回答ありがとうございました<(_ _)>