Excel VBA(エクセル マクロ) そのI フォルダ内のファイル名検索,取得、シート名検索,取得

フォルダ内のExcelファイルのデータを取り込みたい。
そのためにはファイル名検索、およびシート名検索が必要だ。

検索、置換が行えるようになった。

次は、フォルダ内のExcelファイル名、および、Excelファイル内のシート名の検索を行う。

外部ファイルをコピーする際、ファイル名とシート名を指定しなくてはならず、今までは手入力だったのを、自動化する狙い。


Dim DHfile As String

Sheets("用語").Select
Cells(14, 3).Value = Dir(ThisWorkbook.Path & "\" & "DH*.xls")

DHfile = Cells(14, 3).Value

'ファイルのオープン
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & DHfile, UpdateLinks:=0

Workbooks(myname).Worksheets("用語").Cells(14, 4).Value = Workbooks(DHfile).Sheets(1).name

'ファイルのクローズ
With Workbooks(DHfile)
.Saved = True
.Close
End With


DIR関数を使い、マクロを実行しているExcelファイルがあるフォルダ「ThisWorkbook.Path」内で先頭に「DH」がつくxlsファイルを検索してCells(14, 3).Valueに代入。ここで、ThisWorkbook.Pathは最後に「¥」が付かないため、ファイル指定の際に「&」を使用して「¥」を挿入する。

Cells(14, 3).Value = Dir(ThisWorkbook.Path & "\" & "DH*.xls")

ChDir ThisWorkbook.Pathでパスを指定しても、エラーになる可能性があるためこちらを使用したほうが良い。


以下のように、WorkbooksからSheets(1)の名前を抽出しようとしたら、エラーとなった。やはり、Workbooksを開いた状態にしないと、シート名は検索できないようだ。

Cells(14, 4).Value=Workbooks(myname).Sheets(1).name


ということで、ファイルのオープンを行う。

'ファイルのオープン
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & DHfile, UpdateLinks:=0

UpdateLinks:=0は「ファイルの更新をしますか」というダイアログが出て止まるのを防ぐ。


次は、シート名(1ページ目)を抽出。他ファイルが開いた状態なので、Workbooks(myname).Worksheets("用語").Cells(14, 4).Valueとワークブック、ワークシートを指定して、Cells(14, 4).Valueに抽出した値を代入。

Workbooks(myname).Worksheets("用語").Cells(14, 4).Value = Workbooks(DHfile).Sheets(1).name


最後に、ファイルをクローズ。

With Workbooks(DHfile)
.Saved = True
.Close
End With

セーブの確認のダイアログが出て止まらないようにするため、.Saved = Trueとする。


以上で、DHで始まるエクセルファイル名、1ページ目のシート名を検索可能となった。


シート名まで検索したい場合は下の文章を変更。

Workbooks(myname).Worksheets("用語").Cells(14, 4).Value = Workbooks(DHfile).Sheets(1).name

Dim bookname As Workbook, line1 As Integer
Set bookname = Workbooks(DHfile)
Dim s As Worksheet
line1 = 31
For Each s In bookname.Worksheets
Workbooks(myname).Worksheets("用語").Cells(line1, 2).Value = s.name
If InStr(s.name, "発表用") > 0 Then
Cells(line1, 2).Value = s.name
End If
line1 = line1 + 1
Next

DHfileのエクセルファイル、ワークシートをそれぞれ、DimでWorkbook、Worksheetで宣言して、For Each s In bookname.Worksheetsでエクセルファイルのワークシート全てをsに代入。
s.nameがワークシートの名前となるので、

If InStr(s.name, "発表用") > 0 Then

で、s.nameに「発表用」の言葉があるか判定する。

InStr(s.name, "発表用") は、「発表用」がs.nameの何文字目にあるかを検索し、なければ0を返すので、0以上であれば、「発表用」の言葉がありと判定する。

これで、シート名の検索が可能となる。

これを発展させると、フォルダ内の全てエクセルファイル、および、全てのシートについて、ある文字があるかどうかを検索可能となる。

これは本業と離れてしまうので、プログラムは後で作ることとする。

さあ、最後の山場。グラフ化を目指す。
忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP