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

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

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("クリップボードへ送る内容")

これで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@

を見るとNE:xxを見ることができます。って、VBAでプリンタ選択するためだけに危険なレジストリエディタを起動しなくてはいけないって、本当にMS様ゲイツ様はドSです。

そんな無駄な時間をゲイツ様に強いられたここ数年でしたが、先日ググってみたら、問題解決しました。

Sheets("シート名").PrintOut ActivePrinter:="プリンタ名"で印刷できるようになってた

数年前はそんな状況だったけど、2019年末にはそんな問題も解決してました。

以前の方法は以下の通り

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

でも今は以下だけでOK

Sheets("シート名").PrintOut ActivePrinter:="EPSON VP-4300"

この一行でプリンタの選択と印刷が可能です。しかもあの忌まわしきNE:xxを指定する必要なし。

最初からこうしてくれてればあんな苦労しなかったのにぃぃぃって感じです。

そんなわけで長いこと悩まされた2つの問題をこの年末年始に解決できました(*´ω`*) でも、また新たな解せぬ問題が生まれるんだろうな~、

そう、マイクロソフトならね

(・ω・)ノ

 

カテゴリ:PC・スマホ・WEBネタ

UNITORO以外の最近の投稿

N/A

twitter

Recent entries

Category

Popular entries 2018

Popular entries 2017

Popular entries 2016