パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

エクセルマクロVBAでフォルダ内のExcelファイル全てに処理をする方法

f:id:gene320:20181104200447j:plain

フォルダ内のすべてエクセルファイルに処理をしたい場合があります。

例えば、以下のフォルダ内のエクセルファイルに同じ処理をしたいとします。

f:id:gene320:20181104191150j:plain

そして、フォルダ内の「Book1~Book5」の各エクセルファイルのセルA1~セルA5までに数値を入れることを考えます。

f:id:gene320:20181104191816j:plain

このような作業は、手作業でも可能です。しかし、数が多くなればマクロを使った方がラクに仕事を終わらすことができます。

マクロを使えば、10コでも100コでも自動で処理を行うことができます。

そこで、マクロを使って処理する方法を紹介します。

フォルダ内のExcelファイル全てに処理をするマクロの解説

この記事で紹介するマクロでやっていることについて説明します。

1. フォルダ内のエクセルファイルのみを開いて、処理をする(*A)
2. 各エクセルファイルの処理が終わったら「yymmdd_更新_元のファイル名」で保存
3. エクセルファイルの拡張子が「.xls」「.xlsx」「.xlsm」でも対応可能

*A.ここでは、数値を入れ込む処理を行っています。ワードファイルに処理は行いません。

以下で実行する前の状態と実行後の結果について詳しく紹介します。

マクロ実行前

f:id:gene320:20181104192732j:plain

1.エクセルファイルが5コ、ワードファイルが5コ

2.エクセルファイルのセル1~セル5には何も記載されていない

マクロ実行後

f:id:gene320:20181104193300j:plain

1.新しいエクセルファイルが5コ追加

もともと存在していた5つのエクセルファイルを対象したマクロを実行し、それぞれを「181104更新Book○」という名前で新しく保存した

ワードファイルに影響はしません。なお、パワポやテキストのファイルがあっても処理を行いません。

2.エクセルファイルのセル1~セル5には数字が記載される

それぞれのエクセルファイルのセル1~セル5に数字1~5が記入されます。

エクセルマクロVBAでフォルダ内のExcelファイル全てに処理をするマクロのプログラム

プログラムを紹介する前に準備をします。

VBAのプログラムを作る前にFSO操作の参照設定を行う

このマクロを使うときは、VBEの参照設定を変更する必要があります。参照設定とは、機能拡張のことです。

このプログラムを使うのには、参照設定で以下のライブラリに追加する必要があります。

設定方法

1.VBEを開いて頂いて、「ツール」→「参照設定」
2.このライブラリにチェックを入れて、OKをクリック
・Microsoft Scripting Runtime

この設定を変更しないとマクロが動きません。忘れずに設定するようにしてください。具体的な設定方法は以下の通りです。

1.VBEを開いて、「ツール」→「参照設定」

f:id:gene320:20170613213650p:plain

2.以下の2項目にチェックを入れて、OKをクリック
・Microsoft Scripting Runtime

f:id:gene320:20171229013436p:plain

なお、Microsoft Scripting RuntimeはVBAでフォルダを操作できるようにするために必要です。

VBAのプログラムは以下で紹介

このマクロの概要は以下です。

[1] 「Analysis」というフォルダを指定
[2] 「Analysis」というフォルダ内のファイルを調べる
[3] 調べたファイルがエクセルだったら、ファイルを開く
[4] 開いたエクセルファイルのセル1~セル5に数値1~5を入れる
[5] 処理したエクセルファイルを「yymmdd_更新_元のファイル名」として新しく保存

マクロのプログラムは以下です。

Option Explicit

Sub Excel_files_open()
    
    Dim newfilename, str, hiduke As String
    
    Dim fs As FileSystemObject
    Dim basefolder As Scripting.Folder
    Dim mysubfiles As Scripting.Files
    Dim mysubfile As Scripting.File
    Dim folderpath, filepath As String
    
    Set fs = New Scripting.FileSystemObject
    
    folderpath = ThisWorkbook.path & "\Analysis"
    
    Set basefolder = fs.GetFolder(folderpath)
    Set mysubfiles = basefolder.Files
    
    For Each mysubfile In mysubfiles
        
        If fs.GetExtensionName(path:=mysubfile) Like "[xl]*" Then
            
            Workbooks.Open mysubfile.path
            
            ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = 1
            ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value = 2
            ActiveWorkbook.Worksheets("Sheet1").Range("A3").Value = 3
            ActiveWorkbook.Worksheets("Sheet1").Range("A4").Value = 4
            ActiveWorkbook.Worksheets("Sheet1").Range("A5").Value = 5
            
            str = ActiveWorkbook.Name
            hiduke = Right(Replace(Date, "/", ""), 6)
            newfilename = hiduke & "更新_" & str
            
            ActiveWorkbook.SaveAs folderpath & "\" & newfilename
            ActiveWorkbook.Close
            
        End If
    Next

End Sub

0.変数の定義

Sub Excel_files_open()
    Dim newfilename, str, hiduke As String
    Dim fs As FileSystemObject
    Dim basefolder As Scripting.Folder
    Dim mysubfiles As Scripting.Files
    Dim mysubfile As Scripting.File
    Dim folderpath, filepath As String

Sub Excel_files_open()

変数を定義します。Folder、Files、FIleなどに注意して記載します。sを付ける付けないで意味が変わってしまうからです。

folderpath, filepathをString型で定義します。

1.「Analysis」というフォルダを指定

Set fs = New Scripting.FileSystemObject
folderpath = ThisWorkbook.path & "\Analysis"
Set basefolder = fs.GetFolder(folderpath)
Set mysubfiles = basefolder.Files

FileSystemObjectとは、ファイルやフォルダを操作するときに使うので、覚えておくのがお勧めです。

folderpathを「マクロの入ったエクセルのフォルダパスに存在するAnalysisフォルダ」とします。

basefolder(親フォルダ)を先ほど指定したfolderpathにします。

mysubfilesをbasefolder(親フォルダ)内の全ファイルとします。

2.「Analysis」というフォルダ内のファイルを調べる

For Each mysubfile In mysubfiles

mysubfiles(basefolder内の全てのファイル)を一つずつfor each構文を使って調べます。

3.調べたファイルがエクセルだったら、ファイルを開く

If fs.GetExtensionName(path:=mysubfile) Like "[xl]*" Then
 Workbooks.Open mysubfile.path

ファイルの拡張子を調べて、「拡張子の先頭2文字が"xl"だったら」というif文を作ります。

「拡張子の先頭2文字が"xl"だったら」がTrueなら、それはエクセルファイルなので開きます。

4.開いたエクセルファイルのセル1~セル5に数値1~5を入れる

ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = 1
ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value = 2
ActiveWorkbook.Worksheets("Sheet1").Range("A3").Value = 3
ActiveWorkbook.Worksheets("Sheet1").Range("A4").Value = 4
ActiveWorkbook.Worksheets("Sheet1").Range("A5").Value = 5

ActiveWorkbook(新しく開いたエクセルファイル)のシート「"Sheet1"」のセルA1を 1にする

ActiveWorkbook(新しく開いたエクセルファイル)のシート「"Sheet1"」のセルA2を 2にする

ActiveWorkbook(新しく開いたエクセルファイル)のシート「"Sheet1"」のセルA3を 3にする

ActiveWorkbook(新しく開いたエクセルファイル)のシート「"Sheet1"」のセルA4を 4にする

ActiveWorkbook(新しく開いたエクセルファイル)のシート「"Sheet1"」のセルA5を 5にする

5.処理したエクセルファイルを「yymmdd_更新_元のファイル名」として新しく保存

   str = ActiveWorkbook.Name
   hiduke = Right(Replace(Date, "/", ""), 6)
   newfilename = hiduke & "更新_" & str
   ActiveWorkbook.SaveAs folderpath & "\" & newfilename
   ActiveWorkbook.Close
  End If
 Next
End Sub

strをActiveWorkbook(新しく開いたエクセルファイル)の名前とする

Date(今日の日付 yyyy/mm/dd)の / を置換で削除して右から6文字取得したものをhidukeとする(つまり、hiduke=「yymmdd」となる)

newfilenameをhiduke更新strとする(つまり、newfilename=「yymmdd更新ファイル名」となる)

folderpathにnewfilenameとしてファイルを保存する

ActiveWorkbook(新しく開いたエクセルファイル)を閉じる

End if

Next

End Sub

エクセルを自分用に編集アレンジして使いたいなら、エクセルマクロの無料動画で学ぼう

この記事では、フォルダ内のExcelファイル全てを処理をする方法を紹介しました。

しかし、この記事を見たとき、「もっと○○ができるのでは?」や「ここはなんとかならないのか」と感じる人は少なくないはずです。

例えば、「他の条件を付け加えたい」や「さらに機能を増やしたい」といった要望が出るかもしれません。

このような要望を満たすには、マクロを勉強して自力でマクロを編集できるようになる必要があります

もし、自力でマクロを編集できるようになれば、今より仕事の効率はグッと上がります。

実際、私も自力でマクロを書けるようになってからは、仕事の生産性が一気に上がりました。

他の人が30分~1時間かけて行う仕事が、ボタン一つで終わらせることができるようになったのです。その結果、周囲からの信頼も増し、仕事で高い評価を得られるようになりました。

ただ、要望に応えるようになるためには、マクロを学ぶ必要があります。

まずは無料でマクロを勉強してみる

ウェブや書籍で勉強すれば、マクロを習得できると考えている人は少なくありません。

しかし、仕事で使えるマクロを習得したいなら、仕事で使える部分に特化した教材で学ぶことをお勧めします。

なぜなら、ウェブや書籍には仕事に関係しない部分まで提供していることが多いからです。

例えば、マクロ初心者なのに配列を学ぼうとする人がいます。実は配列なしでも仕事で使えるマクロを書くことは可能です。

しかし、マクロ初心者ほど「全ての知識が必要だ」と考えて、無駄な学習に時間を使ってしまうのです。詳しくは、こちらの記事で紹介しています。

www.fastclassinfo.com

www.fastclassinfo.com

そこで、私がお勧めするのは仕事に直結するマクロ教材です。とくにお勧めするのは、こちらの無料オンライン動画です。

なぜなら、仕事に直結する部分に絞って、エクセルマクロを学ぶことができるからです。

マクロの作り方・考え方から解説しているので、教材をしっかり学べばここで紹介したマクロをゼロから書けるようになります。

マクロ初心者が、仕事に直結したいマクロを学ぶなら、まずはこちらの無料オンライン動画を試すのがいいです。

興味がある人は、まずは無料でエクセルマクロの勉強を始めてみてください。

もっと学びたいと感じたら、さらに深く勉強をしてみることをお勧めします。