ワークブックの新規作成と保存 – ExcelVBAの散策とruby

2016/04/16

参考サイト：

• Excel VBA 入門講座 ワークブックの作成
• Excel VBA 入門講座 ワークブックの保存

《目次》

• 1.　概要
  • (1)　ワークブックの追加(Workbooks.Add)
  • (2)　ワークブックの保存(SaveAs)の基本
  • (3)　ワークブックの保存(SaveAs)のパラメータ
  • (4)　ワークブックのプロパティ
• 2.　マクロを登録するスクリプト
• 3.　win32oleのスクリプト
• 4.　exlapのスクリプト
• 5.　CSVおよびTXTでの保存

1.　概要

　ワークブックの追加(新規作成)は Workbooks.Add で行います。

　Addメソッドが正常に実行されると開かれたワークブックがアクティブワークブックになります。

　ワークブックの保存は SaveAs メソッドで行います。

　ActiveWorkbook.SaveAs "C:\test", xlWorkbookNormal のように記述します。

　ワークブックには、それがどんなワークブックなのかを示すいろいろなプロパティがありますが、主なプロパティを紹介します。

(1)　ワークブックの追加(Workbooks.Add)

　Workbooks.Add を実行すると、新しい空のワークブックが追加されて、それがアクティブワークブックになります。

　ワークブック名は Book1 です。拡張子は、とりあえずなしの状態。

　もう１回 Workbooks.Add を実行すると、Book2という名前のワークブックが新たに作られます。

含まれるワークシートの枚数を調整

　通常、新たに追加されたワークブックにはワークシートが３枚あります。Sheet1, Sheet2, Sheet3 です。

　ワークシートが2枚だけあればいいなら、Application.SheetsInNewWorkbook の値(整数)を調整します。次のようにします。

Sub Macro1()
    Dim SNum As Integer
    SNum = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 2
    Workbooks.Add
    Application.SheetsInNewWorkbook = SNum
End Sub

　Application.SheetsInNewWorkbook の値は、変更しっぱなしだと後々困るので、変数に待避して、Workbooks.Addを実行した後で復元しています。

　この待避・復元による方法は、枚数を任意に指定できますが、プログラムが長くなって面倒です。

ワークシートが１枚だけのワークブックを設ける

　ワークシートが１枚だけのワークブックを新規作成する方法があります。Addメソッドにパラメータを渡します。下のようなマクロになります。

Sub Macro1()
    Workbooks.Add xlWBATWorksheet
End Sub

　上を実行すると、Book1 というワークブックではなく Sheet1 という名前のワークブックが作られます。ワークブック名が Sheet1 というのはなんだか妙ですが。

　上のマクロを更にもう１回実行すると、Sheet2 という名前のワークブックができます。

　xlWBATWorksheet(整数値 -4167)というパラメータを指定するのは、気分として「ほしいのはワークブックというよりワークシートだ！入れ物としてワークブックが必須だというなら、適当なものでいい」というニュアンスです。そのため、このパラメータを指定すると、ワークシートと同名のワークブックになります。

　ワークシートの名前の変更のところで、ワークシート名として、ドライブ名・フォルダ名・ファイル名の区切り文字(‘:’, ‘\’ など)およびワイルドカード文字(‘*’, ‘?’ など)を使うことができない旨を書きましたが、なぜそうなのかの理由が「ワークシート名がワークブック名として使われることがあるから」です。

目次に戻る

(2)　ワークブックの保存(SaveAs)の基本

　ワークブックの保存は SaveAsメソッドで行います。これは、Excelのメニューの「名前を付けて保存」に当たるメソッドです。

　ActiveWorkbook.SaveAs "C:\test", xlWorkbookNormal とすれば、アクティブワークブックが C:\test.xls として保存されます。

ファイル名の拡張子を指定するときの注意点

　注意しなければならないのは、SaveAs のパラメータを “C:\test.xls” とせずに “C:\test” とする点です。拡張子を付けません。

　拡張子は、２番目のパラメータで指定します。次のものが指定可能です。

• xlWorkbookNormal(整数値 -4143)：　.xls
• xlOpenXMLWorkbook(整数値 51)：　.xlsx
• xlOpenXMLWorkbookMacroEnabled(整数値 52)： .xlsm

　他にもいろいろ指定できますが、ここでは省略します。

　SaveAs のパラメータを “C:\test.xls” のように拡張子付きで指定し、２番目のパラメータを省略すると、Excel2010の私の環境では正常にオープンできないワークブックが保存されてしまいます。

　Excel2010では、拡張子 .xlsx が標準です。なので、２番目のパラメータがないと xlsx形式で保存されます。しかし、ファイル名が test.xls になっていると「名前と実態が食い違う」ということになります。結果、正しくオープンできないものになります。

　食い違わなければトラブルは起きませんが、１番目のパラメータには拡張子を付けず、第２パラメータで拡張子を指定するのが無難です。

ファイル名にドライブ名やフォルダ名をつけない場合

　１番目のパラメータ(ファイル名)を、ドライブ名・フォルダ名なしで単２ “test” とすれば、通常、ユーザーのドキュメントフォルダ(マイドキュメント)に保存されます。

　それ以外のフォルダに保存したい場合は、ドライブ名・フォルダ名を付けてファイル名を指定します。

　Excel関連の解説では、「完全パスを含めない場合は、ファイルは現在のフォルダに保存されます」のようによく書かれていますが、Excelにとっての「現在のフォルダ」は、ドキュメントフォルダ(マイドキュメント)のことのようです。

ファイル名を「既に存在するファイル」の名前にした場合

　SaveAsで注意すべきもう一つの点は、既に存在するファイル名と同名にならないようにするということです。

　重複するファイル名を指定した場合は、「置き換えますか」というダイアログボックスが出ます。上書き保存するかどうかの確認メッセージ(一種の警告メッセージ)です。

　それを出さなくするには Application.DisplayAlerts を False にします。ただ、そうすると問答無用で上書き保存されてしまうので少々不安です。それへの対処方法としてバックアップを残すやり方がありますが、それについては次項で触れます。

　文章での説明が続いたので、具体的なマクロコードを掲げておきます。

Sub Macro1()
    Workbooks.Add
    Range("A1").Value = "test"
    Application.DisplayAlerts = False  ' 「置き換えますか」を抑制
    ActiveWorkbook.SaveAs "C:\test", xlWorkbookNormal
    Application.DisplayAlerts = True
End Sub

目次に戻る

(3)　ワークブックの保存(SaveAs)のパラメータ

　SaveAsメソッドには様々なパラメータがあります。パスワードを設定するかどうか、バックアップファイルを残すかどうかなどを指定できます。

　パラメータを順番に名前だけ上げると次のとおり。

FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended,
CreateBackup, AccessMode, ConflictResolution, AdToMru, TextCodepage,
TextVisualLayout, Local

　パラメータを上の順番に従って列記するなら SaveAs の後に単純に並べればいいのですが、パスワード関連は飛ばして CreateBackup(バックアップの作成)を指定したいといった場合は、下のように書きます。

ActiveWorkbook.SaveAs FileName:="C:\test", _
FileFormat:=xlWorkbookNormal, CreateBackup:=True

　パラメータ全部について述べるのは大変なので、気になるものについて簡単に記します。

FileName

保存するファイルの名前(文字列)

FileFormat

ファイルを保存するときのファイル形式(ファイル名の拡張子に対応)

Password

ファイルを保護するためのパスワード(15文字以内の文字列)。パスワードを設定して保存したファイルは、開く時にパスワードが要求されます。

WriteResPassword

ファイルの書き込みパスワード(文字列)。パスワードを設定して保存したファイルをパスワードを指定せずに開くと、ファイルは読み取り専用で開かれます。

ReadOnlyRecommended

これをTrueにすると、ファイルを開く時に読み取り専用で開くことを推奨するメッセージが出ます。

CreateBackup

これをTrueにするとバックアップ ファイルが作成されます。test.xlsのバックアップは「test のバックアップ.xlk」といった名前になります。

AddToMru

これをTrueにすると、最近使用したファイルの一覧にワークブックが追加されます。

目次に戻る

(4)　ワークブックのプロパティ

　ワークブックのプロパティのうち、比較的よく参照しそうなものを記してみます。

Name

ワークブックの名前。新規作成された直後は Book1 のような名前。SaveAsで保存すると、Book1.xls のような名前になります。ドライブ名とフォルダ名は含まれません。

FullName

ワークブックのファイル名(フルネーム)。新規作成された直後は Book1 のような名前。SaveAsで保存すると、C:\Book1.xls のような名前になります。

Path

ワークブックのパス名。新規作成された直後は “” の空文字列。SaveAsで保存すると、C: とか C:\Users のような名前になります。ドライブ名・フォルダ名・ファイル名からファイル名を除いたものです。Path & "\" & Name が FullName になるという関係です。

FileFormat

ワークブック(ファイル)の形式(xls, xlsx, xlsm など)を示す整数値。

Author

ワークブックの作成者を示す文字列。

CreateBackup

バックアップファイルを作るかどうかを示すフラグ(True, False)。

Saved

保存済みかどうかを示すフラグ(True, False)。Save, SaveAsの実行によりExcelファイルとして保存すると、このフラグがTrueになります(ただし、CSVなどで保存してもTrueにならない)。これがFalseの場合、ワークブックをCloseメソッドで閉じようとすると、保存するかどうかの確認メッセージが出ます。

　FileFormatについて少し補足します。

　Excel2010の場合、SaveAsメソッドで xlWorkbookNormal(整数値 -4143)を指定してワークブックを保存すると、保存後の FileFormat の値は xlExcel8(整数値 56)になります。どちらの整数値も拡張子 .xls に対応するものです。

　ただ、Excel2003までだと xlExcel8 を使えなかったと思います。なので、Excel2003以前のバージョンでも動かせるようにするには xlWorkbookNormal の方を使うのが無難ということになります。

目次に戻る

2.　マクロを登録するスクリプト

　「概要」でいろいろ述べました。それらを一通り盛り込むのは大変なので、ごく一部しか取り上げられませんが、サンプルを掲げてみます。

　これまでと同様に、マクロには Control + j というショートカットキーを割り当てます。Book1.xlsを開いてから、このショートカットキーを入力すると、次の処理が行われます。

• 2枚のシートからなるワークブックを新規に作成
• そのブックを Book1_TwoSheets.xls という名前で保存
• シートが1枚だけのワークブックを新規に作成
• そのブックを Book1_OneSheet.xls という名前で保存。パスワード(abcの３文字)を設定。

　マクロ実行後にExcelを終了させてから、Book1_TwoSheets.xls, Book1_OneSheet.xls を改めて開くと、意図通りに保存されているか確認できます。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Sub Macro1()
     Dim OrgPath As String
         Dim SNum As Integer
     OrgPath = ActiveWorkbook.Path & "\"  ' Book1.xlsのパス名
     SNum = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 2  ' 2枚のシートからなるブック
    Workbooks.Add
    Application.SheetsInNewWorkbook = SNum
    Worksheets(1).Range("A1").Value = "2枚のシートの1枚目"
    Worksheets(2).Range("A1").Value = "2枚のシートの2枚目"
    ActiveWorkbook.SaveAs OrgPath & "Book1_TwoSheets", xlWorkbookNormal

    Workbooks.Add xlWBATWorksheet
    Range("A1").Value = "シート1枚だけのブック"
    Range("A2").Value = "パスワードを設定"
    ActiveWorkbook.SaveAs FileName:=OrgPath & "Book1_OneSheet", _
        FileFormat:=xlWorkbookNormal, Password:="abc"
End Sub
EOS

macro_name = "Macro1"
Dir.glob("Book1*.xls") {|filename|  File.delete(filename)}
Exlap.new("Book1.xls") do |wb|
  wb.macro_add(macro_str)
  wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
  wb.ss(1).Range("A1").Value = "ワークブックの新規作成と保存のテスト"
  wb.save
end

　変数 OrgPath には Book1.xls のパス名を代入しています。Book1_TwoSheets.xls, Book1_OneSheet.xls の二つをBook1.xlsと同じフォルダに作成するために用いています。

目次に戻る

3.　win32oleのスクリプト

　前述のマクロと同じ処理を行うwin32oleのスクリプトは下のようになります。

　先のマクロ登録用スクリプトでは、マクロを収納する入れ物として Book1.xls を設けましたが、今回はマクロを登録する訳ではないので Book1_TwoSheets.xls, Book1_OneSheet.xls の二つを保存して終了します。Book1.xls は作成しません。

 # encoding: Windows-31J
 require "win32ole"
 
 def fullpath(filename)
     fso = WIN32OLE.new("Scripting.FileSystemObject")
     return fso.GetAbsolutePathName(filename)
 end
 
 module XlConstant
end
include XlConstant

Dir.glob("Book1*.xls") {|filename|  File.delete(filename)}
app = WIN32OLE.new("Excel.Application")
WIN32OLE.const_load(app, XlConstant)
snum = app.SheetsInNewWorkbook
app.SheetsInNewWorkbook = 2  # 2枚のシートからなるブック
wb = app.Workbooks.Add
app.SheetsInNewWorkbook = snum
wb.Worksheets(1).Range("A1").Value = "2枚のシートの1枚目"
wb.Worksheets(2).Range("A1").Value = "2枚のシートの2枚目"
wb.SaveAs(fullpath("Book1_TwoSheets"), XlWorkbookNormal)

wb = app.Workbooks.Add(XlWBATWorksheet)
wb.ActiveSheet.Range("A1").Value = "シート1枚だけのブック"
wb.ActiveSheet.Range("A2").Value = "パスワードを設定"
wb.SaveAs("FileName"=>fullpath("Book1_OneSheet"),
    "FileFormat"=>XlWorkbookNormal, "Password"=>"abc")
app.Quit

　wb = app.Workbooks.Add というのは、Addメソッドが新規作成のワークブックオブジェクトを返すことを利用して、新しいオブジェクトを変数 wb に代入するものです。

　rubyではアクティブなワークブックやワークシートを暗黙の前提とすることができないので、このように逐次　変数にオブジェクトを代入します。

目次に戻る

4.　exlapのスクリプト

　同じ処理を行うexlapのスクリプトを掲げます。

　定義済み定数(XlWorkbookNormalなど)の読み込みが暗黙のうちに行われること以外は、先の win32oleのスクリプトとほぼ同じです。

 # encoding: Windows-31J
 require "./exlap"
 
 Dir.glob("Book1*.xls") {|filename|  File.delete(filename)}
 app = Exlap.new
 snum = app.SheetsInNewWorkbook
 app.SheetsInNewWorkbook = 2  # 2枚のシートからなるブック
 wb = app.Workbooks.Add
 app.SheetsInNewWorkbook = snum
wb.Worksheets(1).Range("A1").Value = "2枚のシートの1枚目"
wb.Worksheets(2).Range("A1").Value = "2枚のシートの2枚目"
wb.SaveAs(Exl::fullpath("Book1_TwoSheets"), XlWorkbookNormal)

wb = app.Workbooks.Add(XlWBATWorksheet)
wb.ActiveSheet.Range("A1").Value = "シート1枚だけのブック"
wb.ActiveSheet.Range("A2").Value = "パスワードを設定"
wb.SaveAs("FileName"=>Exl::fullpath("Book1_OneSheet"),
    "FileFormat"=>XlWorkbookNormal, "Password"=>"abc")
app.quit

　Exl::fullpath(……) は、exlapの中で定義されているExlというモジュールの中の fullpath() を呼び出すものです。与えられたファイル名(文字列)の絶対パスを返します。win32oleのスクリプトの fullpath() と同じです。

目次に戻る

5.　CSVおよびTXTでの保存

　ExcelファイルをExcelのない環境の人に渡す場合、最もポピュラーな形式は CSVでしょうか。SaveAsメソッドの解説でもCSV形式での保存がよく取り上げられています。

　CSVで保存する場合、次の二点に注意する必要があります。

• CSV形式で保存できるのは、ワークブックではなくワークシート１枚だけです。アクティブシートのみ保存されます。
• CSVで保存しても、ワークブックが保存されたとは見なされません。そのため、ワークブックを閉じようとしたり、Excelを終了しようとすると、「ワークブックを保存しますか」という趣旨の確認メッセージ(警告メッセージ)が出ます。

　SaveAsメソッドの２番目のパラメータで xlCSV(整数値 6)を指定すればCSV形式での保存が可能です。

　警告メッセージを出さないようにする例を下に掲げておきます。SaveAs実行の後でワークブックを閉じます。

Sub Macro1()
    Workbooks.Add
    Range("A1").Value = "3枚のシートの1枚目"
    Worksheets(2).Range("A1").Value = "3枚のシートの2枚目"
    ActiveWorkbook.SaveAs ActiveSheet.Name, xlCSV
    ActiveWorkbook.Close False
End Sub

　上のマクロを実行すると、第１ワークシートが Sheet1.csv として書き出されます。

　ActiveWorkbook.Close False によってワークブックを閉じていますが、Closeメソッドにパラメータとして False を渡しているのは「ワークブックを保存するかどうか」の確認メッセージを出さないようにするためです。

　今回のケースでは、Closeすることによって Sheet2 に書き込んだ情報は失われてしまいます。

　それは困るという場合は次のようにして、ワークブック内の全シートを書き出します。

Sub Macro1()
    Workbooks.Add
    Range("A1").Value = "3枚のシートの1枚目"
    Worksheets(2).Range("A1").Value = "3枚のシートの2枚目"
    Worksheets(3).Range("A1").Value = "3枚のシートの3枚目"
    For Each Sobj In Worksheets
        Sobj.Activate
        ActiveWorkbook.SaveAs Sobj.Name, xlCSV
    Next Sobj
    ActiveWorkbook.Close False
End Sub

　上記に関連して、For Each の使い方を説明すべきところですが、別の機会にします。

　全シートをCSVで保存するサンプル(マクロ登録およびwin32oleのスクリプト)を ExcelVBA_ruby.zipに入れてあるので、興味があるようでしたら覗いてみてください。

　CSVでなく、タブ区切りテキストで保存する場合は、xlCSV の代わりに xlCurrentPlatformText(整数値 -4158)を指定します。

　Sheet1.txt のような拡張子 .txt で保存されます。