デスクトップに別名で保存する

すっかり間が開いてしまったので、とりあえず記事アップ。
ＶＢＡでデスクトップにファイルを保存するときのやり方。
今回のポイントはデスクトップ、とひとことで言っても使用するパソコンやログインユーザによってそのフルパスは異なるので、そのパスを取得すること＆別名保存のポイント。

*****************************

Sub デスクトップに名前をつけて保存()

Dim hullpath As String
Dim filename As String

'保存するファイル名を指定。特定のセルの値やらユーザフォームで入力した値でもＯＫ。
filename = "任意の名前と拡張子" '拡張子はバージョンにあわせて指定。

'デスクトップのパスを取得してファイル名と合体。同様にマイドキュメントなども取得可能。
hullpath = CreateObject("Wscript.Shell").Specialfolders("Desktop") & "\" & filename

'ファイル名を指定し、保存。ver指定するときは「, FileFormat:=（エクセルのバージョンを表す文字列）」
ActiveWorkbook.SaveAs filename:=hullpath

'他の人が使ったときのために保存完了のメッセージを表示する。
MsgBox "現在のファイルを" & filename & "という名前でデスクトップに保存しました。"

End Sub

ユーザーフォームいろいろ

これは本当におぼえがき。
VBAのユーザーフォームについて。
個人的には何千行もあるデータを飛び飛びに更新しないといけないときなどに入力補助で使ったり、簡単なシステム作るときには入力フォームとして使ったりするんだがいつも使うごとに忘れるので。

-----------------------

◆ComboBox、ListBox

• リストセット
　（Box名）.AddItem （追加するデータ） エクセルの別の場所にリスト表作って、そこを参照するとメンテがラク。 追加するデータに変数かましてFor～Nextでするとラク。 ちなみに特定列の最後の行番号は以下で取得。 　Cells(Rows.Count, （特定列No）).End(xlUp).Row

• 選択した値
　（Box名）.Text ComboBoxやListBoxで選択した値。これを変数に入れたりセルに入力したり。 もちろん初期値として値を入れて表示することも。

◆OptionButton,CheckBox

• 値
　（OptionButtonかCheckBox名）.Value 選択されていたらTrue、選択されていなければFalse。

• テキスト表示
　（OptionButtonかCheckBox名）.Caption ボタンのあとに表示する文字。 ちなみにOptionButtonはフレームに入れて使う。

◆TextBox

• 値
　（TextBox名）.Text そのまんまだな。

◆Label

• 表示する内容
　（Label名）.Caption ここはTextではないんだな（よく間違う）。 しかもLavelと間違うな。

◆CommandButton

• 特定の動作のほかにユーザーフォームを閉じるボタンをつける
　Unload Me これでユーザーフォームを閉じることができる。

2007あれこれ

うあなんかひさびさねw
暇人になったので、なまらないように投下。てゆーかリクエスト溜めてる放置記録更新中の本拠地のほうを更新しろやとかいう意見も現在受け付けておりません。ごめん。

-----------------------

んでもって本題のエクセルなんだが、2003→2007でえらいこと見た目が変わったんだが、機能自体は多少追加（削除された機能もあり）＆VBAは結構変わってた。そのことについていくつかメモメモしておく。


◆セキュリティ厳しすぎるよ・・
デフォの設定ではマクロは全ブロックされる。
というかVBAも設定いじらないと起動もできない・・・
極端に走りすぎだよママン・・

◆PDF保存
Microsoftの配布しているアドインを入れたらダイレクトにPDF保存できるようになった。これは結構便利だ。

◆アドインマクロをメニューにぶっこむ
以前メニューをカスタマイズするという記事を書いたと思うが、あれはアドインのマクロをアドイン読み込み時にマクロでメニューに追加する、というものであった。
なぜそんな面倒なことをしていたかというと、アドインのマクロはツールボタンに追加できなかったからだ。
2007は、アドインを保存したあとにクイックアクセスツールバーに追加できるのでそちらを利用する。

◆小数点入力（機能追加）
セルへの数字入力時に、特定の位置に自動で小数点を入れてくれる。
データ入力時にものすごい便利だ（今までは整数で入れて10で割ったりしてた）。
で、その設定のオンオフが地味に面倒なのでアドインに以下のマクロを記述してクイックアクセスツールバーに追加する。

Sub 小数点入力１オン()

　Application.FixedDecimal = True 'ここがオン
　Application.FixedDecimalPlaces = 1 'ここが小数の桁数。必要なら変える

End Sub
Sub 小数点入力オフ()

　Application.FixedDecimal = False 'ここがオフ

End Sub

'そのほか標準モジュールに一緒に貼り付けたら便利なもの（以前の記事に既出含む）

Sub 値貼り付け()

　Selection.PasteSpecial Paste :=xlPasteValues

End sub
Sub 右入力()

　'既出じゃない気がする。エンターキー押下後のカーソル移動設定変更（右）
　Application.MoveAfterReturnDirection = xlToright

End sub
Sub 下入力()

　'既出じゃない気がする。エンターキー押下後のカーソル移動設定変更（下）
　Application.MoveAfterReturnDirection = xlDown

End sub

-----------------------

他のアプリケーションで作ったファイルを開くための記述とか特殊パス（デスクトップとか）を取得するための記述も変わっていたような気がするが思い出せない・・・
以後、思い出し次第追加する。

仮置き

とりあえず会社で明日使う用にアップ。
エクセルで差込ファイルを作成してワードファイルを開くのに、エクセルひらきっぱだと差込失敗するのでその対応。
ああ、ワードファイル開いてればいったん閉じる操作もしないとだめか。そこまではとりあえず無視だ。
エクセルファイルのシートを同ディレクトリに特定ファイル名で保存して閉じる。
で、ワードの特定ファイルを開く。
ひらがなの部分を実際のファイル名にする。
そのうちちゃんと解説書くけど今日はここまで。
2003で動作確認、2007で動くのか不明。

Sub hozon()
Dim exfile As String
Dim exname As String
Dim wdfile As String
Dim wdname As String

exname = "エクセルファイル名拡張子付"
exfile = ActiveWorkbook.Path & exname

Sheet1.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=exfile
ActiveWorkbook.Close

wdfile = "ワードファイル名拡張子付"
wdname = ActiveWorkbook.Path & wdfile

Dim wdobj As Object
Dim wdbook As Object

Set wdobj = CreateObject("Word.Application")
wdobj.Visible = True

Set wdbook = wdobj.documents.Open(wdname)

End Sub

でもってワード側のマクロ。

現在のレコード出力

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With

レコード別ファイルで全出力

ActiveWindow.Panes(1).Activate
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

ファイル名は
"支出伺"　＆　"日付"　＆　"任意の文字列".doc
とする。
日付取得のためにシートのどこかにこっそり起案日を差し込んどく。
これを変数に取り込んで、「エクセルファイルと同じフォルダに以下のファイル名で保存されます」のメッセージとともにユーザーフォームに表示。
任意の文字列をテキストボックスで入力できるようにし、あわせてファイル名の変数として取得。

＆ファイル名をつけて保存
ActiveDocument.SaveAs FileName:=　ファイル名の変数

＜追記＞
2007でフィルタリングつけて差込印刷したのでいろいろあって使えなかった・・・俺の苦労は・・・

セルの更新日（時）を記録する

この間このカテゴリが役立った。
そのとき思いついた、セルの更新日（時）を別のセル（隣の列、など）に記載するテクニックをアップしておく。
たとえばデータ入力で、同じ行でも更新にタイムラグのある場合に有効。何しろ確認のとき、更新日の記載された列にフィルタをかければ、その日に更新されたセルのみを抽出できるので。

VBAを起動させ、対象シートに以下Privateからend subまでをコピーし、必要な部分は入力しなおす

---------------------

Private Sub Worksheet_Change(ByVal Target As Range)

'特定列（ここでは仮にＡ列）のセルの内容変更があった場合に、隣の列に更新日時を記載する

Dim crow As Integer　 '内容変更セルの列番号を格納する変数
Dim ccolumn As Integer　 '内容変更セルの行番号を格納する変数

'変更したセルではなく、変更を確定したときのセルがActiveCellとして認識されるので、カーソルの移動方向にあわせて変更セルを特定する
　Select Case Application.MoveAfterReturnDirection

　　Case xlToRight　'右移動の場合
　　　crow = ActiveCell.Row
　　　ccolumn = ActiveCell.Column - 1

　　Case xlDown　'下移動の場合
　　　crow = ActiveCell.Row - 1
　　　ccolumn = ActiveCell.Column

　　Case Else　'それ以外の場合→たぶん上・左は日本人は使わないだろうので対象外とする
　　　MsgBox "リターンキー押下時のカーソルの移動方向は、" & Chr(13) & "右か下のみにしてください", vbOKOnly + vbExclamation
　　　Exit Sub

　End Select

　'ここから、時刻と日付を入れる処理

　If crow > 1 And ccolumn = 1 Then　'crow > 1 は、最初の１行（おそらく項目名となる）を無視するため
　ccolumn = 1 は、変更を記録する対象列としてＡ列を指定するため。列番号はコレ以降、B=2、C=3と続くので、判定したい列に応じた必要な数字を入れる

　　Cells(crow, ccolumn + 1) = Date & " " & Time '更新日を入れるセルに日時を代入
　例として対象列の1列右隣を指定。必要に応じて「+1」の部分の数字を変える

　End If

End Sub

---------------------

日時「Date & " " & Time」の部分は、日付だけなら「Date」のみ、時刻だけなら「Time」だけでよい。

自分仕様のメニューをつくる

いろいろと使いたい機能はあるが、ショートカットキーをそんなに覚えちゃいられない。
とりあえず、忘れないように割り当てたショートカットキーはどこかで簡単に見れるようにしておこう。
メニューバーに自分仕様のメニューを追加しておくと見やすいと思われるのでその方法↓

-----------------------

メニューバーのカスタマイズはエクセル自体の基本機能で可能であるが、アドインに存在するマクロを登録することが2003ではできないので、以下の方法を使う。
ちなみに2007ではアドインのマクロを普通にボタンで追加できるので、こんなまどろっこしいことをしなくてもＯＫ。

１）VBAを起動して、作成済みのアドインブックの標準モジュールに動かしたいマクロを記述する
　　ここでは例として、登録したショートカットの一覧を表示するマクロ

　　Sub about()

　　 MsgBox "●追加ショートカット一覧" & Chr(13) & Chr(13) & "　 ＞値のみ貼り付け・・・Ctrl + Shift + v", _
　　　　vbOKOnly + vbInformation, "カスタマイズメニューについて"

　　End Sub

　　＜解説＞
　　この「about」という名前のマクロは以下のメッセージを表示するものである
　　
　　「_（アンダーバー）」はプログラム内で改行するためのもの。改行しないなら不要

２）作成済みのアドインブックの、前回作成した「Private Sub Workbook_AddinInstall()」のところに、メニューを表示するマクロを追加する
　　たとえばショートカットを割り当てるマクロとあわせると、

　　Private Sub Workbook_AddinInstall()

　　　'新たなメニューを追加する
　　　Set Menu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)

　　　'追加するメニューの名称指定
　　　Menu.Caption = "カスタマイズ"

　　　'追加した項目にサブメニューも追加
　　　Set Submenu1 = Menu.Controls.Add

　　　'サブメニューの名称とクリック時の動作指定
　　　Submenu1.Caption = "カスタマイズ一覧"
　　　Submenu1.OnAction = "about"

　　　'で、これが前回の記事で記述したショートカットキー割り当て
　　　Application.MacroOptions Macro:="Datapaste", ShortcutKey:="V"

　　End Sub

３）アドインブックの読み込みをやめたときに備えて以下のマクロも記述する

　　Private Sub Workbook_AddinUninstall()

　　　Application.CommandBars("Worksheet Menu Bar").Controls("カスタマイズ").Delete

　　End Sub
　　＜解説＞
　　これをしないと、アドインブックの読み込みをやめても追加したメニューが削除されず、
　　アドインブックを読み込むたびに同じメニューが追加されてしまう

４）アドインブックを読み込む（前回参照）
　　すでにアドインとして追加済みのブックに上記マクロを記述したなら、アドインを再読み込みする
　　下記画面（前回記事参照）でいったん該当アドインブックのチェックを外して「ＯＫ」ボタンで閉じ、
　　再び下記画面を開いてチェックを入れて「ＯＫ」ボタンを押す
　　

すると以下のようにメニューが追加され、サブメニューをクリックすればメッセージが表示される。


-----------------------

このような一覧表示だけでなく、ショートカットに登録するほどではないほかのマクロも、同様にメニューから動かせるようになる。

よく使う動作をショートカットキーに登録する

たとえば。
離れたセルに値だけを貼り付けたいという動作のとき、右クリックして形式を選択して貼り付けダイアログを開き、「値」のトグルボタンを選択してＯＫを押す。
というのが激しく面倒だと感じたことはないであろうか。
私はめんどくさい。
というわけで、こんなしょっちゅう使うようなものはショートカットキー一発で動作するようにしておくと便利である。方法は以下のとおりマクロ使用。
※今まで書き忘れていたが当カテゴリ記事はエクセル2003むけである。他のバージョンだと特にマクロは動かない可能性大。

---------------------
１）VBAを起動して標準モジュールを追加し、動かしたいマクロを記述する
　　例は、値のみ貼り付ける動作のためのマクロ

　　Sub Datapaste()
　　'「Datepaste」の名前は任意。好きな名前をつけられる。

　　　Selection.PasteSpecial Paste:=xlPasteValues

　　End Sub

２）ThisWorkbookに以下のマクロを記述する
　　例は、１）で作成した「Datapaste」というマクロにショートカットキーとしてctrl+shift+vを割り当てるためのもの

　　Private Sub Workbook_AddinInstall()

　　　Application.MacroOptions Macro:="Datapaste", ShortcutKey:="V"

　　End Sub

３）ファイルをアドインファイルとして保存する
（ア）VBAを閉じてエクセル画面に戻り、メニューのファイルから「名前をつけて保存」をクリック


（イ）「Microsoft Office Excel アドイン」形式を選び、保存する
　　　このとき、保存した名前を覚えておく


（ウ）保存したファイルをアドインとして読み込む
　　　まずメニューのツールからアドインをクリックし、


　　　さきほど保存した名前にチェックを入れ、ダイアログを閉じる


--------------------

これで、エクセルを開けばつねにctrl+shift+vで値のみ貼り付けができる。
ただし、上記の場合はコピーモード中でないとエラーが起こるので注意。
１）の内容を変えれば、もちろん他の動作も可能。

ピボットで集計する（複数回答バージョン）

ようやくご本尊登場ーーー。
意味がわからぬ方は準備の記事へドゾー。
最初からやると長くなるので、ピボットの画面を出すところまではこちらと一緒なので省略。

------------------
１）どのような項目を集計するか指定する
（ア）必要な項目を必要な場所へドラッグする
　　　例（商品のイメージを年代別に集計）では、
　　　・行のフィールド：（なし）
　　　・列のフィールド：年代
　　　・データアイテムのフィールド：選択肢の答えの数字表示のほうすべて


（イ）集計値を正しく設定する
　　　デフォルトは「データの個数」であるがなぜか計算結果空白である部分もカウントされるので、
　　　フィールドの設定（こちらの記事４）の（イ）参照）を「数値の個数」もしくは「合計」に変更する
　　　ちなみに行ごとにすべてやらないとダメ

２）見た目を整える
　　ここはこちらとまったく一緒なので省略

３）行見出しを見やすくする
　　ピボットのままでは「データの個数」などといらぬ文字がついている
　　しかし直接修正はできないので、新しく行見出しを作る
　　列を挿入して、新たな文字列を入力するが、手入力は面倒なので、数式処理する
　　例は、上記表の一番左に１列挿入し、データ１行目の見出しを作成する数式
　　=MID(B5,10,20)
　　＜解説＞
　　B5に表示されている文字から文字を取り出す
　　左から10番目の文字から取り出しを開始し、そこから20文字取り出して終わる

　　取り出す文字数は、実際に必要な文字数より多ければそこで切り捨てられる（空白が追加されるわけではない）ので、
　　多めにしておけばＯＫ

　　で、これを他の行にもコピーする
　　もとの列はとりあえずいらないので非表示にする
　　最後に、新しい行見出しに枠線を設定して完了

ピボットで集計する（複数回答バージョン）、の準備

前の記事、ピボットで集計するは、複数回答の場合は適用できない。複数回答の場合は・・・・

------------------------

えっと、申し訳ない！
日本語表示した複数回答設問がうまく集計できないことが判明ーー。しかも前どうやっていたかもう忘れたww
＜追記＞思い出したよ数字表示させたあと文字列表示してたんだよww
で、とりあえず集計のためにまず以下N～R列のような数値表示の列を追加する。泥縄ってこういうことを言うのだな。



１）追加した列にラベル入力のための数式入力
　　なぜセルのコピーでないかというと、全く同じ内容の列ラベルがあるとピボットが作成できないからで、
　　アタマに同じ文字列を追加するのに最も簡単なのが数式処理だからである
　　例はN列にI列のためのラベルを表示する数式

　　="_" & I10

　　＜解説＞
　　I10のセルの先頭に文字列「_」をくっつけた文字列を表示するための数式
　　これを必要列数コピーする

２）数値を表示するための数式入力
　　例はセルN11にI11のデータの有無を表示させるもの

　　=IF(I11="","",1)

　　＜解説＞
　　対応するセルにデータがあれば「1」と表示する数式

------------------------

順番的には「1」表示してその後の列に文字列表示するほうがスマートな気がするが。＜追記＞だから前そうしてたんじゃん俺ww忘れ去ってたけどwww
泥縄だもの。。。とほほ。。。

ピボットで集計する

集計といえばピボットテーブルである。
昔ピボットの使い方を知らなかった頃はsubtotal関数やcountif関数などを使って苦労していたのであるが、覚えるとこんなに便利なものはない。なにより、関数を入力すると参照間違いなどがありえるが、ピボなら間違わない。
というわけで、基本の使い方↓

-------------------------

１）入力範囲全体を選択し、メニューバーのデータ→ピボットテーブルとピボットグラフレポートをクリック
　　なぜ必要な部分だけでなく全体かというと、次項目の「※」参照
　　入力範囲が後から変わったときのことを考えて、あらかじめ名前をつけた範囲を名前で指定しても良い


なおこのとき、入力範囲の１行目に空白セルがあったり、同じ内容のセルがあったりすると、エラーが出てピボットテーブルを作成できないので気をつける。

２）そのまま「次へ」
　　※同じデータを使って別の分析をする場合は、ファイルサイズを小さくしメモリを節約するために、
　　　「分析するデータのある場所」は「ほかのピボットテーブルレポートまたはピボットグラフレポート」にし、
　　　表示された画面でそのまま「次へ」を選ぶ


３）ピボットテーブルを作成する場所を選び、「次へ」
　　新規シートでも既存のシートでもよいが、既存のシートの場合は貼り付けセルまで指定する　


４）ピボットの原型が表示されるので、どのような項目を集計するか指定する
（ア）必要な項目を必要な場所へドラッグする
　　　例（商品の認知度を年代別に集計）では、
　　　・行のフィールド：商品の認知度
　　　・列のフィールド：年代
　　　・データアイテムのフィールド：項番（←これはデータの必ずある列ならどこでもＯＫ）


（イ）集計値を正しく設定する
　　　データフィールド（集計値が表示されている部分）の右クリックメニューから「フィールドの設定」を選択し、

　　　新しいウィンドウで「データの個数」を選択し、ＯＫを押す


５）見た目を整える
　　必要なデータだけを表示し、見やすいものにする
（ア）不必要な行や列を隠す
　　　たとえばここでは未入力の行が空白でカウントされているが、それは集計上不必要なので、非表示にしたい項目名の右クリックメニューで「表示しない」を選択


（イ）項目の表示順がばらばらの場合は、並べ替える
　　　項目名の部分の右クリックメニューで「順序」を選択する
　　　行・列ともに同様の操作が可能


（ウ）不必要なゼロ値があれば、非表示にする
　　　メニューバーのツール→オプションの表示タブで「ゼロ値」チェックボックスのチェックを外す
　　　この場合、シート自体の設定でゼロを非表示にしているため、ゼロ値が必要な表がある場合は適用不可
　　　そういうときは、条件付書式で値がゼロの場合は文字色を背景色と同一にする設定をすればよい


-------------------------

これで一応完成、であるが、注意しなければいけない点として、
「データが自動で更新されない」
というものがある。
もし入力データを追加・修正した場合には、ピボットテーブル上の任意の場所の右クリックメニューから「データの更新」を選択し、手動で更新する必要がある。
面倒な場合はマクロを組んで、何らかのタイミングごと（ピボットが存在するシートの表示など）にピボットを更新するという手もあり。