VBS(VBA)のExcel操作でセルをコピーした時に表示形式が勝手に変わる現象

自分が困ったのでメモ

動作環境

Windows7 Professional
Excel2013
VBScript 5.8

ブックのコピーをしてみる

シートごとコピーする場合

Copy1.vbs

Option Explicit
Dim xl
Dim NewBook
Set xl = CreateObject("Excel.Application")

With xl.WorkBooks.Open("C:\test\コピー元.xlsx")
    'ブックをコピーする
    .Sheets.Copy
End With

'表示する
xl.Application.Visible = true
'参照の削除
Set xl = Nothing

8,9,13,14行目の書式が変わってる...

どうやらマイナス表記が括弧付きかつ桁区切りの場合が勝手に変わってしまう模様。

Copyメソッド以外でコピーしてみる

セルの値を張り直してみる

Copy2.vbs

Option Explicit
Dim xl
Dim NewBook
Set xl = CreateObject("Excel.Application")

With xl.WorkBooks.Open("C:\test\コピー元.xlsx")
    'ブックをコピーする
    .Sheets.Copy

    'シートの値をコピーする
    Set NewBook = xl.Workbooks(2)
    'シートの追加
    Call NewBook.Sheets.Add
    '使用しているセルをコピー
    .Sheets(1).UsedRange.Copy
    '書式、値すべて貼り付け
    NewBook.Sheets(1).Range("A1").PasteSpecial(-4104)

End With

'コピー中状態を解除
xl.CutCopyMode = false
'表示する
xl.Application.Visible = true
'参照の削除
Set xl = Nothing

セルの値を張り直しても結果は同じでした。

解決策1

マイクロソフトが解決策を提示してました。
VBAでも発生するようです。
https://support.microsoft.com/ja-jp/help/956331

書式をユーザー定義にして、「#,##0;[赤](#,##0)」のように設定すればコピー時に不備は発生しないみたいです。

解決策2

Excel側の書式を触ることが出来ない場合、NumberFormatLocalをコピー元から1セルずつ移植することで、一応対応できます。
但し、滅茶苦茶重いです。

Copy3.vbs

Option Explicit
Dim xl
Dim NewBook
Set xl = CreateObject("Excel.Application")

With xl.WorkBooks.Open("C:\test\コピー元.xlsx")
    'ブックをコピーする
    .Sheets.Copy

    'シートの値をコピーする
    Set NewBook = xl.Workbooks(2)

    Dim copySheet
    Dim copyRange
    '使用しているセルをコピー
    For Each copySheet In .Sheets
        For Each copyRange In copySheet.UsedRange
            '書式を貼り付け
            NewBook.Sheets(copySheet.Name).Range(copyRange.Address).NumberFormatLocal = copyRange.NumberFormatLocal
        Next
    Next
End With

'コピー中状態を解除
xl.CutCopyMode = false
'表示する
xl.Application.Visible = true
'参照の削除
Set xl = Nothing

うまくいきました

まとめ

基本的にはマイクロソフトの出している解決策で対応しましょう。
どうしてもExcelの書式を触れない場合は、無理やり書式だけ移していく方法が取れますが、滅茶苦茶重いのでお勧めはできないです。