【VBA実践編1-1】別ファイルを開いて編集・保存する

VBAの実践編ということで、別ファイルを操作して編集するということをやっていきます。

業務では複数のファイルに対して同じ処理を行いたいということがあると思います。

その場合も、まずは1つのファイルを編集するというところから始めます。

その後複数処理できるようにVBAを改良していく、という流れで進めていきます。

今回のVBAでやりたいこと

上記のようなレンタル履歴を表したCSVファイルがあります。

4列ある表で、1行目はヘッダー（項目名）になります。

タイトル列だけが必ず入力されていて、他の列は入力されている場合とそうでない場合があります。

この１～５の処理をVBAで書いていきます。

別ファイルを開いて編集するVBAコード

VBAが書かれたファイルと上記のCSVファイルを同じフォルダにあり、そのフォルダ内に「rented」というフォルダがあります。

下記が今回使用するVBAコードです。

Sub Rental()
'編集するファイルのパスを取得する
Dim fileName As String
fileName = "rental1.csv"
Dim filePath As String
filePath = ThisWorkbook.Path & "\" & fileName

'ファイルを開く
Dim wb As Workbook
Set wb = Workbooks.Open(fileName:=filePath)

Dim ws As Worksheet
Set ws = wb.ActiveSheet

'開いたファイルの最終行を取得する
Dim lRow As Long
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'2列目と3列目の1行目から最終行まで同じ内容を入力する
ws.Cells(2, 2).Resize(lRow - 1, 1).Value = 1
ws.Cells(2, 3).Resize(lRow - 1, 1).Value = Date

'4列目にメモを入力する（行ごとに処理を分ける）
Dim memo As String
memo = "customer1"
Dim i As Long
For i = 2 To lRow
    If ws.Cells(i, 4).Value = "" Then
        ws.Cells(i, 4).Value = memo
    Else
        ws.Cells(i, 4).Value = ws.Cells(i, 4).Value & vbCrLf & memo
    End If
Next i

'日付の書式を変更する 例）2020/03/05 00:00
ws.Cells(2, 3).Resize(lRow - 1, 1).NumberFormatLocal = "yyyy/mm/dd hh:mm"

'保存するパスを指定する
Dim savePath As String
savePath = ThisWorkbook.Path & "\rented\" & Format(Now, "yyyymmddhhmmss") & wb.Name

'ファイルを別名保存して閉じる
wb.SaveAs fileName:=savePath, FileFormat:=xlCSV, local:=True
wb.Close savechanges:=False

End Sub

VBAコードの解説

細かい解説はこちら

編集するファイルを開く

'編集するファイルのパスを取得する
Dim fileName As String
fileName = "rental1.csv"
Dim filePath As String
filePath = ThisWorkbook.Path & "\" & fileName

 

まずは編集したいファイルがある場所を取得します。

ファイルを操作するにはファイルパスが必要になります。

ファイル名は今回は固定で「rental1.csv」にしています。

これに「Thisworkbook.Path」をつなげます。

「Thisworkbook.Path」とは今開いているファイル（今回はVBAが書かれているファイル）がある場所を返してくれます。

今回はCドライブに「movie_reta」というフォルダを作ってそこにファイルを格納しているので、「C:\movie_retal」が返ってきます。

このパスとファイル名を「￥」マークでつなぎます。

'ファイルを開く
Dim wb As Workbook
Set wb = Workbooks.Open(fileName:=filePath)

 

そして、「Workbooks.Open」でファイルを開きます。

ファイルを開くには、さきほど取得したファイルパスが入っている変数：filePathを引数に指定します。

「Workbooks.Open」には引数がいくつかありますが、今回は単純に開くファイルパスだけを指定しています。

開いたファイルは「wb」という変数にセットしておきます（変数前のSetを忘れず！）。

編集するシートと最終行を取得する

Dim ws As Worksheet
Set ws = wb.ActiveSheet

'開いたファイルの最終行を取得する
Dim lRow As Long
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

 

次に、開いたファイルの編集するシートを取得します。

CSVファイルは基本１つしかシートがないので、開いたときのシート（つまりアクティブシート）を編集するシートとして取得します。

そして、編集する内容が入力されている行の最終行を取得します。

タイトル列の１列目は必ず入力されているという前提ですので、１列目の最終行を取得します。

 

セルの内容を編集する

'2列目と3列目の1行目から最終行まで同じ内容を入力する
ws.Cells(2, 2).Resize(lRow - 1, 1).Value = 1
ws.Cells(2, 3).Resize(lRow - 1, 1).Value = Date

 

ここからがいよいよセルの編集です。

最初に書いたVBAで行いたい処理の１～４のメイン処理です。

2、3列目はすべての行が同じ内容で良いので、まとめて値を入力するようにします。

複数のセルに対して同じ内容を入力する場合、Resizeを使うのが便利です。

Resizeは基準になるセルから、範囲を広げるときに使います。

今回の場合、1行目はヘッダーなので処理は行わないので、2行目から最終行までが処理を行いたい範囲です。

まず、ws.Cells(2, 2)で基準セルをセットし、Resize(lRow – 1, 1)で範囲を広げます。

lRowをマイナスしているのは、ヘッダー行分を減らしているためです。

最終行が5行だったら、2行目から5行目（つまり4行分）に処理をしたいので、最終行5から1を引いているということです。

列は広げない場合は1列を指定します。

これで範囲が指定できたので、あとは値をセットするだけです。

'4列目にメモを入力する（行ごとに処理を分ける）
Dim memo As String
memo = "customer1"
Dim i As Long
For i = 2 To lRow
    If ws.Cells(i, 4).Value = "" Then
        ws.Cells(i, 4).Value = memo
    Else
        ws.Cells(i, 4).Value = ws.Cells(i, 4).Value & vbCrLf & memo
    End If
Next i

 

4行目は行によって処理を変えていきます。

セルにすでに入力されていた場合、その内容の後ろに今回の値を追加します。

すべての行を順番に見ていくのでfor文で処理をします。

セルの値を1行ずつ見ていき、値がなければそのままセルに値をセットし、すでにある場合は、後ろに追加するという分岐にしています。

追加のときの「VbCrLf」というのは改行コードです。

そのまま値を追加すると分かりづらいので今回はセル内で改行をしています。

'日付の書式を変更する 例）2020/03/05 00:00
ws.Cells(2, 3).Resize(lRow - 1, 1).NumberFormatLocal = "yyyy/mm/dd hh:mm"

 

最後に日付の書式を修正します。

「NumberFormatLocal」はセルの書式を変更するプロパティです。

今回は「日付+0：00」にしたいので「yyyy/mm/dd hh:mm」とします。

そしてすべて行に適用するのでResizeを使用します。

ファイルを名前をつけて別名保存する

'保存するパスを指定する
Dim savePath As String
savePath = ThisWorkbook.Path & "\rented\" & Format(Now, "yyyymmddhhmmss") & wb.Name

 

最後に編集したファイルを指定したフォルダに名前をつけて保存します。

保存先の場所とファイル名を指定する必要があります。

保存先は「rented」フォルダで、ファイル名は保存した日時+開いたファイル名とします。

これを「\」マークでつなぎます。

'ファイルを別名保存して閉じる
wb.SaveAs fileName:=savePath, FileFormat:=xlCSV, local:=True
wb.Close savechanges:=False

 

保存するには「SaveAs」メソッドを使用します。

引数には保存するファイルパス（fileName）、ファイルの形式（FileFormat）、言語設定（Local）を指定します。

言語設定をなぜ指定するかというと、指定しなかった場合、CSVファイルの日付形式が変更されてしまうためです。

今回は日付の形式をわざわざ変更しているので、この形式を活かすために「Local:=True」としています。

最後にファイルを「Close」で閉じます。

これで「rented」フォルダに新しいファイルが保存できます。

まとめ

今回は実践編として、ファイルを開いて編集をするということを行いました。

実務でVBAを使うときには必ずこうしたファイル操作が必要になります。

自分が行いたい処理とファイルを操作という2つの処理を組み立てていけば色々とできるので、ここで紹介しているコードを自分なりにアレンジしてみてください。