質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.05%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 3,517

score 23

●やりたいこと:
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ファイルを開くとき、ファイルをダブルクリックで開いた時と同じ値になるように
処理することはできますでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

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

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


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

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

これに対し、

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


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

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

対応方法

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

案①(取込結果を修正する)

簡単に言うと、ファイルをオープンした後で目的に金額のセルを通過型の「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/09 11:09

    ご回答、ご提案ありがとうございます。

    まずは、私の質問の仕方がわかりにくかったようで、混乱を招いたことをお詫びいたします。

    「494640」の値は「今まで手動でコピペしていた部分」であり、過去のデータです。
    「\300,321」は「今回VBAで開いたcsvファイル」で、新たに今回読み込んだ【別の】データです。

    「¥300,321」をコピペして「494640」になるわけではありません。

    今回のcsvファイルを手動でダブルクリックで開くと、「\300,321」の部分は「300321」の通貨型になります。
    わかりにくくてすみません。


    今回は他のcsvファイルもあるため、いただいた手順をもとに試行錯誤して問題の発生しない手順を考えてみます。
    試したところ、Excel上では「\300,321」のような標準(文字列)として書かれたセル状態でもそのまま数式バーから四則演算にかけて計算することはできるようなので、Excelブック側に追記して計算する程度の用途ならこのまま貼り付けてしまっても現状は問題なさそうです。

    キャンセル

+1

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

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

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

Sub ボタン1_Click()
    Call Test
End Sub

' ==================================================
'  テスト
' ==================================================
Sub Test()
    Dim msg As String: msg = ""

    ' CSVから読み込んだ値
    Dim csvVal As String
    csvVal = "\12,345"

    ' セルには以下の値を入れてテストしてます
    ' A1 = 通貨型 \12,345
    ' A2 = 文字列 "\12,345"
    ' A3 = 標準 12345
    ' A4 = 数値型 12345
    For Each c In range("A1:A4")

        Dim cellVal As String: cellVal = c.Value
        msg = msg & "[" & cellVal & "] = [" & csvVal & "] : " & Compare(cellVal, csvVal) & vbCrLf

    Next

    MsgBox msg
End Sub

' ==================================================
'  比較
' ==================================================
Function Compare(ByVal cellVal As String, ByVal csvVal As String) As Boolean

    If InStr(csvVal, "\") = 1 Then
        ' 先頭が \ マークの場合は一旦 通貨型にして比較

        ' ★通貨型にする
        Dim csvCur As Currency
        csvCur = CCur(csvVal)

        ' ★それを文字列にする
        Dim chk As String: chk = CStr(csvCur)

        ' ★比較
        Compare = (cellVal = chk)
    Else
        ' それ以外の場合はそのまま比較
        Compare = (cellVal = csvVal)
    End If

End Function

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/08 15:37 編集

    ああーちゃんと読んでなかった Workbooks.Open で開くんですね
    テキストで読み込むわけではないんですね ごめんなさい
    私の回答はスルーしてください ´ `

    キャンセル

  • 2017/02/09 11:06

    ありがとうございます。
    試行錯誤している最中、「CStr」してもダメだなぁと思っていましたが、このコードだと「CCur」で実施しているようですね。
    今後のコードの書き方の参考にさせていただきます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.05%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • トップ
  • VBAに関する質問
  • Excelでcsvの\マーク付きの金額欄が通貨として扱われるが、VBAで開くと文字列として扱われる