アドインでzip保存

Word、Excelなど保存したあと、メールに添付したり、ファイルサーバーに保存するときに、zip形式で保存することってありませんか。エクスプローラーの「送る」で、簡単にzipに形式に保存できるので、それほど面倒ではないのですが、編集後、保存と同時にzip保存できるならその方が、場合によってはありますよね。今回は、VSTOで使える.NETを使って紹介したいと思います。

プロジェクト名は、今回は「ExcelZipSaveAddIn」とします。今回はアドインを作成しますよ。VBAでアドイン作ったことある人「シーン」私の周りにはいません。作成したことある人なら、分かるのですがテンプレートを使って作成します。テンプレートって本当に便利なのですが、ネットにある雛形以外に、オリジナルのテンプレートを作成したり使ったことがない人が多いですよね。なじみが、ないからといって、難しいわけでは特にありません。VSTOのアドインは、もしかしたら、VBAより「やさしい」かも知れません。今回は、.VBAは、まったく登場しません。.Net基本を兼ねて紹介になります。本当に簡単なので、参考にしてみてください。

いつものように、新規作成からプロジェクトを選んで、

今回は、Excelのアドインを選んでみました。Wordでも作成方法は、同じですよ。名前は「ExcelZipSaveAddIn」としました。今回の仕様は、保存と同時に同じファイル名で、zipファイルを作成するだけの簡単なものを作成します。

ソースを開くといつものように、Startup() とShutdown（）が作成されます。

Public Class ThisAddIn
    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub
End Class

今回は、ここにイベントを追加します。保存後に処理したいので、WorkbookAfterSaveを追加します。イベント名はVBAとここは同じです。追加方法を紹介します。

Application(ここではExcel）を選択してから

イナズママークから、WorkbookAfterSaveを選択して下さい。イベントの追加は、簡単ですので、コツを掴んで慣れてください。

追加されてコードは、こちら、

Private Sub Application_WorkbookAfterSave(Wb As Microsoft.Office.Interop.Excel.Workbook, Success As Boolean) Handles Application.WorkbookAfterSave

End Sub

ここに、処理を書いていきたいと思います。今回は、ZipArchive クラスを使って作成します。クラスといっても関数と同じなので、VBAで作成されたことのある方なら、難しくありません。

イベントの引数に、Successとあります。これは、失敗か成功かってことなので、保存が成功した場合のみ作成したいので、

If Success = True Then

End If

上記を追加しました。Wbは、保存したファイルの情報の中身があるので、こちらの情報を使ってファイル名とファイルの保存先のPATH名を取得したいと思います。

Wb.Name　‘ファイル名  Wb.Path ‘PATH名

PATH名とファイル名が分かったので、zip名は、決まりましたね。

Dim zipPath As String = Wb.Path & “\” & Path.GetFileNameWithoutExtension(filename) & “.zip”

Path.GetFileNameWithoutExtensionは、拡張子を除いたファイル名が取得できます。右からドッドのところまで探してなんてしなくていいんですよ。

            Using archive As ZipArchive = ZipFile.Open(zipPath, ZipArchiveMode.Update)
                archive.CreateEntryFromFile(filename, filename)
            End Using

上記では何をやっているかと言うと、先ほどのzipを作成して、ファイルをそこに追加するイメージです。Using　End　Usingとありますが、こちらは、ファイルの後処理を上手にやってくれます。ここでは、とりあえずコードの通り、作成してみてください。

実は、このままだと、必ず失敗します。なぜかと言うと、Excelが保存したファイルを使ってますよって叱られます。そこで、保存したファイルを一時フォルダーにコピーして、そのを使って、処理するように作成します。で、作成したコードは、こちら

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

    Private Sub Application_WorkbookAfterSave(Wb As Microsoft.Office.Interop.Excel.Workbook, Success As Boolean) Handles Application.WorkbookAfterSave
        If Success = True Then
            Dim filename As String = Wb.Name    'ファイル名
            Dim zipPath As String = Wb.Path & "\" & Path.GetFileNameWithoutExtension(filename) & ".zip"     '保存したいzip名

            Dim tempPath As String = Path.GetTempPath()                     '一時フォルダ場所
            System.IO.File.Copy(Wb.Name, tempPath & Wb.Name, True)          '一時フォルダに上書きコピー

            'ZIP作成
            Using archive As ZipArchive = ZipFile.Open(zipPath, ZipArchiveMode.Update)
                archive.CreateEntryFromFile(tempPath & filename, filename)
            End Using

            System.IO.File.Delete(tempPath & filename)                      '一時フォルダからファイルの削除
        End If
    End Sub
End Class

実行すると、Excelが立ち上がって来ます。編集後、保存ボタンを押すと、同じフォルダーにzipファイルも作成されます。アドインなので、どのファイルに対しても、実行されます。作成されたものは、COMアドインとして実行します。使い続けるには、問題ないのですが、削除した場合、Excel側で簡単に出来ます。知らない人もいるかも知れないので、紹介したいと思います。Excelのオプションからアドインを選択する

管理（A)からCOMアドインを選んで設定ボタンを押して

先ほど作成した、ExcelZipSaveAddInのチェックボック又は、削除ボタンで解除できます。

話は変わって、Visual Studio 2013 Previewが出ています。興味があるかたは、評価を兼ねてダウンロードしてみてください。近々横浜でExcelユーザーのAccessデーターシート活用の講座を開く予定でいます。内容は、AccessをAccessらしく使わない講座になります。なにそれって感じですが、Excel共有でこまっているとか、フィルター処理がどうも重い、データベースを使うまでは、などExcelでこまっていることをAccess2013を使って紹介したいと思います。併せて、VBAの入門講座も予定しています。現場で知りえた情報満載です。リクエストも引き続き募集中です。

宣伝で恐縮です。夏にも負けない、プロフェッショナル向けオリジナルパソコン販売中です。VisualStudioも是非買ってください。

テーブルを使ったレコード追加（VSTO版）

ほぼ２ヶ月ぶりの更新になりました。更新を待ち望んでいた方がいたら、すいません。約束はできませんが、今後は頻繁に更新したいと思います。

テーブルを扱ったVBAは、あまり取り上げられていないので、あえて取り上げてみました。参考になったでしょうか。どちらかというとあまり取り上げられていない内容を今後も取り上げていきたいと思っています。

前回と同じく簡単なインシデント、バグ管理のテーブルを使いますが、入力フォームは操作ウィンドウで作成したいと思います。操作ウィンドウを使ってのメリットは、何と言っても豊富なコントロールが使えることでしょうか。では、作成手順を紹介します。

プロジェクトは、ブックでもテンプレートでもかまないと思います。まずは、Sheet1に前回と同じテーブルを作成します。

操作ウィンドウとをプロジェクトに追加したら、ツールボックスからコントロールを選んで、次のような配置してみてください。

配置したコントロールはこちら

日付入力とは、MonthCalendar　がとっても便利なので、ここでも使ってみました。障害状況のTextBoxは、複数行入力できるようにプロパティ項目のMultilineをTrueに設定しましょう。これで、改行キーを使って入力することができます。Excelのセルでは改行のたびに数式バーでAlt + 改行キーを使って入力するので、何十行も入力するとなると本当にイライラしますよね、それに文字数が多いと表示もけして見やすいとはいえないから、そういったときは、操作ウィンドウを使ってみることをお勧めします。

デザインが、決まったところで、間単に今回の仕様を説明します。管理番号は、自動的に連番が振られます。報告日時にカーソルまたは、マウスクリックするとカレンダーコントロールが表示され日付の選択が可能になります。報告日時からフォーカスが外れると、カレンダーコントロールは、非表示になります。追加登録ボタンにより、TextBoxの各項目を、Sheet1のテーブルに追加していきます。仕様は前回と同じで、カレンダーコントロールが、追加されただけにしました。なので、VBAのコードをそのまま、使えるようにしてあります。早速、コードの紹介から。

Public Class ThisWorkbook
    Private pane As ActionsPaneControl1

    Private Sub ThisWorkbook_Startup() Handles Me.Startup
        pane = New ActionsPaneControl1

        pane.GetSeq()
        Me.ActionsPane.Controls.Add(pane)
    End Sub

    Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown
        pane.Dispose()
    End Sub
End Class

操作ウィンドウの登録と表示は、いつもと同じです。GetSeqは、管理番号を取得する処理を追加しました。ActionsPaneControl1のコードを順番に紹介します。

'管理番号を取得
    Public Sub GetSeq()
        Dim rowCnt As Long = Globals.Sheet1.Range("テーブル1").ListObject.ListRows.Count
        If rowCnt < 1 Then
            rowCnt = rowCnt + 1
        End If
        Me.TextBox1.Text = "Q" + rowCnt.ToString("D4")
    End Sub

ここでのポイントは,VBAのFormat関数ではなく、.ToString(“D4”）と書かれている点です。実は、数値の書式フォーマットは、.Netではとても便利になっています。.ToStringについての、数値だけでなく、Objectも大抵扱えます。”D4” とは　Format　の　“0000” と同じ意味になります。

'報告日時フォーカス移動時の処理
    Private Sub TextBox4_Enter(sender As Object, e As EventArgs) Handles TextBox4.Enter
        'カレンダー表示
        Me.MonthCalendar1.Show()
    End Sub

報告日時にフォーカスが移動したり、マウスでクリックしたら、カレンダーを表示する処理を追加しました。ポイントは、イベントの追加方法ですね。

　まずは、デザインモードからTextBox4を選択して、稲妻マークをがイベントになるので、それを選択してください。たくさんのイベントが表示されるので、ここでは、Enterの隣の空白をマウスでクリックすると、TextBox4_Enterが、ソースに追加されます。

今回は、フォーカスから外れたら、カレンダーを消したいので、Leaveもイベントから追加します。

   '報告日時フォーカスから外れる時の処理
    Private Sub TextBox4_Leave(sender As Object, e As EventArgs) Handles TextBox4.Leave
        'カレンダーの非表示
        Me.MonthCalendar1.Visible = False
    End Sub

コードも Me.MonthCalendar1.Visible = False と、とても簡単に書くことができます。VBAのフォームになれている方なら、すぐ使いこなすことができると思います。

表示されたカレンダーの処理に移りま

i

'カレンダーコントロールを日付を選択した時の処理
    Private Sub MonthCalendar1_DateSelected(sender As Object, e As DateRangeEventArgs) Handles MonthCalendar1.DateSelected
        Me.TextBox4.Text = e.Start.Year.ToString() + "/" + e.Start.Month.ToString() + "/" + e.Start.Day.ToString()
        'カレンダーの非表示
        Me.MonthCalendar1.Visible = False
    End Sub

またまた、.ToString（）が出てきました。e の引数から情報を取得しているのは、選択した時点の情報を取得したいからです。イベントには概ね　引数の　e が存在します。

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim tbl As Excel.ListObject
        Dim insertRow As Long = 0

        tbl = Globals.Sheet1.Range("テーブル1").ListObject()
        insertRow = tbl.Range.Rows.Count

        tbl.Range(insertRow, 1) = Me.TextBox1.Text()
        tbl.Range(insertRow, 2) = Me.TextBox2.Text()
        tbl.Range(insertRow, 3) = Me.TextBox3.Text()
        tbl.Range(insertRow, 4) = Me.TextBox4.Text()

        tbl.ListRows.Add()

        Dim tbox As Control

        For Each tbox In Me.Controls
            If tbox.GetType Is GetType(TextBox) Then
                tbox.Text = ""
            End If
        Next

        Me.GetSeq()
    End Sub

最後は、追加ボタンの処理です。VBAとほとんど、同じではありませんか。ポイントは、一箇所だけになります。 Dim tbl As Excel.ListObject　です。Excel.ListObject と必ず、Excelをつけることです。.Netの中にListObjectが存在するので、VBAのコードのように抜かしてしまうと.Netの型とExcelのListObjctと区別がつかない場合があり、エラーになるケースがあるからです。

どうですか、VBAのコードと、同じではないですか。とても移植がらくて、便利なカレンダーコントロールなども使えてとても便利です。こちらの内容がわからない人は、最初のころのページも見てみてください。VSTOの良さがわかって頂けたでしょうか。次回も、VSTOの良さを紹介したいと思います。

テーブルを使ったレコード追加（VBA版）

企業レポートの報告では、エクセルでインシデント管理されている会社ってたくさんあるようですね。今後はWeb管理だの、いろいろ言われていますが、まだまだ、皆さんの会社では、今後も（たぶん）Excelを使っていくよって人がいるのでは、ないでしょうか。このあたりを今回はVBAとVSTOを交えて、紹介したいと思います。

今回は、Excelのテーブルを使った例を紹介を紹介したいと思います。項目は、一般的なインシデントの項目をテーブル化するだけにしました。項目を入力したら、先頭行を範囲指定して、[挿入メニュー]の[テーブル]を選択指定下さい。以下のような[テーブルの作成]ダイアログが表示されます。最後にチェックボックスは、必ずチェックしてください。

次のように表示されます。

１行目が入力出来る状態でテーブルが作成されます。そのまま入力セルに移動して、入力もできますが、今回は同じシートに入力項目を追加したいと思います。

こんな、感じでどうでしょうか。　さっそく、ボタンイベントを追加します。ボタンをダブルクックで、追加されます。

Sub ボタン_Click()

End Sub

テーブルのアクセス方法が分からない。VBAのコードをどうかけば、いいの。そんな方は、テーブルのオブジェクトは、どのグループか調べましょう。　答えは、”ListObject”　になります。分かったところで、入力した内容を、レコードに書き込むには、今までのRangeが使えます。テーブルを使わない場合は、Sheetからみているので、cells(1,1)　は　[A1]セルを指していますが、テーブルでは、cellsの代わりにRange(1, 1).　は、テーブルの項目の左の項目の入力データの１行目を指します。テーブルを基準に相対的にセルを見るので、扱いがとても楽になります。列や行を追加や、Ａ、Ｂ、Ｃなど気にしなくてすみます。

Sub ボタン_Click()
    Dim tbl As ListObject
    Dim insertRow As Long
    
    Set tbl = Sheet1.Range("テーブル1").ListObject
    insertRow = tbl.Range.Rows.Count

    tbl.Range(insertRow, 1) = Range("M2").Value
    tbl.Range(insertRow, 2) = Range("M3").Value
    tbl.Range(insertRow, 3) = Range("M4").Value
    tbl.Range(insertRow, 4) = Range("M5").Value

    tbl.ListRows.Add
    Set tbl = Nothing
    
    Range("M2").Value = "Q" & Format(Val(Right(Range("M2").Value, 4)) + 1, "0000")
    Range("M3:M5").Clear
   
End Sub

項目増えることもあるので、　tbl.Range(insertRow, tbl.ListColumns(“管理番号”).Index) 　としてもいいと思います。これなら、項目名を使って、アクセスできます。ListColumns以外にも、ListRows.Countなど便利なメッソドやプロパティがあるので、使ったことがない人は確認してみては、どうでしょうか。今回は、テーブルを使ったVBAが、はじえめての方に、簡単ですが、VBAのコードを紹介しました。次回は前回と同じく、操作ウィンドウ（作業ウィンドウ）を使って、テーブルにレコード追加したいと思います。VBAtとVB.Netの違いは、また改めて紹介したいと思います。

インシデント管理で、一番面倒なことは、Excelでドキュメント項目を入力するときの、改行が【[数式バー】で、Alt +　Enter と面倒なことと、数式バーが広げたり、縮めたり面倒な点でしょうか。そこで、ちょっとしたアドインをVSTOを使って作成しました。欲しい方は、連絡を頂ければ、いくらかでお分けしたいと思います。公開は、近々できたらしますので、楽しみにしてください。講習会参加者か、Office2013または、VisualStudio購入者には、便利なアドインとして、いつものように提供させてもらいます。　次回は、VBAからVSTOの移植になります。お楽しみに！

セルの内容を操作ウィンドウに表示する

[Sheet1]A1セルの内容を操作ウィンドウのLabelコントロールに表示させる

今回も、とても簡単ですよ。テンプレートはExcelブックを使います。

名前は、ExcelWorkbookPaneSample にしました。保存先や名前は適当につけてください。

作成するとソリューションエクスプローラーにが次のように表示されます。

Sheet1のデザインを使って、A1に「Hello World!」を設定してください。

操作ウィンドウコントロールを追加します。ここでは、ExcelWorkbookPaneSampleを選択して右クリックで追加を選択してください。

名前は、サンプルなのでそのままにActionPaneControl1とします。注目する点は、ActionPaneControlってのがコントロールの名前であることです。

追加を選択すると、ソリューションエクスプローラーが次のように変化します。

VBAには、操作コントロールが追加されました。って言ってもあくまでもコントロールの定義が追加されただけで、実行しても表示されません。デザインウィンドウで、Labelコントロールを追加します。

　操作ウィンドウにドラッグすると配置できます。このあたりは、VBAのユーザーフォームの作成と同じです。

ここからが、コードを追加していきます。今回も数行程度のコードを追加するだけです。

ThisWorkbook.vbに次のコードを追加してください。

Public Class ThisWorkbook
    Public pane As ActionsPaneControl1

    Private Sub ThisWorkbook_Startup() Handles Me.Startup
        pane = New ActionsPaneControl1

        Me.ActionsPane.Controls.Add(pane)
    End Sub

    Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown
        pane.Dispose()
    End Sub

 

解説

Public pane As
ActionsPaneControl1ですが、Publicにしているのは、Sheet1からも参照できるようにです。

型宣言しただけのpaneにちゃんとNew をして使えるようにしてください。

まだ、これだけでは、表示されません。Workbookに操作ウィンドウを追加する必要があります。

Me.ActionsPane.Controls.Add(pane)

ThisWorkBookのActionsPane.Controls のAddメソッドを使って追加してください。複数のデザインを用意して入れかえたり、複数の操作ウィンドウを追加したりもできます。

Disposeはコントロールリソースの解放です。

Sheet1.vbに次のコードを追加してください。

Public Class Sheet1
    
    Private Sub Sheet1_Startup() Handles Me.Startup
        Globals.ThisWorkbook.pane.Label1.Text = Me.Range("A1").Text
    End Sub

解説

Globals.This…のGlobalsでグローバルアクセスができます。プロジェクト内なら他の定義の参照が簡単にできます。

ここでは、Globalsの使い方だけマスターできればいいと思います。

結果

セルA1の値が、操作ウィンドウに表示されました。次週は、操作ウィンドウをユーザーフォームにみたてます。