いつもお世話になっております。
以下のようなデータをWebサイトからコピーします。ここでは1行分のデータのみ示していますが、通常は100行以上のデータを扱います。
08/08/28 09:00 94.38 94.54 94.38 94.49
日付、時間、データ1,2,3,4です。
各データはスペースで区切られています。
このデータをシートのあるセルに貼り付けると、一つのセルに上記のデータがすべて入力されます。
それぞれを分割したいので、区切り位置指定ウィザードで区切ります。
すると、それぞれのデータが6列に分割されます。
分割後、先頭の日付は2008/8/28と表示されます。
上記の操作を下記VBAコードで自動でやらせると日付が2028/8/8になってしまいます。
Worksheets("test").Range("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
質問1.なぜVBAでやると日付がおかしくなるのでしょうか。
この問題を解決するために元のデータを貼り付けた後にデータの先頭に20を付けて2008/08/28にしたいのですが、一度区切り位置指定ウィザードを使うと次回から貼り付けたデータは前回の設定で自動で区切られてしまい、データの先頭に20を付けたら日付が202028/8/8となってしまいます。
質問2.区切り位置指定ウィザードの設定をVBAでリセットすることはできないでしょうか。
うまく説明できませんが、本当に困っています。
よろしくお願い致します。
No.3
- 回答日時:
こんにちは。
>Array(1,5)に変更した設定が機能しておらず、日付が2028/8/8となります。
>全く、訳が分かりません。
テキストインポートしたものは、いわゆる「眠っているデータ」で、貼り付けたりしたら、その「眠っているデータ」は、目を覚まして、Excel側の「型のキャスト」でそれに相応しいデータ型に変えられてしまいます。
Excel2003 ですと、少し違った状態になるはずですが、もともと、Excelは、アメリカ産のソフトウェアですから、以下のような表記に対して、それに相応しい型に解釈されてしまいます。
>08/08/28
#8/8/2028# '上記のリテラル値は、Excel2003 でも同じ
Excelのバージョン/OS側の設定にもよりますが、簡易型のテキストインポートでは、うまく行かないことも多いです。
「この問題を解決するために元のデータを貼り付けた『後』に」という考え方ですと、うまくいきません。型のキャストされた後では、元の値は取り戻せません。
例えば、貼り付ける前に以下のようにしてあげることです。
With CB
.GetFromClipboard
buf = .GetText
End With
'buf の中身を調べて、日付型の場合は、20 を付加する。
buf1 = Split(buf, vbCrLf)
i = 1
For Each nLine In buf1
j = 1
For Each v In Split(Trim(nLine), Space(1))
If v Like "##/##/##" Then '日付型の場合
Cells(i, j).Value = "20" & v
Else
Cells(i, j).Value = v
End If
j = j + 1
Next v
i = i + 1
Next nLine
>質問2.区切り位置指定ウィザードの設定をVBAでリセットすることはできないでしょうか。
それは、もう一度、ダミーをインポートすれば直りますが、そのような必要性はないはずです。正しいプロパティ値にすればよいだけのことですから。
Wendy02様、回答ありがとうございます。
2028/8/8となる理由は理解できました。
アメリカが基準なんですね。
アドバイス頂いたコードの内容は私には少々難しいようです。
xls88様にアドバイス頂いた区切り位置指定の設定をリセットする方法を毎回実行する方が何かトラブルがあった時に対処しやすいように思います。
Wendy02様にアドバイス頂いたコードは後ほど勉強させて頂きます。
ありがとうございました。
No.2ベストアンサー
- 回答日時:
(2)について
「列のデータ形式」に該当するオプションを、すべて外して「区切り位置」を「完了」してください。
マクロだと、「列のデータ形式」に該当するパラメータを、すべてFalseにして「区切り位置」マクロを実行します。
>With CB
> .GetFromClipboard
> .GetText
>End With
>Worksheets("test").Paste Destination:=Worksheets("test").Range("H1")
>
>この方法が行けないのでしょうか。
関係はないと思います。
余談ですが
たしかExcel200からの機能だと思いますが、Split関数を使えば区切り文字があるデータを簡単に分割取得できます。
xls88様、回答ありがとうございます。
仰る通りにしたところ、うまく区切ることができました。
再現性も問題ありません。
Split関数の件ですが、確かに存在するようです。
しかし、私の知識ではxls88様のアドバイスが簡単に理解でき、何かトラブルが起こっても対処できるように思います。
ありがとうございました。
No.1
- 回答日時:
まず、(1)について
TextToColumnsメソッドのFieldInfoパラメータの設定を変えればいいようです。
TextToColumnsメソッドのヘルプを調べればわかると思います。
あるいは、「区切り位置」作業を「マクロの記録」しても良いと思います。
ウイザードの3/3で、「列のデータ形式」で、1列目を「日付」の「YMD」に設定して「完了」としてください。
列は、「データのプレビュー」でデフォルトで「G/標準」となっている見出し部分をクリックして選択できます。
デフォルトで、1列目が選択(黒く反転表示)されています。
この回答への補足
xls88様、回答ありがとうございます。
早速試してみました。
マクロ記録したところ、日付に当たるArray(1,1)がArray(1,5)となりました。
実行するとうまくいきました。
しかし、貼り付けたデータをClearメソッドで消去して、別のデータを
Worksheets("test").Paste Destination:=Worksheets("test").Range("H1")
にて貼り付けるとArray(1,5)に変更した設定が機能しておらず、日付が2028/8/8となります。DMYになっています。
貼り付けたデータは自動で6データに区切られます。
ところがコードを使わずに手動(Ctrl+Vや右クリックの貼り付けなど)で貼り付けると2008/8/28となります。
この場合ももちろん自動でデータが区切られます。
全く、訳が分かりません。
Webサイトから取得するデータは単にテキスト選択をしてクリップボードにコピーしたものなので、DataObjectオブジェクトを使ってクリップボードからデータを取り出して貼り付けています。
With CB
.GetFromClipboard
.GetText
End With
Worksheets("test").Paste Destination:=Worksheets("test").Range("H1")
この方法が行けないのでしょうか。
アドバイス、よろしくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセル VBA 処理スピードを上げたいのですが。 6 2023/03/31 20:52
- Visual Basic(VBA) VBAで日付入力しているのですが 4 2023/03/02 11:25
- その他(Microsoft Office) マクロVBAについて 1 2022/09/06 18:12
- Excel(エクセル) エクセルのマクロについて教えてください。 1 2023/02/06 13:01
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Excel(エクセル) エクセルのマクロについて教えてください。 2 2023/02/04 12:47
- Excel(エクセル) エクセルに詳しい方 よく読んでからのご回答お願いします 外部からデータが来ますが、日付が202201 7 2022/06/29 16:15
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
教えて下さい
-
多量のSUMIF式を軽くしたい
-
配列でデータが入っている要素...
-
モジュラス103の算出方法について
-
VBA 空白セルを削除ではない方...
-
この行は既に別のテーブルに属...
-
メモ帳(テキストデータ)をExc...
-
ユーザーフォームのテキストボ...
-
アクセス2000で画像データ...
-
GridViewの行の一部を複数行にする
-
VBA 判定文で理解できない結果...
-
Excelのマクロでワードのテキス...
-
エクセルVBAでもっと早く転記
-
VBAで「"」[']をエスケープする...
-
ページ数を求めたい
-
[VBA] CountIfに変数を使いたい
-
C言語について
-
VBAを使ってOutlookメール本文...
-
ブレーカー落ちで壊れたりしな...
-
S9タイプからXタイプにデータ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
教えて下さい
-
【エクセル】測定時間がバラバ...
-
配列でデータが入っている要素...
-
多量のSUMIF式を軽くしたい
-
エクセルで2つの時系列のデー...
-
Excelのマクロでワードのテキス...
-
メモ帳(テキストデータ)をExc...
-
VBA 空白セルを削除ではない方...
-
VBAを使ってOutlookメール本文...
-
Accessで該当データにフラグを...
-
合計3TBのデータのハッシュ値を...
-
シーケンサにパソコンからアク...
-
S9タイプからXタイプにデータ...
-
カンマからスラッシュに
-
VBAでシートからコンボボックス...
-
VBA 毎日取得するデータを順番...
-
ビットシフトについて
-
ユーザーフォームのテキストボ...
-
EXCELVBAでSQLserverからデータ...
-
特定のデータの抽出方法を教え...
おすすめ情報