Excelでcsvの\マーク付きの金額欄が通貨として扱われるが、VBAで開くと文字列として扱われる

●やりたいこと：
Excel2010、および2016の環境でVBAを組んでいます。

地方銀行の取引明細やカードの使用履歴など、様々なcsvファイルを
Excelのシートごとに分けて手動でコピー&ペーストしていましたが、
これをVBAで自動化しようとしています。

●問題点：
すでにExcel側に存在する情報については、重複しないようコピペせずに飛ばし、
まだコピペしていない行のみコピペするようにしていますが、下記フォーマットの
csvがうまく処理できません。

"001","","12月15日","","","\300,321","","振込","ﾏﾙﾊﾞﾂｸﾞﾙｰﾌﾟ(ｶ"

※機密情報のため、社名や金額は仮のものに変換しています。
※金額欄の\は円マーク

実際にはすでにExcel側に記入済みの行にもかかわらず、重複していない行と
判定されてコピペされてしまいます。

●確認したこと：
セルの内容を数式バーで確認してみると、今まで手動でコピペしていた部分は金額欄が
下記のように数字になっており、形式は「通貨」になっています。
該当csvをダブルクリックでExcelから開いても同様です。

494640

今回VBAで開いたcsvファイルは、「Workbooks.Open Filename:=」コマンドで開いた時点で、
下記のように数式バーでバックスラッシュ記号とカンマ付きの文字列として扱われています。

\300,321 ←\はバックスラッシュ

おそらくExcelの仕様だと思うのですが、ダブルクリックでcsvファイルを開いた時と、
「Workbooks.Open Filename:=」コマンドでcsvファイルを開いた時とで
金額部分のセルの表示が変わってしまっているようです。

これが原因で、過去に手動でコピペしたセルとVBAで開いたcsvのセルが一致しないと判定されてしまうようです。

●実際のコード：
コピペは下記のコードで実施しました。

各変数
TargetBook → csvのファイル名
CP_FirestRow → 範囲選択でコピーする際の最初の行（csv側）
LastRow_target → 範囲選択でコピーする際の最後の行（csv側）
LastCol → コピー元(csv)の列数
LastRow_this → 貼り付け先のシートの最後の行（Excel側）

＝＝＝＝＝＝＝＝＝＝＝＝
Workbooks(TargetBook).Activate
Workbooks(TargetBook).Sheets(1).Range(Cells(CP_FirestRow, 1), Cells(LastRow_target, LastCol)).Copy
ThisWorkbook.Activate
.Cells(LastRow_this + 1, 2).PasteSpecial Paste:=xlPasteAll
＝＝＝＝＝＝＝＝＝＝＝＝

貼り付け先Excel側と元のcsv側の行が重複しているか
確認する関数。

＝＝＝＝＝＝＝＝＝＝＝＝
'CSVの該当行とExcelシートの行との重複をチェックする関数
'SheetNameはExcel側シート名、TargetBookがCSV名、LastColが列数、iはcsv側の抽出する行、rはExcel側で抽出する行
Function Chofuku_chk(ByVal SheetName As String, TargetBook As String, LastCol As Long, i As Long, r As Long)
Dim p As Long, Chofuku As Boolean

Chofuku = False

For p = 1 To LastCol    '左の列から順にチェック
    'Excel側の左1列はチェック欄のため、p + 1で1列ずらす
    If Workbooks(TargetBook).Sheets(1).Cells(i, p).Value = _
    ThisWorkbook.Worksheets(SheetName).Cells(r, p + 1).Value Then
        Chofuku = True
    Else
        Chofuku = False
        Exit For
    End If
Next p

Chofuku_chk = Chofuku     '左の列から右の最後の列まで、すべて一致したときのみTrueを返す

End Function
＝＝＝＝＝＝＝＝＝＝＝＝

上記 Chofuku_chk と同じコードを使って検証してみたところ、やはり金額のセルのところで不一致を
起こしていました。

※年をまたいでいるので、csv側の日付部分も勝手に「2017/12/15」など、今年の日付に
されてしまい不一致判定でしたが、これは別途対応予定です。

VBA上でcsvファイルを開くとき、ファイルをダブルクリックで開いた時と同じ値になるように
処理することはできますでしょうか？

行動規範の内容に同意します

回答2件

ベストアンサー

「¥300,321」をコピペしても「494640」にはなりません。
特に何も指定せずに元のセルのままコピペしていれば「300321」の通貨型で貼り付くはずです。

494640は「\300,321」の比較対象ではなく、サンプル的に提示いただいた他データの値という認識で大丈夫ですよね？

提示いただいた内容のCSVファイル（1行のみ）を作成し、手元の環境で確認してみてみました。

まずファイルのダブルクリック、及び「ファイル」メニューからファイル名を指定して開いた場合には、「¥300,321」の項目は通貨型として取り込まれ、値は「300321」となっていました。

これに対し、

Workbooks.Open Filename:="C:\Test\Test.csv"

というマクロで同ファイルを開いてみたところ、同項目は標準で取り込まれ、値も「¥300,321」となりました。

比較結果が一致しないのはこのためだと思われます。

対応方法

問題の回避方法は大きく２つあると思います。
①比較時に一致するように、セルの内容を修正する。
②比較時に一致するように、比較の判定方法を変更する。

案①（取込結果を修正する）

簡単に言うと、ファイルをオープンした後で目的に金額のセルを通過型の「300321」という値に変更してあげる方法です。

Dim wb As Workbook
Set wb = Workbooks.Open("C:\Test\Test.csv")

Dim ws As Worksheet
Set ws = wb.Sheets(1)

'データのある範囲をループ処理
Dim iRow As Long
For iRow = 1 To ws.Cells(Rows.Count, "F").End(xlUp).Row
    '書式設定
    ws ws.Cells(iRow, "F").NumberFormatLocal = "\#,##0_);[赤](\#,##0)"
    
    '値の変更（￥とカンマを除去）
    Dim sVal As String
    sVal = ws.Cells(iRow, "F").Value
    ws.Cells(iRow, "F").Value = Replace(Replace(sVal, ",", ""), "\", "")
Next

案②（比較判定を変更する）

これも簡単に言うと、「\300,321」と「300321」は同じだという判定に修正するということです。

Function Chofuku_chk(ByVal SheetName As String, TargetBook As String, LastCol As Long, i As Long, r As Long) As Boolean
    Dim p As Long, Chofuku As Boolean
    
    Chofuku = False
    
    For p = 1 To LastCol    '左の列から順にチェック 
        'Excel側の左1列はチェック欄のため、p + 1で1列ずらす 
        'If Workbooks(TargetBook).Sheets(1).Cells(i, p).Value = _ 
        '    ThisWorkbook.Worksheets(SheetName).Cells(r, p + 1).Value Then 
        If ChkVal(Workbooks(TargetBook).Sheets(1).Cells(i, p), ThisWorkbook.Worksheets(SheetName).Cells(r, p + 1)) = True Then 
            Chofuku = True 
        Else 
            Chofuku = False 
            Exit For 
        End If 
    Next p
    
    Chofuku_chk = Chofuku     '左の列から右の最後の列まで、すべて一致したときのみTrueを返す
    
End Function 

'判定を行う関数
Function ChkVal(rng1 As Range, rng2 As Range) As Boolean
    'ファイル側の参照列によって判定を変更
    If rng1.Column = 6 Then
        '参照列がF列（金額）の場合
        Dim dVal1 As Double
        Dim dVal2 As Double
        '\とカンマを除去してDouble型に変換
        dVal1 = CDbl(Replace(Replace(rng1.Value,",",""),"\","")
        dVal2 = CDbl(Replace(Replace(rng2.Value,",",""),"\","")
        If dVal1 = dVal2 Then
            ChkVal = True
        Else
            ChkVal = False
        End If
    Else
        '参照列がF列（金額）以外の場合
        If rng1.Value = rng2.Value Then
            ChkVal = True
        Else
            ChkVal = False
        End If
    End If
End Function

まとめ

どちらでも目的の結果が得られると思います。

単純に「思ったような比較できていない」という問題に対して考えると、元データに手を加えて今までの判定を行う案①よりも、判定方法自体を見直す案②の方がよさそうに思います。
ただし今回は開いた時点で現行とは異なる状態になっているので、最終的な貼り付け結果も既存に合わせて出力したいのであれば、案②だとペースト時に金額列の加工が必要となってしまいます。
どうせ加工するのなら案①のように最初に加工してしまった方が、後続処理に手を加える必要もなくなります。

今回は金額についてのみ加工するサンプルを提示させていただきましたが、日付など他にも型や値の考慮が必要な列があればそれぞれ対応が必要になると思います。

がんばってみてください。

投稿2017/02/08 05:52

jawa

総合スコア3020

ot2os

2017/02/09 02:09

ご回答、ご提案ありがとうございます。まずは、私の質問の仕方がわかりにくかったようで、混乱を招いたことをお詫びいたします。「494640」の値は「今まで手動でコピペしていた部分」であり、過去のデータです。「\300,321」は「今回VBAで開いたcsvファイル」で、新たに今回読み込んだ【別の】データです。「¥300,321」をコピペして「494640」になるわけではありません。今回のcsvファイルを手動でダブルクリックで開くと、「\300,321」の部分は「300321」の通貨型になります。わかりにくくてすみません。今回は他のcsvファイルもあるため、いただいた手順をもとに試行錯誤して問題の発生しない手順を考えてみます。試したところ、Excel上では「\300,321」のような標準（文字列）として書かれたセル状態でもそのまま数式バーから四則演算にかけて計算することはできるようなので、Excelブック側に追記して計算する程度の用途ならこのまま貼り付けてしまっても現状は問題なさそうです。

行動規範の内容に同意します

CSV の値を読み込んだ時に、先頭が￥だったら「CSV の値を通貨型に変換」してから比較するのはどうですか？
…と思いましたが、それだとセル側が数値型とかでも一致とみなされますね…

もし「Cell：数値型 12345」「CSV：\12,345」の場合に一致としたくないなら、さらにセルの書式設定なども見て判定する必要がありますね。
(NumberFormatLocal ですかね)

あと、"\123a" みたいな値がある場合を考慮するなら、「通貨型への変換に失敗したら、単純に文字列として比較する」みたいな処理も必要ですね。

vba
1Sub ボタン1_Click()
2    Call Test
3End Sub
4
5' ==================================================
6'  テスト
7' ==================================================
8Sub Test()
9    Dim msg As String: msg = ""
10    
11    ' CSVから読み込んだ値
12    Dim csvVal As String
13    csvVal = "\12,345"
14    
15    ' セルには以下の値を入れてテストしてます
16    ' A1 = 通貨型 \12,345
17    ' A2 = 文字列 "\12,345"
18    ' A3 = 標準 12345
19    ' A4 = 数値型 12345
20    For Each c In range("A1:A4")
21        
22        Dim cellVal As String: cellVal = c.Value
23        msg = msg & "[" & cellVal & "] = [" & csvVal & "] : " & Compare(cellVal, csvVal) & vbCrLf
24    
25    Next
26    
27    MsgBox msg
28End Sub
29
30' ==================================================
31'  比較
32' ==================================================
33Function Compare(ByVal cellVal As String, ByVal csvVal As String) As Boolean
34    
35    If InStr(csvVal, "\") = 1 Then
36        ' 先頭が \ マークの場合は一旦 通貨型にして比較
37
38        ' ★通貨型にする
39        Dim csvCur As Currency
40        csvCur = CCur(csvVal)
41
42        ' ★それを文字列にする
43        Dim chk As String: chk = CStr(csvCur)
44        
45        ' ★比較
46        Compare = (cellVal = chk)
47    Else
48        ' それ以外の場合はそのまま比較
49        Compare = (cellVal = csvVal)
50    End If
51    
52End Function