【Excel】マクロのエラー

Question

先日、ここで質問させていただきながら、複数のファイルの全シートから、会社名と金額を取り出して、一覧表を作成するマクロを作りました。(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

マクロに関しては初心者ですので、詳しく教えていただけると助かります。

Wendy02 · Accepted Answer

こんにちは。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 · Answer

こんにちは。

>マクロを実行すると時々「ファイルが見つからない」

はっきりとは言えませんが、パスを入れたほうが安全ですね。

問題はここの部分です。
MyPath = C:\○○○\○○○\一覧表\
Workbooks.Open Filename:=MyPath & FILE_NAME

後、コードをみると、Activate とか、Select がありますが、必要ないように書くと、もう少しわかりやすく、スッキリするでしょうね。

hana-hana3 · Answer

＞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 とした方が良いですね。
細かい部分での改善点は多々ありますが・・・^^;

Wendy02 · Answer

こんばんは。Wendy02です。

どこを修正されたのかな？

>元になる複数のファイルを何も変更することなく、閉じるようにするにはどのようにしたらいいでしょうか？

閉じないのですか？
　.Close False
ここに、このように書いて閉じるようにしてありますし、開ける時は、ひとつずつ開けています。

Wendy02 · Answer

こんにちは。Wendy02です。

ActiveWorkbook.Path & "\" '違う場合は、適宜書き込む
> 'ファイルの存在のチェック

新規ブックなどを対象とする場合は、エラーは、ここが発生元になります。保存していない状態は、Excelのブックは、キャッシュの中にしか存在しないので、上手くいかない場合があります。

ちょうと、#2の　hana-hana3　さんのところで、ThisWorkbook という書き方がありました。本来は、自ブックにマクロを入れて処理するのが基本ですから、ThisWorkbook ですが、ここらあたりは、その状況にもよります。明示的にパス名を入れたほうが安全だというのは言うまでもありません。

Wendy02 · Answer

こんにちは。Wendy02です。

今、インターネット検索で、「構文エラー」を探してみました。

mPath = ActiveWorkbook.Path & "\"

この場合は、「=　(イコール)」と「＆　(アンパサンド)」が、そのエラーの原因になるのだそうです。
ただ、それは書き方の問題で、こちらからでは、ちょっと分かりませんね。^^;　VBAの場合は、文字列などと「演算子」とくっついてしまうことがたまにあり、エラーを出しますね。初歩的なことなのですが、あまり考えないでやっていると、そういうことになります。まあ、こういうことはすぐに修正が利くので、あまり考えたことはありませんが・・・。

もう一つおまけに、そのコードは、グローバルなコードの書き方ではなく、あくまでもローカルな書き方です。ただ、グローバルなコードの書き方って、ちょっと嫌味っぽいので書きません。

以下のように書くのが、正式な書き方です。

Dim PathSept As String
　PathSep = Application.PathSeparator　'バックスラッシュもありますから。
　mPath = ActiveWorkbook.Path & PathSep

Wendy02 · Answer

こんにちは。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

ただ、それに関しては、少し、様子を見させてください。今書くと混乱させてしまいます。

お手数掛けて、大変申し訳ありません。

【Excel】マクロのエラー

こんにちは。

＞Workbooks.Open Filename:=FILE_NAME

こんにちは。

こんばんは。

この回答への補足

こんにちは。

こんにちは。

こんにちは。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング