Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 Pro : Excel 2010)
セル文字列のコピペで日付データにならない?
投稿日時: 18/06/01 19:26:07
投稿者: tako552101

win8.1pro 2010です。
 
件名がわかりづらくてすみません。
 
ファイルbook1.xlsのsheet1のJ列はユーザー定義で「[$-411]ge/mm/dd(例:S40/08/25)」と設定しています。このファイルに必要なデータ(csvファイル)を取込むマクロでJ列に生年月日をコピペするようにしています。
 
コピー基ファイルのBY列はセル書式が文字列で「1977/02/09」のように入力されており、この値を下記のようにコピペしています。
 

book1.Sheets("sheet1").Range("BY2:BY120").Copy
ThisWorkbook.Sheets("sheet1").Range("J4").PasteSpecial Paste:=xlPasteValues

しかし、コピペ後J列は「1977/02/09」の表示ままで「S52/02/09」とはなりません。(手動でコピペしても同じ)
 
J列のセルをダブルクリックで文字選択状態にすると意図した表示なります。
 
J列を計算に使用して例えば「2018-YEAR(J4)」は正しく計算されますが「=IF(J4<DATE(1956,5,25),1,999)」は正しく計算されません。
 
原因と回避策ご指導ください。

回答
投稿日時: 18/06/01 22:08:26
投稿者: simple

> コピー基ファイルのBY列はセル書式が文字列で「1977/02/09」のように入力されており
ということは、Date型の値ではなく、純粋に文字列なんですね?
それをJ列に値コピーしても日付じゃないんですから、
>「S52/02/09」とはなりません。
は当然じゃないですか?
 
>J列のセルをダブルクリックで文字選択状態にすると意図した表示なります。
それは、あらためて1977/02/09と入力したことと同じですから、
Excelは気を利かせてそれを日付と認識します。
そして、書式に従って、表示は「S52/02/09」となります。
 
回避策は、色々な方法があると思います。
 
例えば、
・どこか任意のセルに数値の1を入力して、
・それをコピー
・今の、失敗に終わっているセルたちを選択し、
・「形式を選択して貼り付け」で、
・「値」チェックを入れ、「乗算」をクリック
 

回答
投稿日時: 18/06/02 00:19:50
投稿者: WinArrow
投稿者のウェブサイトに移動

>このファイルに必要なデータ(csvファイル)
 
そもそも、CSVファイルは、テキストデータファイルなので、書式は持っていません。
従って、人間が日付を見えているデータは、日付形式ではなく、単なる文字列です。
 
しかし、開き方で、それを日付として認識させる方法があります。
 
CSVファイルをどのっような方法で開いているのでしょうか?
若しかしたら、その開き方を変更すれば、うまくいくかも?

回答
投稿日時: 18/06/02 00:26:50
投稿者: WinArrow
投稿者のウェブサイトに移動

少し説明とコードが整合していないところあり・・・
>ファイルbook1.xlsのsheet1のJ列はユーザー定義で〜〜〜

book1

csvファイル

コピー元ファイル・・・コードではbook1と書いてある
の関係を整理して、説明してください。
 
 

投稿日時: 18/06/02 05:50:42
投稿者: tako552101

みなさん、ありがとうございます。
 
csvファイル=data.csv
メインファイル(ThisWorkbook)=book1.xls
メインファイルで新規book=book2.xls
として、
 
csvを開き、
fn = Application.GetOpenFilename("CSVファイル(*.csv),*.csv")
メインファイルで新規ブック作成(book2.xls)し、
ActiveSheet.QueryTables.Add(Connection:="text;" & fn, Destination:=Range("$A$1"))
として、
 
book2.Sheets("sheet1").Range("BY2:BY120").Copy
ThisWorkbook.Sheets("sheet1").Range("J4").PasteSpecial Paste:=xlPasteValues
としていました。
 
コードはネットから拾ったものをそのまま使用しており、よく見ると途中でbook2、sheet1のセルを全て文字列にしている部分がみつかり、たぶん以下
Dim v(255) As Long
For i = 0 To 255
v(i) = 2
End If
v(i) = 1 に修正にしましたら、日付として認識されるようになりました。
大変お騒がせしました。
 
とこで、前のままでは日付として認識されていないはずのJ列がなぜ「2018-YEAR(J4)」は正しく計算されるのでしょう。

回答
投稿日時: 18/06/02 08:34:30
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

fn = Application.GetOpenFilename("CSVファイル(*.csv),*.csv")
メインファイルで新規ブック作成(book2.xls)し、
ActiveSheet.QueryTables.Add(Connection:="text;" & fn, Destination:=Range("$A$1"))
として、

 

 
fn = Application.GetOpenFilename("CSVファイル(*.csv),*.csv") 
Set book2 = Workbooks.Open(Filename:=fn

に変更してみてください。
 
引用:
Dim v(255) As Long
 For i = 0 To 255
 v(i) = 2
 End If

は、不要になると思います。

投稿日時: 18/06/02 09:48:21
投稿者: tako552101

実は取り込むセルの中にどうしても文字列で取り込む必要があって
前述のようなコードになっています。
Workbooks.Openでも、一部のセルのみ文字列指定ができればいいのですが。
文字は「+15」などプラス記号が含まれた数字で、Excelで開くと「+」が消えてしまいます。

回答
投稿日時: 18/06/02 10:03:34
投稿者: よろずや

Year 関数
年を表す整数を含む Variant ( Integer ) を返します。
 
構文
 
Year(date)
必須の date引数は、日付を表す任意の Variant、 数式、 文字列式、または任意の組み合わせです。
date に Null が含まれている場合は Null が返されます。

回答
投稿日時: 18/06/02 10:04:57
投稿者: simple

最初からコードをキチンと出してもらえば議論ができたと思いますが、
要するに、書式指定を配列vで指定しているようだから、
日付の列だけを変更すればよいだけの話だと思います。
 
なお、追加の質問(なぜYearは動作するんだ)については、
本来シリアル値を指定すべきだが、文字列の場合もExcelが努力して拾っている、ということでしょう。
 
Yearのヘルプを確認して頂きたいが、、
>日付を文字列として入力した場合、エラーが発生することがあります。
などと書いてあります。
要するに、サービスはするけど、それを期待しないでね、ということじゃないですか?

回答
投稿日時: 18/06/02 10:33:06
投稿者: simple

ちなみに、
TextFileColumnDataTypesに与える定数は、
日付(YMD形式)は 5 です。(もちろん、xlYMDFormatを使うのがベターだろうが)

回答
投稿日時: 18/06/02 11:10:17
投稿者: WinArrow
投稿者のウェブサイトに移動

tako552101 さんの引用:
実は取り込むセルの中にどうしても文字列で取り込む必要があって
前述のようなコードになっています。
Workbooks.Openでも、一部のセルのみ文字列指定ができればいいのですが。
文字は「+15」などプラス記号が含まれた数字で、Excelで開くと「+」が消えてしまいます。

「Workbooks.OPenでCSVファイルを開く」を提案した際も
文字列にしなければいけない項目があるかもしれないと懸念したが、
 
引用:
Dim v(255) As Long
 For i = 0 To 255
 v(i) = 2
 End If
 v(i) = 1 に修正にしましたら、日付として認識されるようになりました。
 大変お騒がせしました。

があった(どこで実行したのかわかりませんが)ので、
日付だけ認識できれば解決すると考えていました。
 
項目がいくつあるのかわかりませんが、
文字列で取り込んでから、特定の列だけ日付に変換すれば済む話ですよね?
 
>とこで、前のままでは日付として認識されていないはずのJ列がなぜ「2018-YEAR(J4)」は正しく計算されるのでしょう。
Year関数の引数が文字列なのになぜ計算されるか?
これは、Excelの親切機能です。
このようなことは、ほかにもたくさんあります。
 
親切と受け取るか、大きなお世話と受け取るかは、その時、個人差がありますから、
便利な機能は使い、大きなお世話と感じるならば、使わない
というスタンスがよいと思います。
 
>「2018-YEAR(J4)」
は、年齢を計算しているものと思いますが、
「数え」の年齢になりますが、問題はないのですね?
 
 
 

投稿日時: 18/06/02 11:23:06
投稿者: tako552101

引用:
項目がいくつあるのかわかりませんが、 文字列で取り込んでから、特定の列だけ日付に変換すれば済む話ですよね? 

なるほどそういう手もありますね。
年齢計算は例を上げただけです。
 
原因と回避策は見えてきたので、模索してみます。
ありがとうございます。
 

回答
投稿日時: 18/06/02 15:28:15
投稿者: WinArrow
投稿者のウェブサイトに移動

今回の日付の列だけの回避策
 
一旦、全項目文字列で受取ったデータを、対象範囲だけを日付で複写する方法
 

Dim 行数 As Long

行数 = book2.Sheets("sheet1").Range("BY2:BY120").Rows.coount
ThisWorkbook.Sheets("sheet1").Range("J4").Resize(行数).NumberFormatLocal = "yyyy/m/d;@"
book2.Sheets("sheet1").Range("BY2:BY120").Copy
ThisWorkbook.Sheets("sheet1").Range("J4").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlAdd
Application.CutCopyMode = False

 
複写先のセル範囲に表示形式"yyyy/mm/dd"を設定する
貼り付けるときに「形式を選択して貼り付け」→「値」「加算」を選択する。

回答
投稿日時: 18/06/02 18:10:07
投稿者: WinArrow
投稿者のウェブサイトに移動

おまけ・・・・
 
コードをすっきりさせてみました
 

Dim Target As Range

Set Target = book2.Sheets("sheet1").Range("BY2:BY120")
With ThisWorkbook.Sheets("sheet1").Range("J4")
    .Resize(Target.Rows.Count).NumberFormatLocal = "yyyy/mm/dd;@"
    Target.Copy
    .PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlAdd
End With
Application.CutCopyMode = False

投稿日時: 18/06/02 22:21:19
投稿者: tako552101

ありがとうございます。
試させていただきます。

投稿日時: 18/07/24 09:55:35
投稿者: tako552101

締め忘れていました。