NOTO

xlsxのデータをcronに載せたくていろいろしたあれこれ

 Date:2015-12-16 12:47:48 +0900
 Categories: ETC

概要

この記事では、

環境

PC環境

サーバ環境

状況

もともとはSSL証明書の有効期限の日付管理等をエクセルで行っていました。複数個ある証明書の有効期限を毎日エクセルを開いて確認するというのはなかなか酷な話なので、windows oleを使用したスクリプトを書いていました。

スクリプト自体は

このスクリプトはwindwos上のものなので、スタートアップに仕掛けて起動しておいてLinux(samba)の上にある管理エクセルを読ませて実行していました。そのキックだけがenter一発だけとはいえ手動でした。パソコンを起動しないと実行できないという。

解決策

「linuxのrubyでoleが面倒ならcsv出しちゃえば?」という先輩の一言がきっかけでややこしいんですが、該当のエクセルから使いたいデータをCSVにする方法を思いつきました。VBAではまりながらなんとか実行した記録です。

VBAでCSVを吐く

調べたところ、ThisWorkbookの中でエクセルを保存時にフックできるものがあることがわかりました。それを活用して以下のVBAを書きました。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Call makeNewGeneralCSV
End Sub

' CSVを保存するために別bookを作成
Sub makeNewGeneralCSV()
' 変数の宣言
Dim newBook As Workbook
Dim masterBook As Workbook
Dim filename1 As String
Dim dirPath As String
Dim format1 As String

filename1 = "filename.csv"
format1 = "xlCSV"

' 新しいBOOKの作成 (間に確認)
Set masterBook = Workbooks("managefile.xlsm")
dirPath = masterBook.Path & ""

' general csvが存在するか確認
If Dir(dirPath & "filename.csv") = "" Then
MsgBox ("ファイルは保存されましたが、通知管理用CSVの保存処理に失敗しました。同ディレクトリに「filename.csv」があるか確認してください")
Exit Sub
End If
Set newBook = Workbooks.Open(dirPath & "filename.csv")

' アラートを消す
Application.DisplayAlerts = False

' シートにデータコピー とその他のシート削除
newBook.Sheets("datasheet").name = "datasheet_bak"
masterBook.Sheets("DATASHEET").Copy After:=newBook.Worksheets("datasheet_bak")
newBook.Sheets("datasheet_bak").Delete

' newBookの保存
newBook.save

' newBookを閉じる
newBook.Close

' アラートを戻す
Application.DisplayAlerts = True
MsgBox ("通知用CSVを最新にしました")
End Sub

なぜかxlCSVで吐き出すのにえらく苦労しました。
内容としては、それ用の別のブックを用意してCSV用のデータをコピーしてCSV保存しているというぐらいです。
ポイントはやはり「Private Sub Workbook_AfterSave(ByVal Success As Boolean)」の部分で、
こいつの中に書いておくことで、保存後に挟む処理をかくことができます。

CSVを読み込んでrubyスクリプトでメール通知

こっちもなんてことないんですが、やったことは
Tweet