任意のフォルダ内のすべてのファイルを取得する(VBA/Excel2000以降)

今日は、任意のフォルダ内にあるすべてのファイルを取得する方法を取り上げます。

もちろん、サブフォルダ内のファイルもすべて取得します。

今回は、エクセルを使ってやってみましょう。
VBでも、同様の論理でできますので、興味のある方はチャレンジしてみてください。

エクセルの利点は、取得したファイル名をセルに書き出すことができる点でしょう。

ありがたいですね。


それでは、エクセルを起動してください。

ALTキー + F11キー　で、Visual Basic Editorを起動してください。


次に、「挿入」メニューから、「標準モジュール」をクリックしてください。

これで、コードを書く準備が出来ました。


今回は、ファイルやフォルダを扱うので、専用の部品を使ってみます。
「ファイルシステムオブジェクト」といいます。

ただし、これは、いきなり使用すると手ごわいので、まずは、参照設定をして、
使いやすいようにします。


「ツール」メニューの「参照設定」をクリックします。





表示されたダイアログボックスの「参照可能なライブラリ ファイル」の一覧から、
「Microsoft Scripting Runtime」を見つけ出して、チェックを入れてください。

OKをクリックして、ダイアログボックスを閉じます。






さあ、それでは、コードを記述しましょう。

以下のように入力してください。

Option Explicit
Private fso As FileSystemObject
Private c As Integer

Sub getFiles(path As String)
    Dim fs As folders
    Dim f As folder
    Dim mFile As File
    
    For Each mFile In fso.GetFolder(path).Files
        Cells(c, 1).Value = mFile.Name
        c = c + 1
    Next
    Set fs = fso.GetFolder(path).SubFolders
    If fs.Count = 0 Then Exit Sub
    For Each f In fs
        getFiles (f.path)
    Next
End Sub

Sub test()
    Dim FolderPath As String
    
    Set fso = New FileSystemObject
    FolderPath = "C:sample"
    c = 1
    getFiles (FolderPath)
    Set fso = Nothing
End Sub

参考までに、画像を載せておきます。





getFiles　が、実際の処理をするプロシージャです。
再帰呼び出しを駆使して、サブフォルダ内のファイルを取得できるようになっています。

test　が、任意のフォルダを指定して、先ほどのgetFilesを呼び出すプロシージャですね。

このプロシージャの中の、

FolderPath = "C:\sample"

のダブルコートの部分を、任意のフォルダ名に変更してください。

たとえば、マイドキュメントなら、

C:\Documents and Settings\[ここにユーザー名を入れてください]\My Documents

となりますね。



それでは、エクセルに切り替えて、マクロを実行しましょう。

ALTキー + F8キー を押してください。




エクセルのシートにファイル名の一覧が書き出されます。





たくさんのファイルが保存されているフォルダを指定すると、
結構時間がかかったりしますので、ご注意くださいね。



だい

ユーザー定義関数を作ってみよう(Excel全バージョン共通)

今日は、ユーザー定義関数のご紹介をします。

エクセルは、300以上の関数が用意されていますが、
VBAを使って、オリジナルの関数を作ることもできます。


今回は、生徒さんからの質問というか要望を取り上げてみます。


以下のような表があります。
紙面の都合で、データの数は、11行ですが、実際は、300行ほどあります。





「C列の規格に入っている数字と単位を分離してE列とF列に入れたい。簡単にできないか」というのが、
生徒さんからの要望でした。

データを見ると、単位の文字数もまちまちですし、
数字の桁数もまちまちです。

規則性があれば、最初から何文字とか、最後から何文字とかいうロジックで
対処できますが、今回はそうは行きません。

そこで、データの文字列を頭から1文字ずつ取り出して、
それが数字なのかどうかで、判断するというロジックを採用します。

数字だけを取り出す関数と、文字だけを取り出す関数を作れば、
今回の生徒さんの要望にこたえられそうですね。


これは、やはりVBAを使わないとできませんので、まずは、VBEを起動しましょう。

ALTキー　+　F11キー　を同時に押して、
Visual Basic Editorを起動します。

「挿入」→　「標準モジュール」とクリックして、
コード入力画面を表示します。

以下のようにコードを記述します。

Option Explicit

Public Function getNum(str As String) As Long
    Dim i As Integer
    Dim c As Integer
    Dim buf As String
    
    c = Len(str)
    buf = ""
    For i = 1 To c
        If IsNumeric(Mid(str, i, 1)) Then
            buf = buf & Mid(str, i, 1)
        End If
    Next
    
    If buf = "" Then
        getNum = 0
        Exit Function
    End If
    getNum = CLng(buf)
End Function

Public Function getStr(str As String) As String
    Dim i As Integer
    Dim c As Integer
    Dim buf As String
    
    c = Len(str)
    buf = ""
    For i = 1 To c
        If Not IsNumeric(Mid(str, i, 1)) Then
            buf = buf & Mid(str, i, 1)
        End If
    Next
    getStr = buf
End Function

下の画像のようにしてくださいね。




これで、2つのユーザー定義関数ができました。

あとは、この関数を使って、単位量と単位の列にデータを表示します。


エクセルの画面に切り替えて、E列2行目を選択します。①（下の画像を参照）

関数の挿入ボタンをクリックします。②

関数の分類を「ユーザー定義」にします。③

getNum関数を選択します。④

最後に、OKボタンを押します。






次に表示される画面で、C列2行目を選択してから、OKをクリックします。





すると、E列2行目に　数字が表示されます。





C列2行目の値から、ちゃんと数字だけ取り出されてますね。

それでは、次に　単位を取り出してみましょう。

F列2行目を選択してから、関数挿入ボタンをクリックして、
今度は、getStr関数を選択します。
あとは、最初の関数と同様にやってみてください。






以下の画像のように、kg　が、F列2行目に入ります。





あとは、数式のコピーをして完了です。
オートフィルを使いましょう。

E2:F2　を選択して、右下のフィルハンドルを出したら、
12行目までドラッグします。





はい、完成です。





このように、必要に応じてユーザー定義関数を作成すれば、
作業効率もアップしますよ。




だい

エクセルからインターネットエクスプローラを起動する(Excel2002～2007)

さあ、今日は、エクセルからインターネットエクスプローラを起動してみましょう。

「まず、表示したいサイトのアドレスをA列2行目に入力しましょう。」

「先生、何でもいいですか？」

「いいですよ。でも、折角ですから、このブログのアドレスでやってみましょう。」

「そう来ましたか。でも、先生、アドレスを入力したら、
勝手に青字に下線がついて、クリックしたら、なんと！
インターネットエクスプローラが立ち上がって、サイトが表示されましたよ！」



「それは、ハイパーリンクという機能で、Excel2007は、
自動で設定されるんだね。
他のバージョンは、自力で設定するんだよ。
でも、今回は、ハイパーリンクではなく、
VBAでやってみようじゃないか！」

「そう来ましたか。分かりました。それでは、先生教えてください。」

「Altキーを押したまま、F11キーを押して、
Bisual Basic Editorに切り替えます。
そして、「挿入」メニューから「標準モジュール」をクリックします。」

「さっそく、コードを記述するんですね。」

「そう言いたいところですが、今回は、その前にやるべきことがあります。
それは、参照設定です。」

「何ですか？それ。」

「プログラムの部品を使えるようにすることです。
今回は、インターネットエクスプローラを制御する必要がありますね。
そのために、専用の部品を使える状態にするわけです。」

「そうですか。じゃあやります。」

「もっと楽しそうな顔をしてくださいよ。そうそう、笑って。
「ツール」メニューの「参照設定」をクリックします。



すると、ダイアログボックスが表示されます。



ふつうは、左側の参照可能なライブラリファイルの一覧から、
必要なものにチェックを入れればいいのですが、
何と、今回は、この中にありません。」

「ええ、それじゃ、どうすればいいのですか？」

「そんな時は、画面右側にある参照ボタンをクリックして、探しに行きます。」

「えらいこっちゃ。」



上の図のように、system32フォルダの中から、shdocvw.dllを選択して
『開く』ボタンをクリックしてください。
なお、system32フォルダは、windowsフォルダの中にあります。念のため。」



上の図のように、参照設定ダイアログボックスに戻ります。
Microsoft Internet Controlというところにチェックが付いているでしょ。
これが、今回追加したライブラリファイルですよ。」

「なんだか、大変だなあ。今回は。」

「あとは、コーディングするだけですよ。数行ですからがんばりましょう。」

「ラジャー」

「以下のように入力してください。」




1行目の　Option Explicit は、省略してもOKです。

コピペしたい人のために、コードを書いておきますよ。

Sub DispIE()
　　Dim ie As New InternetExplorer

　　ie.Navigate Range("A2").Value
　　ie.Visible = True

End Sub

「以上で、終了です。さあ動作確認してみましょう。」

「ということは、エクセル画面に切り替えるんですね。」

「そうです。」

「Alt + F8　で、DispIE　を実行してください。
2003までのバージョンの人は、
「ツール」→「マクロ」→「マクロ」でもいいですよ。」

「本当だ！立ち上がったぞ～～。」




「はい、よくがんばりました(^^)
A列2行目のアドレスを色々と変えてやってみてください。」

だい

文字の入力されたセルをすべて選択する（Excel VBA）

「とにかく、文字や数値の入力されたセルを全部選択したい。
こんなことは、可能でしょうか？」

生徒さんからの質問です。

「大丈夫。できますよ。」

「つながったセル範囲でなくてもできますか？」

「大丈夫。できますよ。」

「早く教えてくださいよ～」

「はいはい、今回はVBAでやってみましょうか(^^)」

題材は、以下の表です。
ちなみに、D列の本体価格は、計算式で求めています。



Alt＋F11 で、Visual Basic Editorを起動します。

挿入メニューの「標準モジュール」をクリックします。

以下のように記述してください。

Sub セルの選択2()
　　Selection.SpecialCells(xlCellTypeConstants, 23).Select
End Sub

ちなみに、Subのあとには、マクロ名が来ます。
これは、自分で好きな名前をつけてかまいません。
（最初に半角の数字は使えません。記号も使用しないでください）



さあ、エクセルに切り替えて、
Alt + F8 キーを押して、マクロを実行してください。

どうですか。タイトルと表内の文字と数値が選択されていますね。



計算式は選択されていないことにも注目してください。

「へえ、こんなことが出来るんですね。
でも、タイトルの文字は選択しないように出来ますか？」

「なるほど、タイトル以外は、全部選択したいというわがままなリクエストですね。ふふ、それもできますよ。」

「それは、ありがたいです。」

「やり方は、いたって簡単。さきほどのマクロで実は、対応できますよ。
実行する前に、タイトル以外の行を選択しておいてください。
それから、マクロを実行すると、ほら、できるでしょ。」

「あらら、驚いた」



（上の絵のようにまず選択してから、マクロを実行します）

「マクロを使わない方法も紹介しましょう。

編集メニューのジャンプをクリックします。
左下の「セル選択」をクリックします。



定数を選択してOKを押します。

ほら、これでもできるでしょ」




「エクセルも、やればやるほど　面白いですね」

だい

エクセルからIE（インターネットエクスプローラー）を起動する(Excel VBA)

さあ、今日は、エクセルからIE（インターネットエクスプローラー）を起動してみましょう。

まず、任意のエクセルファイルを開きます。
新規作成してもかまいません。

Alt＋F11 で、Visual Basic Editorを起動します。

今回は、標準モジュールにコーディングします。

挿入メニューの「標準モジュール」をクリックします。



以下のように、入力してください。

Sub IEBoot()
　　Dim ie As Variant

　　Set ie = CreateObject("InternetExplorer.Application")
　　ie.Visible = True
　　ie.navigate "http://www.yahoo.co.jp"

End Sub

参考までに、画像です。
(Option Explicit　は、入力してもしなくてもいいです）


エクセル画面に切り替えます。

ツールメニューのマクロの「マクロ」をクリックして、
一覧の中から、IEBoot　を選択して、「実行」ボタンを押してください。



すると、インターネットエクスプローラーが起動します。




今回は、Yahooのサイトが表示されていますが、
コードの最終行の
http://www.yahoo.co.jp
を、任意のURLに置き換えると、そのサイトが表示されますよ。


だい

任意の列で自動移動する（ExcelVBA)

これも、生徒さんからのエクセルについての質問。

以下の表があります。



「３項目入力したら、自動的に１行下の日付のセルに移動したら、
入力が楽になるんですけど・・・
何とかなりますか？」

そんな生徒さんの質問に僕は答えた。

「大丈夫ですよ。一緒にやってみましょう。」

まずは、Enterキーで、右横のセルに移動するように、
設定を変更しましょう。
ツールメニューのオプションをクリックします。
編集タブをクリックします。



図のように、「右」に変更したら、「OK」を押して閉じます。

これで、入力してEnter　という流れが出来ました。

でも、これでは、数量欄に入力した後は、さらに右隣の
セルに移動してしまい、自分で、１行したの日付のセルを選択
しなければなりません。

さあ、ここからが、腕の見せ所！！

Altキーを押したまま、F11キーを押すと、
Bisual Basic Editorが起動します。



上の図のマウスがあるところの、Sheet1を
ダブルクリックしてください。
（今回の表は、シート1にあることを前提にしています。
シート2にある場合は、Sheet2をダブルクリックしてください。）

ここに、プログラミングするのですよ。
といっても、わずか数行ですから、頑張りましょう。

さあ、下の絵の囲んだ部分の▼をクリックして、
WorkSheet を選択してください。



すると、

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

と、自然に出てきます。
その中に、以下を入力してください。

Const RetsuNo As Integer = 5

If Target.Column = RetsuNo Then
　　Target.Offset(1, -3).Select
End If


下の絵を参考にしてください。
なんなら、上のコードをコピペしてください。



はい、よくできました。
これで終了です。

タスクバーのエクセルボタンをクリックして、
エクセルに切り替えます。
（Bisual Basic Editorの画面で上書き保存ボタンを押さなくても大丈夫です。）


さあ、入力してみてください。

日付を入力してEnterキーを押す。右に移動する。
商品を入力してEnterキーを押す。右に移動する。



数量を入力してEnterキーを押す。すると・・・



ほら、自動的に、次の行の日付のセルが選択されています。

1万行でも2万行でも　ちゃんと、次の行の日付セルに移動できますよ。(^^)

補足：
入力したコードの
Const RetsuNo As Integer = 5
の　5　は、5列目という意味です。つまりE列のことです。
つまり、E列のセルが選択されたら、1行下のB列のセルを選択する
という内容のコードが記述されています。
F列で自動移動したかったら、
Const RetsuNo As Integer = 6
にしてください。

だい