3日がかりのその仕事、3分で終わらせる方法教えます!
パソコンスキルの心技体

Dir関数の利用で初心者がハマりがちな注意点 – Excelマクロ・VBA

2012年1月3日
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

エクセルマクロ・VBA達人養成塾 小川です。

キューバ旅行記、その73です。

キューバ屈指の外人向けリゾート地、バラデロにて。

朝の散歩で海を満喫したあと、9時に、旅行代理店に行ってきました。
そしてさっそく午前中のシュノーケリングのツアーに申し込みをし、宿を、Casa Particular(民宿)からリゾートホテルに変えるべくホテルを紹介してもらい、夕方からスカイダイビングをすべく予約をしました。

そして、シュノーケリングのお迎え待ちです。

エクセルマクロ達人養成塾塾長ブログ
時間がちょっとあったので、とりあえずいろいろうろうろと。

エクセルマクロ達人養成塾塾長ブログ

海に続くあぜ道の向こうに何か生き物がいる。。。

エクセルマクロ達人養成塾塾長ブログ

近づいてみたらニワトリでした。

エクセルマクロ達人養成塾塾長ブログ

Restaurant “Casa de la miel” – レストラン「ハチミツの家」

ちょっと気になったのだけど、結局バラデロ滞在中には行くことのなかったお店。

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ

Restaurant “El Toro” – レストラン「闘牛」

名前からして、たぶん、ステーキハウスなのだろう。

エクセルマクロ達人養成塾塾長ブログ

写真だと光の具合は涼しげですが、実際にはすごい暑いです。

そんなわけで、待っていると、車が到着。

「今日の客は君ひとりだ」ということで、車に乗せられ、「マタンザス」というバラデロよりやや西にある町との間にある、シュノーケリングスポットに向かいます。
高速道路にて。

エクセルマクロ達人養成塾塾長ブログ

道中、海がメチャメチャきれい ヾ(´ー`)ノ

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ

高速道路のトールゲート付近。

塾長のキューバ旅行記、最初から読みたい方はこちらから


Dir関数の利用で初心者がハマりがちな注意点 – Excelマクロ・VBA

今日は、12月10日~11日の講座を受講された、仙台の「デコさん」からの質問内容をご紹介。

クリスマス明けくらいのタイミングで、以下の質問メールを受け取った。

先日はありがとうございました。
10日11日のマクロ講習会を受講した仙台のデコです。

あれから毎日送られてくる課題を楽しみに解いていました。
まだまだすんなり出来るようにはなりませんが、時間を忘れる楽しさですね。

そんな今日この頃、タイミングよく?実業務でマクロをくむ仕事が入りました。

なんとか自力で組んでみたものの、実行途中でエラーで止まってしまいます。

ネット等で調べても手掛かりが見つからず、年末のお忙しい中お手数ですが、原因及び解決法をご教示いただきたくよろしくお願いします。

内容は、1つのフォルダ内にある複数ファイルのうち、条件に合う名前のファイルを開いて、各々のシートの指定エリアを同フォルダの親ファイルにコピーしていくというものです。
貼り付ける際、既に貼り付けたデータを消さないよう「空白セルを無視して」貼り付けています。

マクロは以下の通りです。

Sub juchu_test1()
    Dim ws1 As String
    Dim ws2 As String
    ws1 = ThisWorkbook.Worksheets(1).Name
    ws2 = ThisWorkbook.Worksheets(2).Name
    Dim oWB As Workbook
    Dim sFname As String
    sFname = Dir(ThisWorkbook.Path & “\” & “*受注*.xlsx”, vbNormal)
    Do While sFname <> “”
        Set oWB = Workbooks.Open(Filename:=ThisWorkbook.Path & “\” & sFname, UpdateLinks:=False, ReadOnly:=True)
        Debug.Print Dir
        oWB.Worksheets(ws1).Range(“B2:G9”).Copy
        ThisWorkbook.Worksheets(ws1).Activate
        Range(“B2”).PasteSpecial Paste:=xlPasteAll, Skip
Blanks:=True

        oWB.Worksheets(ws2).Range(“B2:M9”).Copy
        ThisWorkbook.Worksheets(ws2).Activate
        Range(“B2”).PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True

        Application.CutCopyMode = False
        oWB.Close SaveChanges:=False
        sFname = Dir()             ‘ 次のファイル名を参照
    Loop
End Sub

3~4ファイルまではうまくいくのですが、すべての処理が終わらないうちに、下から3行目のsFname = Dir()で下記のエラーが表示して止まってしまいます。

エクセルマクロ達人養成塾塾長ブログ

実業務データと似たようなサンプル環境を作って同じマクロを動かしてみましたが、同様の現象でした。

作ったサンプル環境ごと送信させていただきますのでよろしくお願いします。

以上


ということで。

いただいたサンプル環境は、こんな感じだった↓

エクセルマクロ達人養成塾塾長ブログ

ここで、「元ファイル.xlsm」の中で実行される上記のマクロは、他のファイルを順番に開いていく、というわけ。

○マクロについて、細かい改善点はあるものの全体によく書けていること
○とても質問の仕方が上手なこと
○ご自身で調べる力がおありな感じな雰囲気が漂いまくっていること
○実際、セミナー中やその後の懇親会の席でお話したとき聡明な方という印象を持ったこと

から、投げやりで大ざっぱなポイントだけに絞った返信をした。

返信につけたサンプルは、以下のとおり。

Sub juchu_test1_ogawa()
    Dim ws1 As String
    Dim ws2 As String
    ws1 = ThisWorkbook.Worksheets(1).Name
    ws2 = ThisWorkbook.Worksheets(2).Name
    Dim oWB As Workbook
    Dim sFname As String
    sFname = Dir(ThisWorkbook.Path & “\” & “*受注*.xlsx”, vbNormal)
    Do While sFname <> “”
‘        Set oWB = Workbooks.Open(Filename:=sFname, UpdateLinks:=False, ReadOnly:=True)
        Set oWB = Workbooks.Open(Filename:=ThisWorkbook.Path & “\” & sFname, UpdateLinks:=False, ReadOnly:=True)
        
        ‘以下、実行してみてください。(要は、Dir関数を実行する都度、次のファイルに処理対象が移ってしまうのです。)
        Debug.Print Dir ‘[1]
        Debug.Print Dir ‘[2]
        Debug.Print Dir ‘[3]
        Debug.Print Dir ‘[4]
        Debug.Print Dir ‘[5]
    
        oWB.Worksheets(ws1).Range(“B2:G9”).Copy
        ThisWorkbook.Worksheets(ws1).Activate
        Range(“B2”).PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True
                        
        oWB.Worksheets(ws2).Range(“B2:M9”).Copy
        ThisWorkbook.Worksheets(ws2).Activate
        Range(“B2”).PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True
        
        Application.CutCopyMode = False
        oWB.Close SaveChanges:=False
        sFname = Dir()
    Loop
End Sub

返信メールの本文には、以下の文章を書いた。

デコさん、こんにちは。

VBAでDir関数を使い始めたばかりのころに、ありがちなハマりです (^^;
Dir関数を実行する都度、次のファイルに処理対象が移ってしまいます。

分かりやすいよう、極端な例を作ってみました。

イミディエイトウィンドウの内容がどう変わるか注意しながら、添付のサンプルを実行してみてください。
デコさんなら、それでもうご理解いただけるかと思います。

対処ですが、

Debug.Print Dir

のところ

Debug.Print oWB.Name

とすればよさそうに思います。

以上、多少乱暴な気もしますが。。デキる人向けの回答なので、手短に (^^;


解説すると…。

Dir関数のヘルプを見ると分かりますが、

Dir 関数を最初に呼び出すとき、引数 pathname を指定しないとエラーになります。またファイル属性 (引数 attributes) を指定する場合にも、引数 pathname を指定する必要があります。

Dir 関数は、引数 pathname と一致する最初のファイル名を返します。それ以外のファイル名で引数 pathname と一致するファイル名を取得するには、引数を指定せずに再び Dir 関数を呼び出してください。

ということです。

要は、引数を指定せずに再び Dir 関数を呼び出す度に、条件に該当する次のファイルのファイル名を返してくるので。

Debug.Print 中でむやみに呼び出すと、そこで指定されたファイルは、肝心の処理の際にすっとばされてしまう、ということですね。

Dir 関数は、むやみにテスト用のコード内で呼び出してはいけない、ということです。


ということで。

以下、その後、彼女からいただいたお返事。

小川先生!
驚くほど迅速な回答ありがとうございました!

お返事いただきすっきりしました。
というか、言われてみたらななぜ気付かなかったのかと・・・

年明けには早々エンドユーザへの最終回答ができそうです。
ありがとうございました。
またお願いすると思いますが、よろしくお願いします。


> というか、言われてみたらななぜ気付かなかったのかと・・・

とのことですが。

最初は、やはりそんなモンです ヾ(´ー`)ノ

なので、フォロー期間をフル活用して、分からないことがあったらなんでもガンガン聞くことが重要です。

ちなみに、

> 小川先生!
> 驚くほど迅速な回答ありがとうございました!

ということでしたが。

今回のこのご相談。たまたま僕が別件でPCの前で仕事をしていたということもありましたが、受け取ってからお返事までにかかった時間は、48分でした。
上手に質問できる人は、回答を受け取るまでにかかる時間も短いです ヾ(´ー`)ノ

キーワード

コメント

2 thoughts on “Dir関数の利用で初心者がハマりがちな注意点 – Excelマクロ・VBA

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

最新の記事

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド