Q&A
第三者が状況を再現できる最小構成の情報を提示すると
回答なりコメントなりがつくかもしれません。
手元で適当にマクロを動かしたら2006/9/2になってました。
Excel での値貼り付けについてのトラブルです。
銀行の入出金記録で、日付が、和暦での y/m/d に表示されています。
令和6年9月2日 を、6/9/2 と表現しています。
西暦に変更して処理するために、エクセルシート上で変換しようとしたのですが、うまくいきません。
「6/9/2」をクリップボードにコピーして、シートに張り付けると、「2006/9/2」と表示されます。
毎回、このように表示されるとすれば、年部分に +18 することによって、簡単に西暦に変更できます。
ところが、この操作を、レコードマクロで行うと、「2002/6/9」となってしまいます。
y/m/d のデータが、キーボードでは yyyy/m/d になるのですが、マクロのペーストだと元のデータを d/m/y と認識し、yyyy/m/d に変更しているようです。
銀行の入出金データは、日付・金額・摘要などのデータがあり、フィールドごとにタブで区切られています。
マクロで paste した時に、日付部分を「2006/9/2」にするには、どうすればいいか教えてください。
環境は、Windows11 、Office365 です。
＜手順を記載します＞
メモ帳に ”6/9/2” を入力しておきます。
そこで、文字列をコピーして、クリップボードへ。
Ctrl + [V] で貼り付けると セルには「2006/9/2」と、日付として入力されます。
この操作を、レコードマクロで記録したのが、以下のものです。
再び、メモ帳の文字列 をクリップボードにコピーした後、マクロを実行すると、「2002/6/9」という日付データが入力されます。
Sub Macro1()
'
' Macro1 Macro
'
Range("D1").Select
ActiveSheet.Paste
Range("D2").Select
End Sub
キーボード操作と、マクロの操作で、結果が違うので困ってます。
> 「6/9/2」をクリップボードにコピーして、シートに張り付けると、「2006/9/2」と表示されます。
Excelのセルの書式の問題があると思います。セルの書式を日付用に編集してみてください。
> 毎回、このように表示されるとすれば、年部分に +18 することによって、簡単に西暦に変更できます。
西暦4桁部分が見た目、2桁になっているだけかと思うので、その処置は恐らく間違えてます。
古いExcelでも書式設定の欄で「平成」などの年号を意識できる設定があるので、確認してみてください。
※年号の場合、年内中に年号が変わるケースもあるので大事な情報だと思います。
> ところが、この操作を、レコードマクロで行うと、「2002/6/9」となってしまいます。
Excel自身の「マクロの記録」の機能のことでしょうか。
ExcelやWindowsのバージョンの詳細、操作など、もう少し詳しく説明しましょう。
> 銀行の入出金記録で、日付が、和暦での y/m/d に表示されています。
> 令和6年9月2日 を、6/9/2 と表現しています。
> 銀行の入出金データは、日付・金額・摘要などのデータがあり、
> フィールドごとにタブで区切られています。
コピー元は Excel ワークシート上のセルでしょうか。
それとも、 Excel とは別のアプリケーションで開かれた
何らかのファイル上の文字列でしょうか。
> 「6/9/2」をクリップボードにコピーして、シートに張り付けると、
> 「2006/9/2」と表示されます。
例えば「メモ帳（notepad.exe）上で開かれたテキストファイルの中に
含まれている "6/9/2" という文字列を範囲選択してクリップボードにコピーし、
それを Excel ワークシート上の任意のセルに貼り付けた」という意味で
おっしゃっているならば、Excel の仕様通りの動作です。
> この操作を、レコードマクロで行うと、「2002/6/9」となってしまいます。
そのマクロのコードを具体的に明記して下さい。
メモ帳に ”6/9/2” を入力しておきます。
そこで、文字列をコピーして、クリップボードへ。
Ctrl + [V] で貼り付けると セルには「2006/9/2」と、日付として入力されます。
この操作を、レコードマクロで記録したのが、以下のものです。
再び、メモ帳の文字列 をクリップボードにコピーした後、マクロを実行すると、「2002/6/9」という日付データが入力されます。
Sub Macro1()
'
' Macro1 Macro
'
'
Range("D1").Select
ActiveSheet.Paste
Range("D2").Select
End Sub
キーボード操作と、マクロの操作で、結果が違うので困ってます。
動作環境は Windows11 Office365 です。
ここは質問に対するコメントを書くところです。 質問への情報の追加はここではな質問を編集して追記しましょう。訪れた人が状況を把握しやすくなります。
回答1件
0
ベストアンサー
メモ帳に ”6/9/2” を入力しておきます。
そこで、文字列をコピーして、クリップボードへ。
Ctrl + [V] で貼り付けると セルには「2006/9/2」と、日付として入力されます。
貼り付け先となるセルのその時点での表示形式が「標準」である場合、
Excel はシステムの地域設定に基づき、セルに入力された文字列 "6/9/2" を
日付データとして解釈して型変換を行った上、更に D1 セルの表示形式を
「日付」に変更します。
例えばシステムの地域設定において、日付（短い形式）の表示形式が「yyyy/MM/dd」、
かつ 2 桁年入力の有効範囲が 1950 ～ 2049 と設定されている場合、
"6" を yyyy パート、"9" を MM パート、"2" を dd パートとみなし、
更に "6" を西暦 2006 年と解釈して 2006/09/02 00:00:00 という日時データ
（シリアル値 38962）に変換します。
また、そのセルの内部上の表示形式はユーザー定義形式「yyyy/m/d」に
相当するものに変更されます。
ここで重要なのは、上記の挙動はあくまで Excel ワークシート上のセルを
ユーザーが直接編集した場合のものである、という点です。
再び、メモ帳の文字列 をクリップボードにコピーした後、マクロを実行
Range("D1").Select
ActiveSheet.Paste
「2002/6/9」となってしまいます。
一方、VBAにおける日付リテラルの既定の書式は m/d/yyyy です。
Date リテラルは、シャープ記号 (# #) で囲む必要があります。 日付の値は、#5/31/1993# のように M/d/yyyy の形式で、または #1993-5-31# のように yyyy-MM-dd の形式で指定する必要があります。 最初に年を指定する場合は、スラッシュを使用できます。 この要件は、ロケールやコンピューターの日付と時刻の設定とは無関係です。
そのため、クリップボードにコピーされた "6/9/2" という文字列を
Paste メソッドによって貼り付ける際、"6" が m パート、"9" が d パート、
そして "2" が yyyy パートとみなされ、その結果として
2002/06/09 00:00:00 という日時データに変換されているものと推定されます。
挙動としては、以下のマクロと同じ感じでしょう。
vba
1Sub Macro2() 2 3 With Range("E1") 4 .NumberFormat = "general" 5 .Value = "6/9/2" 6 End With 7 8End Sub
日付が、和暦での y/m/d に表示
西暦に変更して処理するために、エクセルシート上で変換
上記については、日付文字列の先頭に「元号を表す文字列」を付加した上で
日時データに型変換するようにするのが無難です。
vba
1Sub Macro3() 2 3 '[日付]列の列番号 4 Const DateColumn = 1 5 6 Dim wsDestination As Worksheet 7 Dim rngTarget As Range 8 Dim lngRow As Long 9 Dim lngFirstRow As Long 10 Dim lngLastRow As Long 11 12 Application.ScreenUpdating = False 13 14 '新規ワークシートの追加 15 With ThisWorkbook.Worksheets 16 Set wsDestination = .Add(After:=.Item(.Count)) 17 End With 18 19 With wsDestination 20 21 '[日付]列全体の表示形式を一時的に「文字列」にする 22 .Columns(DateColumn).NumberFormat = "@" 23 24 '貼り付け先範囲の先頭の行番号の指定 25 lngFirstRow = 1 26 27 '貼り付け先セルの選択 28 .Cells(lngFirstRow, 1).Select 29 '貼り付け実行 30 .PasteSpecial Format:="テキスト", _ 31 Link:=False, _ 32 DisplayAsIcon:=False 33 34 '[日付]列全体の表示形式を「標準」に戻す 35 .Columns(DateColumn).NumberFormat = "general" 36 37 '最後の行番号の取得 38 lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 39 40 '先頭行から最終行までループ 41 For lngRow = lngFirstRow To lngLastRow 42 '[日付]列のセルの操作 43 With .Cells(lngRow, DateColumn) 44 'セルの値が日時データと解釈できる場合 45 If IsDate(.Value) Then 46 '表示形式の設定 47 .NumberFormat = "[$-ja-JP-x-gannen]ggge""年""m""月""d""日"";@" 48 '元号（R）を付加した上で型変換した結果を代入 49 .Value = CDate("R" & .Value) 50 End If 51 End With 52 Next 53 54 '列幅の自動調整 55 .UsedRange.EntireColumn.AutoFit 56 57 End With 58 59 Set wsDestination = Nothing 60 61 Application.ScreenUpdating = True 62 63End Sub
投稿2024/10/04 06:56
編集2024/10/04 09:23総合スコア1014
WEBのデータをコピーして編集することを想定しています。
銀行の入出金データは、日付・金額・摘要などのデータがあり、クリップボードのデータは、フィールドごとにタブで区切られています。
これらのデータを、複数行コピーし、エクセルに貼り付けたのち、処理をしたいと考えています。
クリップボードのデータを貼り付け時点で、日付部分は エクセルが修正してしまいます。
手作業で、クリップボードのデータを「値貼り付け」→「テキスト」とすれば、日付部分は手入力と同じように
「2006/9/2」の形になるので、年部分に差分を加えれば、希望通りの処理にはなります。
この手作業なしで、マクロで実行できればありがたいのです。
VBAを使って、日付部分を、2006/9/2 のように加工するには、どのような方法が考えられますか。
> WEBのデータをコピーして編集することを想定しています。
その「WEBのデータ」とやらは具体的にどのような形式のもので、
どのようにして「コピー」されているのでしょうか。
> 銀行の入出金データは、日付・金額・摘要などのデータがあり、
> クリップボードのデータは、フィールドごとにタブで区切られています。
> これらのデータを、複数行コピー
・あるWebサイトが提供しているサービスや機能によって、一定書式の
タブ区切りテキストファイル（TSV ファイル）をダウンロードしている。
・ある Web サイト上の何らかのページをブラウザによって開き、
そのページ上に表示されている表（テーブル）や文字列を範囲選択して
クリップボードにコピーし、メモ帳や Excel に直接貼り付けている。
例えば「 TSV ファイルを Excel ワークシートに取り込む」という前提なら、
Power Query によってデータの取込と加工を行なえばよいと思いますが。
> ・ある Web サイト上の何らかのページをブラウザによって開き、
> そのページ上に表示されている表（テーブル）や文字列を範囲選択して
> クリップボードにコピーし、メモ帳や Excel に直接貼り付けている。
このような形で行っています。
クリップボードにコピーした後、ワークシート上で「形式を選択して貼り付け」→「テキスト」にすれば、日付部分は、希望通りになるのです。
この操作を、マクロで再現できればいいのですが。
> ・ある Web サイト上の何らかのページをブラウザによって開き、
> そのページ上に表示されている表（テーブル）や文字列を範囲選択して
> クリップボードにコピーし、メモ帳や Excel に直接貼り付けている。
つまり本当にやりたいことは「ウェブスクレイピング」なのではないかと思われますが。
> クリップボードにコピーした後、ワークシート上で
> 「形式を選択して貼り付け」→「テキスト」にすれば、
> 日付部分は、希望通りになるのです。
それは既に申し上げた通り、あくまで「手作業でやれば」の話です。
マクロによって Paste メソッドや PasteSpecial メソッドを実行する限り、
「貼り付け実行時に」 m/d/yyyy 形式の日付文字列とみなされることは
回避できないでしょう。
> VBAを使って、日付部分を、2006/9/2 のように加工するには、
> どのような方法が考えられますか。
あくまで「クリップボードからの貼り付け」を前提とされるのであれば、
m/d/yyyy 形式の文字列であると解釈されることを前提とした上で、
「貼り付け実行後に」無理やり各セルの値を変換するか、もしくは
「貼り付け実行前に」日付の貼り付け先となるセル範囲の表示形式を
「文字列」にしておくぐらいしかないと思います。
間違いのため削除しました
> m/d/yyyy 形式の文字列であると解釈されることを前提とした上で、
クリップボードの中身に、”” を付加して文字列として認識させるなんてことは、できないですかね。
エクセルの解釈をさせずに、文字列 にしてしまえば、あとから、加工できるのですが。
とりあえずMacro3のコードを、貼り付け先を新規ワークシートのA1セルとし、[日付]列が1列目であると仮定した形に修正しました。
回答ありがとうございます。
試したところ問題が解決しました！
頂戴したコードをベースに、加工してみたいと思います。
ベストアンサーに選ばせていただきました。
あなたの回答
tips
プレビュー
