ExcelVBAで困っています。

フォルダ内にあるエクセルブックを指定するコードを
datafile=Dir(Path & "*.xlsx")にしていますが、.xlsブックと.xlsxブック
両方のデータを取得したいのですがなかなかできません。
教えて頂けないでしょうか。
宜しくお願いします。

質問者からの補足コメント

• 

  ご連絡ありがとうございます。
  datafile=Dir(Path & "*.xls")だと
  .xlsの拡張子を持つブックしか取得できないですよね？
  xlsxとxls両方対象にしたいと思っています。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/06/27 13:01

• 

  ご連絡ありがとうございます。
  ワイルドカードを付けるとワイルドカード以降は全てヒットしますよね？なので.xlsmも対象になりますよね？できればxls,xlsxだけに絞ってデータ取得したいのですが無理ですよね？

    補足日時：2018/06/27 14:12

• 

  ご連絡ありがとうございます。
  Dir(Path & "*.xls")　と　Dir(Path & "*.xls*")は同じ意味になりますか？
  拡張子の末尾にアステリスクを記述しないでもよいのでしょうか？

    補足日時：2018/06/27 15:05

• 

  tatsu99 様
  お久しぶりです（*^_^*）
  ご連絡ありがとうございます。
  ちょっと教えてほしいのですが
  datafile=Dir(Path & "*.xls*")　
  上記の"*.xls*"と　"*.xls"は同じ意味になり　xlsxもxlsmも返しますよね？
  ・・・
  下記のコードがいまいちわからないのですが教えてもらえますか？
  If LCase(Right(datafile, 4)) = ".xls" Or LCase(Right(datafile, 5)) = ".xlsx" Then
  End If

  No.4の回答に寄せられた補足コメントです。 補足日時：2018/06/27 15:35

• 

  下記のコードがいまいちわからないのですが教えてもらえますか？
  If LCase(Right(datafile, 4)) = ".xls" Or LCase(Right(datafile, 5)) = ".xlsx" Then
  
  LCaseは小文字に変換して検索してdatafileの右端から４文字が".xls"または
  右端から５文字が".xlsx"のときに処理するという認識でよろしいですか？
  また、ピリオドを外して下記コードにしても同じ意味になりますか？
  If LCase(Right(datafile, 3)) = "xls" Or LCase(Right(datafile, 4)) = "xlsx" Then

    補足日時：2018/06/27 15:53

• 

  tatsu99様
  連絡が遅れて申し訳ありません。
  いつも丁寧に教えていただいてありがとうございます。
  結論から言うと出来ました！！ありがとうございます（*^_^*）
  もう一つ教えて頂きたい部分があるのですが
  取得したデータを新規ブックに保存してマクロ有効ブックは保存せずに閉じて終了しています。
  実行したマクロブックをCloseではなく、そのブックごと削除は可能でしょうか？
  現在のコード↓
  '【集約したデータを新規ブックに名前を付けて保存】
  ActiveSheet.Copy '集約データをコピー
  ActiveWorkbook.SaveAs Filename:=myfolder & "\" & "Alldata" '新規ブックに保存。
  集約book.Close savechanges:=False 'ブックを保存せずに閉じる。
  単純にDeleteではできませんでした

  No.7の回答に寄せられた補足コメントです。 補足日時：2018/06/28 16:25

No.9 ベストアンサー 回答者： tatsu99 回答日時： 2018/06/29 11:32

＞そのフォルダ内から実行したマクロブックがなくなっていれば上記で説明したことが

＞起こる確率が低くなると感じいろいろな方法を模索しておりました。
＞なにか良い案があれば教えて頂けないでしょうか。
なるほど、そのような事情がありましたか。
私の方で思いつくのは、「マクロの先頭で、まず、Alldata.xlsxがすでに存在すれば、何もしないでマクロを終了する。」
ことくらいです。
以下の文をマクロの実行部の先頭に入れれば、そうなります。
If Dir(ThisWorkbook.Path & "\" & "Alldata.xlsx") <> "" Then
Exit Sub
End If

しかしながら、マクロを興味本位でいじられることの防止にはなっていないので、根本的な解決にはならないかと思います。
この件は、マクロだけでなく、運用の問題もからみます。
私の力量の範囲を超えますので、広く他の回答者の方々の意見を収集されてはいかがでしょうか。
一旦このスレッドを閉じてから、再度、今回の事情を提示して質問されることを推奨致します。

この回答へのお礼

tatsu様
いろいろとありがとうございました。
大変勉強になりました（*^_^*）
ありがとうございます。

お礼日時：2018/06/29 11:46

No.8 回答者： tatsu99 回答日時： 2018/06/29 00:29

>取得したデータを新規ブックに保存してマクロ有効ブックは保存せずに閉じて終了しています。

>実行したマクロブックをCloseではなく、そのブックごと削除は可能でしょうか？
自分自身のブックを削除することと、理解しました。
結論から言いますと、それはできません。
マクロのあるファイルをクローズせずに、削除することはできません。
マクロのあるファイルをクローズすると、マクロ自体が消滅し、クローズ以降の命令が実行できなくなります。

自分自身のブックを削除することはしないのが通常の運用方法かと思います。
どうして、マクロのあるファイルを削除する必要なあるのか、その理由、背景などを説明していただければ、
削除せずにすむ方法があるかもしれません。

この回答へのお礼

tatsu99 様
ご連絡ありがとうございます。
どうしてマクロのあるファイルを削除したいかというと、
私個人で使用する時はよいのですが、他の人にマクロブック
を使ってもらう際、集約したいブックの入っているフォルダにそのまま
マクロブックを放り込んで、マクロを実行した後、集約したブックは.xlsx形式で
完成していて、なおかつマクロブックはそのフォルダから消えている。
が一番良い理由は、お恥ずかしながらうちの職場は、エクセルを使いこなせる方が
ほとんどいないということです。私も使いこなせていませんが・・・・
もし、そのまま残してしまうとマクロを興味本位で
いじったりして、正常に集約できていないにもかかわらずそのまま使用し、間違ったデータが出回るのを防ぎたいからです。
集約したい方がいたらマクロブックを集約したいブックのあるフォルダにいれて
マクロを実行してくださいと言えば集約されたデータは.xlsx形式で保存されますし、
そのフォルダ内から実行したマクロブックがなくなっていれば上記で説明したことが
起こる確率が低くなると感じいろいろな方法を模索しておりました。
なにか良い案があれば教えて頂けないでしょうか。
宜しくお願します。

お礼日時：2018/06/29 09:28

No.7 回答者： tatsu99 回答日時： 2018/06/27 16:49

>下記コードですが(myfolder & "\*.xls*")を"\*.xls"に変更しても

>xlsとxlsxとxlsmのデータを返します
これは、信じられません。xlsmは返らないずです。
本当に発生しているなら、VBAのバグです。
但し、あなたが、「実際に発生している」と主張されるのに対して、こちらで反証する手段がありません。
（実際に、同じ環境で、動作確認ができないので）
ですので、これ以上は、深追いできません。

>逆に"\*.xlsx"なら.xlsxデータのみ返します
これは、当然の結果です。あっています。

あなたが提示されたマクロで、.xlsと.xlsxのみ処理する場合は、以下のようになります。
・・・追加①
・・・追加②
が追加部分です。
（申し訳ありませんが、実際の動作確認はしていません。）
---------------------------------------
Set 集約book = ThisWorkbook 'このマクロ有効ブック（コピー先ブック）を集約ブックとする
myfolder = ThisWorkbook.Path 'このマクロ有効ブックが保存されているフォルダをマイフォルダと指定


datafile = Dir(myfolder & "\*.xls*") 'マイフォルダ内のExcelブック（.xlsx）を検索（）"

If datafile = "" Then Exit Sub 'マイフォルダ内にエクセルブック（xlsx）がなければ終了。

Do Until datafile = Empty 'マイフォルダ内を全て検索

If datafile <> 集約book.Name Then '集約ブックとデータ元のブック名がこのブックの名前でなければ

If LCase(Right(datafile, 4)) = ".xls" Or LCase(Right(datafile, 5)) = ".xlsx" Then '・・・追加①
Set 集約sheet = Workbooks.Open(myfolder & "\" & datafile) 'そのターゲットブックを開き集約データとする。

集約sheet.Worksheets.Copy After:=集約book.Sheets(集約book.Sheets.Count) 'コピーしてコピー先ブックの末尾に置く

集約sheet.Close '開いたブックを閉じる

bc = bc + 1 'ブック数をカウント
End If '・・・追加②


End If

datafile = Dir 'フォルダ内の次のExcelブックを検索

Loop '繰り返す

この回答へのお礼

tatsu99 様
>下記コードですが(myfolder & "\*.xls*")を"\*.xls"に変更しても
>xlsとxlsxとxlsmのデータを返します

すみません。正しくはxlsとxlsxのみ返しています。
xlsmファイルを追加して実行していません・・・テスト環境はxls,xlsxのファイルのみです。

早速①②追加して試したいと思います。
ありがとうございます。

お礼日時：2018/06/27 16:59

No.6 回答者： tatsu99 回答日時： 2018/06/27 16:02

＞LCaseは小文字に変換して検索してdatafileの右端から４文字が".xls"または

＞右端から５文字が".xlsx"のときに処理するという認識でよろしいですか？
はい。あっています。

また、ピリオドを外して下記コードにしても同じ意味になりますか？
If LCase(Right(datafile, 3)) = "xls" Or LCase(Right(datafile, 4)) = "xlsx" Then

はい。通常は同じ意味になりますが、個人的には推奨しません。
もし、取得したファイル名が、AAA.XLSとかBBB.XLSXなら問題ありませんが、
取得したファイル名が、AAA.XLSXLSとかBBB.XLSXLSX
のような場合、それも対象になってしまいます。
（通常、このようなファイルは存在しませんが、意図的にこのようなファイルを作成しておけば、
datafile=Dir(Path & "*.xls*")　　の場合、そのようなファイルも返します。）

No.5 回答者： tatsu99 回答日時： 2018/06/27 15:51

No4です。

>datafile=Dir(Path & "*.xls*")　
>上記の"*.xls*"と　"*.xls"は同じ意味になり　xlsxもxlsmも返しますよね？

上記の"*.xls*"と　"*.xls"は異なります。
①"*.xls"は、拡張子が、.xlsのみしか返しません。
②"*.xls*"は、拡張子が、.xls、.xlsx、.xlsm、等を返します。

つまり、②の方法で、余分に余分に読み込んだ.xlsmを切り捨てるために
拡張子が.xlsm、.xlsxのファイルに絞り込みを行います。
ファイル名の右側の文字が、".xls"又は".xlsx"の場合処理をするようにします。
それが、以下の
If LCase(Right(datafile, 4)) = ".xls" Or LCase(Right(datafile, 5)) = ".xlsx" Then
End If
の部分です。
尚、Windowsの場合、ファイル名が大文字／小文字を区別しないということになっていますので、
AAA.XLSもaaa.xlsもファイルとしては同じ扱いになります。その為
If Right(datafile, 4) = ".xls" Or datafile, 5) = ".xlsx" Then
End If
とすると、拡張子が小文字の".xls"か".xlsx"のみ処理してしまいます。（つまり".XLS"、".XLSX"は処理できない）
これでは都合が悪いので、大文字でも、小文字でも、処理できる等にする為に、
取得したファイル名を一旦、小文字に変換(Lcase)してから、後ろの4文字又は5文字を比較するようにしています。

この回答へのお礼

tatsu99様
丁寧な解説有難うございます。
下記コードですが(myfolder & "\*.xls*")を"\*.xls"に変更しても
xlsとxlsxとxlsmのデータを返します
逆に"\*.xlsx"なら.xlsxデータのみ返します
どこが悪いのでしょうか？


Set 集約book = ThisWorkbook 'このマクロ有効ブック（コピー先ブック）を集約ブックとする
myfolder = ThisWorkbook.Path 'このマクロ有効ブックが保存されているフォルダをマイフォルダと指定


datafile = Dir(myfolder & "\*.xls*") 'マイフォルダ内のExcelブック（.xlsx）を検索（）"

If datafile = "" Then Exit Sub 'マイフォルダ内にエクセルブック（xlsx）がなければ終了。

Do Until datafile = Empty 'マイフォルダ内を全て検索

If datafile <> 集約book.Name Then '集約ブックとデータ元のブック名がこのブックの名前でなければ

Set 集約sheet = Workbooks.Open(myfolder & "\" & datafile) 'そのターゲットブックを開き集約データとする。

集約sheet.Worksheets.Copy After:=集約book.Sheets(集約book.Sheets.Count) 'コピーしてコピー先ブックの末尾に置く

集約sheet.Close '開いたブックを閉じる

bc = bc + 1 'ブック数をカウント

End If

datafile = Dir 'フォルダ内の次のExcelブックを検索

Loop '繰り返す

お礼日時：2018/06/27 16:21

No.4 回答者： tatsu99 回答日時： 2018/06/27 15:04

>ワイルドカードを付けるとワイルドカード以降は全てヒットしますよね？なので.xlsmも対象になりますよね？できればxls,xlsxだけに絞ってデータ取得したいのですが無理ですよね？

その通りです。
その場合、余分なファイルも読み込まれるので、再度絞り込みをされてはいかがでしょうか。
datafile=Dir(Path & "*.xls*")
・・・
If LCase(Right(datafile, 4)) = ".xls" Or LCase(Right(datafile, 5)) = ".xlsx" Then
ここで、拡張子が".xls"又は".xlsx"の場合の処理を行う
End If
・・・

No.3 回答者： ShowMeHow 回答日時： 2018/06/27 14:19

そういうことですね。

拡張子を限定するのであれば、二回に分けてやればよいのでは？

この回答へのお礼

ShowMeHow様
連絡が遅れ申し訳ありません。
いろいろ参考にさせて頂き有難うございました。

お礼日時：2018/06/28 16:27

No.2 回答者： ShowMeHow 回答日時： 2018/06/27 13:04

ワイルドカードを末尾にもつけるってことです。

No.1 回答者： ShowMeHow 回答日時： 2018/06/27 12:56

"*.xls*"

でどうでしょう。