Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : その他)
CallをはさむとDo〜Loopで次のファイルの情報が取得できない
投稿日時: 21/04/05 17:50:58
投稿者: Alice

「Microsoft Excel for Microsoft 365 MSO 64ビット」を使用しています。
 
フォルダ内の全Excelファイルを処理する為、下記サイトを参考にコードを作成しましたが、間にCallをはさまなければ、全ファイル名を取得でき順次処理ができるのですが、Callがあると「Dir()」で情報が取得できずに空データが返ってきて、全てのファイル処理がなされません。
 
http://officetanaka.net/excel/vba/file/file07.htm
 
  myFile = Dir(myFolder & "\*.xls*")
      
        Do While myFile <> ""
 
        〜〜処理内容〜〜
 
      Call budget
 
               〜〜処理内容〜〜
 
      myFile = Dir()
         
        Loop
 
Call の直前に「myFile = Dir()」を配置すると、2ファイル目までは処理できましたが、それ以降が次のファイル名が取得できず、処理が終わってしまいました。
 
Callを挟む場合は、その前にファイル名一覧を取得して、一時的にワークシートに書き出すか配列に入れて処理をするしかないのでしょうか?
 
ご教示いただけると幸いです。
宜しくお願い致します。
 

回答
投稿日時: 21/04/05 19:16:13
投稿者: simple

budgetのなかでDir()を使っていませんか?
使っていなければ、コードを提示できませんか?
これだと原因はつかめませんよ。

回答
投稿日時: 21/04/05 23:14:08
投稿者: よろずや

引用:
Callを挟む場合は、その前にファイル名一覧を取得して、一時的にワークシートに書き出すか配列に入れて処理をするしかないのでしょうか?
はい、Callの中でDirを使っている場合はそうなります。
  
http://officetanaka.net/excel/vba/file/file07.htm

のページをスクロールして一番下をご覧ください。
「FileSystemObjectで取得する」を使えば、お望みのことが素直に実現可能です。

投稿日時: 21/04/06 09:03:01
投稿者: Alice

simple様
よろずや様
 
早速の回答ありがとうございます。
 
Callでとばしている「budget」プロシージャ内を見たのですが、Dir()は使っておらず、変数「myFile」も「budget」プロシージャ内では使用していませんでした(←これは不必要情報かと思いますが)。
 
「budget」プロシージャも長いので、割愛して転載すると下記のようなかんじです(全て記載しないと意味ないとは思いますが。。)。
 
よろずや様がアドバイスして下さった「FileSystemObjectで取得する」の方法に変えてリトライしてみようと思います。
(こちらのサイト、上部の方しか見ていませんでした^_^;。ご指摘ありがとうございます。)
 
----------------------------------
Sub subsidiary_budget()
 
Dim subOriginFile '予算のコピー元ファイルのフルパス
Dim subOriginFile_name '予算のコピー元ファイル名
 
Dim subsidiaryTITLE    'ペースト先の表タイトルを格納した配列
 
Dim start_row As Long 'ペースト先データ表開始行
Dim start_col As Long 'ペースト先データ表開始列
Dim end_col As Long 'ペースト先データ表最終列
 
Dim copy_startROW As Long 'コピー元データ表開始行
Dim copy_startCOL As Long 'コピー元データ表開始列
 
Dim err_flg As Long '表タイトルの照合が不一致の場合に付与するフラグ
 
    'コピー元ファイルの冒頭名称を配列から取得(ファイルを特定する為)
    For i = 1 To UBound(subFile, 2)
     
        If InStr(myFile_name, subFile(i, 2)) <> 0 Then
         
            CopyFile = subFile(i, 1)
             
            Exit For
         
        End If
     
    Next i
     
    '予算のコピー元ファイルのフルパス取得
    subOriginFile = Dir(BudgetFolder & "\" & subFile(i, 1) & "*.xls*")
     
    'ファイルを開き、該当シートを選択
    Workbooks.Open BudgetFolder & "\" & subOriginFile, UpdateLinks:=False
        subOriginFile_name = ActiveWorkbook.Name
        Worksheets(copy_sh).Select
         
 On Error Resume Next
  
            '念の為フィルタとグループ化されている場合を考慮し、全データ表示させるようにする
             
                ActiveSheet.ShowAllData
                 
                ActiveSheet.Outline.ShowLevels RowLevels:=1
                ActiveSheet.Outline.ShowLevels columnLevels:=1
                 
                Err.Clear
   
     〜〜割愛〜〜
TOBASU:
 
    Workbooks(subOriginFile_name).Close savechanges:=False '上書き保存せず閉じる
 
 On Error GoTo 0
  
End Sub
 
 
 
 
 
 

回答
投稿日時: 21/04/06 10:28:56
投稿者: hatena
投稿者のウェブサイトに移動

「budget」=「subsidiary_budget」ということですか。だとしたら、
 

Alice さんの引用:

    '予算のコピー元ファイルのフルパス取得
    subOriginFile = Dir(BudgetFolder & "\" & subFile(i, 1) & "*.xls*")

 
Dir使ってますね。

回答
投稿日時: 21/04/06 10:34:42
投稿者: simple

Dir関数を使っているじゃありませんか。

引用:
'予算のコピー元ファイルのフルパス取得
    subOriginFile = Dir(BudgetFolder & "\" & subFile(i, 1) & "*.xls*")

これが原因ですね。

回答
投稿日時: 21/04/06 10:49:01
投稿者: simple

# 丸かぶりしてしまいました。失礼しました。(気持ちの整理上、そのままとさせて頂く)
 
myFile = Dir()
というのは、以前に行ったDir関数を繰り返し実行して、
条件に合致するものをすべて取得するのに使います。
ところが、その処理の途中で違う条件でDir関数が実行されてしまうと、
前の履歴はクリアーされますから、ご質問のような結果になります。
 
FSOを使えば、
Fileオブジェクトのコレクションを返してくれますから、
上記のようなことは起きません。
http://officetanaka.net/excel/vba/filesystemobject/folder06.htm
 
FSOについては、
http://officetanaka.net/excel/vba/filesystemobject/
から始まる、記事群が参考になります。

投稿日時: 21/04/06 13:32:58
投稿者: Alice

hatena様
simple様
 
申し訳ありません。
CallでとばしたプロシージャでDirを使用していたのを見落としました。
最初の投稿でsimple様の書込みのとおり、Callでとばしたプロシージャを記載しておいて良かったです。
 
(また、Callでとばしたプロシージャ名も、最初の投稿時「無難な名称の方がネット上では良いだろう」と思い、投稿内では変更しました。)
 
「FileSystemObjectで取得する」の方法に変えて、フォルダ内の全てのファイルを処理できるようになりました。
 
皆さまの書込み、感謝しております。
ありがとうございました。