EXCEL VBAのクリップボード文字化け問題とプリンタ名が頻繁に変わる問題を解決

2020-01-05

@title@

釣りに一切関係ないニッチソリューションシリーズです。

マイクロソフト社のエクセルVBAで発送伝票発行システムを作って毎日使っています。伝票の発行が終わると、お客さんに送るメールの内容がエクセルで生成され↓の

@title@

「メール文章コピー」っていうボタンをクリックすると、メール文章がクリップボードにコピーされます。そしてメールアプリに本文をペーストして送信。っていう一連の流れ。

が、しかし。2019年くらいから、いきなりクリップボードの内容が文字バケするようになりました。文字化けというか□にFFFFという制御文字みたいなのがメール本文に挿入されるトラブルが急に起こるように。

@title@

VBAペーストの文字バケが起こるタイミングに再現性が無い

この問題で一番困ったのは、文字化けが起こるときとそうでない時があって、その違いというか要因が全く不明なところ。しかもメールアプリ(サンダーバード)にペーストすると文字化けするけど普通のテキストエディタへ(EmEditor)のペーストは正常に行われます。

@title@

↑テキストエディタにペーストした図。この正しい内容がクリップボードに入っているようだけど、メールアプリ(サンダーバード)だと文字化けする。

この問題が発生した当時は、解決方法が全く解らないので、一旦テキストエディタにペーストしてからメールアプリに再度コピペするという面倒な作業を強いられました。

Windowsがキレイなときは文字化けしない

その後、しばらく使ってて文字化けしないケースが徐々に分かってきたんです。それは「Windowsがフレッシュな時は文字化けしない」です。再起動したりログインし直したりして該当のExcelブック・サンダーバード・クロームの3つを起動した状態では文字化けが一切起こらない。一方、他の業務で頻繁に使うアドビのアプリを使ったあとは100%文字化けを起こすことがわかったんです。

なのでオレの一日の業務の流れ的に、このVBAを含むEXCELを使った発送業務をする前に、一旦Windowsをログアウトし再度ログインする、っていう流れで2019年後半を過ごしてきました。幸いPCがSSDなので再ログインも10秒くらいで終わるので、大きな問題は無かったです。

とはいえ、例えばSSHのウインドウをたくさん開いてる時とか、丸一日かかるバックアップを動かしてるときなどWindowsのログアウトをしたくないときもある訳です。が、伝票発行は毎日やらないといけない業務なので、なんとかこの文字化け問題を解決せねば・・・・と思い続けはや一年くらい経過し、やっと時間ができたので直しました。

PutInClipboardが文字化けを起こす

文字化けを起こすVBAは以下の感じでした。

Message = "Hello" & vbCrLf & "Thank you for your shopping. ～略～

With New MSForms.DataObject
.SetText Message '変数の値をDataObjectに格納する
.PutInClipboard 'DataObjectのデータをクリップボードに格納する
End With

オレは20数年前にVBAをちょっとやってたことがあったけど、今回のような理不尽なトラブルが多すぎて(マイクロソフトあるある)人生の時間の無駄と思ってそれ以降やってません。が、この業務の自動化のためにやむを得ずVBAを組んだのです。組んだというと聞こえはいいけど、概ねマクロの自動記録とネットからのコピペですがw

そのクリップボードコピー部分もネットからコピペしたものです(*´ω｀*)

作った当時はこれで正常に動いていたけど、WindowsのバージョンやExcelのバージョンが変わったことでトラブルがでるようになったんですね。

「VBA クリップボード　文字化け」でググると同じ問題で悩んでいるひとが居て、それなりに解決方法が書かれてるけど、難しくてわけわからんw

そんな中、以下のページにとってもわかりやすく書かれてました。

VBAでクリップボードへのコピー｜teratail teratail[テラテイル]

↑を参考に作り直したのが以下のコード

ーーーーーーーーーーーーーーーーーーーーーー

Sub SetCB(ByVal str As String)
'クリップボードに文字列を格納
With CreateObject("Forms.TextBox.1")
.MultiLine = True
.Text = str
.SelStart = 0
.SelLength = .TextLength
.Copy
End With
End Sub

ーーーーーーーーーーーーーーーーーーーーーー

↑まずはマクロに上記サブルーチンをコピペ。そしてコピペする部分で↓

SetCB("クリップボードへ送る内容")

ってやれば問題解決です！

以下の赤文字が文字化けする旧バージョン

Message = "クリップボードへ送る内容"

With New MSForms.DataObject
.SetText Message '変数の値をDataObjectに格納する
.PutInClipboard 'DataObjectのデータをクリップボードに格納する
End With

↑これを文字化けしないバージョンにしたのが以下の緑文字↓

で、クリップボードに書きたい箇所で以下を記述

SetCB("クリップボードへ送る内容")

これでOKです！他の解説が難しくて理解できなかったオレのようなVBA初心者でも解決できることを願います！

VBAで指定してるプリンタ名がころころ変わって、そのたびにVBAがエラーを出す問題

ついでにもう一個の問題と解決方法も書いておきます。上記伝票発行システムはプリンタを3台使い分けしています。

複写式ドットインパクトプリンタのEPSON VP-4300
A4普通紙でインヴォイスを印刷するCanon LBP6230
粘着式ラベルシート用のCanon LBP252

以上3台を伝票の種類によってVBAから切り替えを行い印刷しています。そのシステムを最初に作った5～6年前の時点ではVBAでプリンタを切り替えをするには、以下の記述が必須のようでした。

EPSON VP-4300の例

Application.ActivePrinter = "EPSON VP-4300 on NE06:"
ExecuteExcel4Macro _ "PRINT(1,,,1,,,,,,,,2,""EPSON VP-4300"",,TRUE,,FALSE)"

Application.ActivePrinterでプリンタ名を指定するのはOKだけど問題は末尾の NE06:という部分。そうなんですよコントロールパネルとかアプリの印刷画面で目にするEPSON VP-4300という名称だけでは選択できず、まさかのWindows内部で使われているNE:○○という識別子まで指定しなくてはいけない、、っていうなんともマイクロソフトらしいダメ仕様。

ま、ここまでは100歩譲ってOKとします。でも、本当の問題はここから・・・

プリンタにつくNE:○○がころころ変わっちゃう大問題

ある日、いつも通りに伝票を発行してたらいきなりVBAがエラー。は？って思ったらプリンタが見つからない的なエラー・・・・え？何事？って調べたら

Windows内部で保持してるNE:xxのxx部分が全部変わっていた・・・っていう大惨事。

VBAでNE:00を指定してるのに、Windows側が変えちゃうからエラーになるわけです。

21世紀になって随分経つけどマイクロソフト社のやること、開発者への嫌がらせに近いこのような仕様は30年全く変わってないと痛感しましたw

で、どういうタイミングでNE:xxの番号が変わるのかと言うと社内LANのルーターを再起動したり途中のハブの電源が切れちゃったときなど。あ、EPSON VPはPCに直結だけどCanonレーザーは有線LAN接続です。なのでdhcpでプリンタのIPアドレスが変わるとWindows側のNE:xxも変わるっぽい(確実な検証はしてません)。それかPC本体を再起動したときも変わることがあるっぽい(未検証)ので、普段再起動は絶対にしないように心がけてるけど、またまたマイクロソフト社の陰謀で「Windowsアップデート」で夜間勝手に再起動されることもあるんですよね。なので、1～2ヶ月に一回はNE:xxが変わって、そのたびにVBAの Application.ActivePrinter = の部分をシコシコと書き換えしてました。該当するところが9箇所あるんで、そりゃもうたいへん。

ちなみにVBAでもNE:xxの番号を調べる方法はあるようですが、手っ取り早いのがレジストリエディタを見ることです。

コンピューターHKEY_CURRENT_USERSoftwareMicrosoftWindows NTCurrentVersionDevices

@title@