母校のバンドが予選をトップ通過したと聞いて、ここ数日浮足立っている私です。
VBAでテーブル化と好みのテーブルスタイル設定のマクロ化に取り組んでいます。
前回は、オリジナルのテーブルのスタイルを作成できるようになりました。
【Excel VBA】テーブル変換とスタイル変更 ~標準モジュールでスタイルを新規作成する~ - ゆるおたノート
今回はこれをマクロで保存できるようにしたいと思います。
進捗
挑戦!!と付いているモノが本記事のテーマです。
- Clear!!
選択範囲をテーブル化- Clear!!
指定範囲にテーブルがあると「実行時エラー」が発生するので、エラーが出てからOn Errorで「テーブルの解除」に進んだ方が良いかも。
- Clear!!
- Clear!!
しましまの無い、罫線・見出しだけのテーブルスタイルを作る- Clear!!
テーブルスタイル関連のオブジェクト - Clear!!
TableStyle
オブジェクト - Clear!!
TableStyleElement
オブジェクト /TableStyleElements
コレクション
- Clear!!
- Clear!!
ブックの既定のスタイルに登録する - 挑戦!!保存先をダイアログで指定
- クラスで共通処理を分離
- ユーザーフォームで操作簡略化
- (もし出来れば)色は自由に選べるようにしたい
コード
Module1: ConvertIntoTable
メイン
コメント行を含めて、ブックを保存の2行と後処理の2行だけ増えました。
ひとまずこれで完成です。疲れた…
Public Sub Main() '念のため変数に入れておく Dim myRange As Range: Set myRange = Selection.CurrentRegion Dim myBook As Workbook: Set myBook = ActiveWorkbook Dim mySheet As Worksheet: Set mySheet = myBook.ActiveSheet 'エラー対策 If hasListObjectOnSelection(myRange) = False Then Exit Sub Dim listObj As ListObject Set listObj = convertRangeIntoTable(mySheet, myRange) Set myRange = Nothing Set mySheet = Nothing '▼追加 Call setTableStyle(myBook, listObj) 'ブックを保存 Call selectOverwriteOrNew(myBook) MsgBox "処理が完了しました。" '後処理 Set listObj = Nothing Set myBook = Nothing End Sub
Module3: saveWorkbook
ユーザーに保存方法を確認
また選択肢が増えました…流石にウザいかな。
でも、使う状況によって、上書きの場合と新規作成の場合とありますよね…?
Option Explicit Option Private Module Public Sub selectOverwriteOrNew(ByRef targetBook As Workbook) If MsgBox("このブックを新しいブックとして保存しますか?", vbYesNo) = vbNo Then If MsgBox("上書き保存しますか?", vbYesNo) = vbNo Then Exit Sub End If targetBook.Save Dim currentPath As String currentPath = ThisWorkbook.Path & "\" & ThisWorkbook.name MsgBox ("保存しました。" & vbCrLf _ & currentPath) Exit Sub End If Dim newFilePath As String newFilePath = saveViaDialogBox(targetBook) MsgBox ("保存しました。" & vbCrLf _ & newFilePath) End Sub
新規作成する
今日の本丸。
Application
オブジェクトのGetSaveAsFilename
というメソッドを使って、ユーザーが保存先とファイル名を入力できるようにします。
(途中で長々書いているコメントは、GetSaveAsFilename
メソッドの使い方をメモしたものです。)
Private Function saveViaDialogBox(ByRef targetBook As Workbook) As String Dim newFileName As String Const MACRO_BOOK As String = "Excelマクロブック,*.xlsm" '保存先のフォルダを指定していない場合は、ダイアログには カレントフォルダ が表示される。 '事前にChDirしたり、InitialFileNameをフルパスで指定したりも可。今回は カレントフォルダ のままで。 ' @param {string} InitialFilename (省略可)既定値として表示するファイル名 ' @param {string} FileFilter ファイルの候補(ファイルフィルター文字列)をカンマ区切りで ' @param {num} FilterIndex (省略可)FileFilterの既定のインデックス(1始まり) ' @param {string} Title (省略可)ダイアログボックスのタイトル ' @param {string} ButtonText ※Macintosh専用 ' ' @return {string/boolean} ファイル名(かパス)。「キャンセル」を押すと"False"が返ってくる。 newFileName = Application.GetSaveAsFilename(InitialFileName:=getInitialFileName(targetBook), _ FileFilter:=MACRO_BOOK) targetBook.SaveAs Filename:=newFileName saveViaDialogBox = targetBook.Path & "\" & targetBook.name End Function
これで、(新規作成の場合は)ダイアログボックスから保存先を選択出来るようになりました*1。
既定値を設定
日付でファイル名を管理したいので、ファイル名の先頭に実行日を付与するようにしました。
Private Function getInitialFileName(ByRef targetBook As Workbook) As String Dim fileNameWithDate As String '当日の日付を付与しておく '【例】20190516_テーブルサンプル.xlsm fileNameWithDate = Format(Date, "yyyymmdd") & "_" & targetBook.name getInitialFileName = fileNameWithDate End Function
本日はここまで。
補足(GetSaveAsFilenameメソッドの挙動)
キャンセルボタンを押した場合
ダイアログボックスでキャンセルボタンを押すと、ファイル名がFalse.xlsm
として保存されました。
これは、GetSaveAsFilename
メソッドの戻り値として「何も選択されてない」という意味でFalse
が返ってきたからのようです。
…ということは、途中まで操作したところで間違いに気づいたりして「やっぱり保存は止めよう」となった時*2のために、分岐を作ると良いのかもしれません。
ファイル形式の制限
今回はマクロをExcelのブックに直接書いているので、「マクロ付きブック」の状態です。
このブックを、ダイアログボックスでファイル名を入力して「マクロ無しブック」として保存しようとすると、実行時エラーになりました。
実行時エラー'1004':
この拡張子は、選択したファイル形式には使用できません。[ファイル名]ボックスでファイル拡張子を変更するか、[ファイルの種類]ボックスで別のファイル形式を選択してください。
ブックのタイトルバーにも「保存に失敗」との表示が…
つまり、今回のコードは「マクロ付きブック」で実行しているので、FileFilter
という引数には実質「Excelマクロブック形式」しか選択できない状態のようです。
この点は手動で操作した場合と同じ仕様だと思いますが、処理を足さないと保存形式の選び直しはさせてくれないのですね。めんどくさいな…
FileFilter
の指定
これを踏まえると、FileFilter
の条件を増やす必要があるのは、取引先とかのバージョンに合わせて拡張子を変える時くらいでしょうか?
この場合のサンプルコードも書いてみました。先述の参照先のコードをお借りしました。
Const XLSX_BOOK As String = "Excelファイル,*.xlsx" Const XLS_BOOK As String = "Excel2003以前,*.xls" newFileName = Application.GetSaveAsFilename(InitialFileName:=getInitialFileName(targetBook), _ FileFilter:=XLSX_BOOK & "," & XLS_BOOK)
カンマ区切りをさらにカンマ区切りするのが面白いですね。
ワークシートのCOUNTIFS
関数みたいな。
後記
今回のコード自体はそんなに難しくなかったと思うのですが、引数の使い方がちょっと注意が要りそうだなと思いました。
あと、ファイルの操作ならFileSytemObject
なのかなと予想してたのに、違うみたいです(よく分かってない)。
入門者からの出口はまだまだ遠そうです…
ちなみに…
本日限定ですが、Twitterでアンケートを作ってみました*3。
ご回答いただけたら嬉しいです。
<2020/10/21追記>
その後4名の方にご回答頂きました。ご協力ありがとうございました!
VBA使いの皆様にお聞きします。
— yuricks7 (@yuricks7) 2019年5月16日
画像のAとBはどちらが読みやすいですか?
無知のくせに思い込みしやすい性格なので、出来るだけ多くの方のご意見を聞いて知識をアップデートして行きたいと思います。
このシリーズについて
テーブルの変換とテーブルスタイルの新規作成をマクロ1発で使えるように考えています。主に自分向け。
もし間違いやヘンなところ等ありましたら、コメント欄やTwitter、お問い合わせフォーム等でご指摘いただけたら嬉しいです。
次回
Coming Soon...
連載目次
- 【Excel VBA】テーブル変換とスタイル変更 ~一発で変換とスタイル変更を済ませたい~ - ゆるおたノート
- 【Excel VBA】テーブル変換とスタイル変更 ~処理の流れを整理してみる~ - ゆるおたノート
- 【Excel VBA】テーブル変換とスタイル変更 ~テーブル変換するところまで標準モジュールで書いてみる~ - ゆるおたノート
- 【Excel VBA】テーブル変換とスタイル変更 ~標準モジュールでスタイルを新規作成する~ - ゆるおたノート
- 当記事【Excel VBA】テーブル変換とスタイル変更 ~ブックの保存先を選ぶ~ - ゆるおたノート
注釈
*1:こちらを参考にさせていただきました。 名前を付けて保存ダイアログ(GetSaveAsFilename)|VBA入門
*2:「そこまで考える必要ある?」と思うかもしれませんが、私はよくあるのです…
*3:ツイート2件とも同じこと言ってたり、画像のメッセージボックスが足りてないのは見逃してください…