EXCEL2000 区切り位置指定ウィザードについて

質問

いつもお世話になっております。

以下のようなデータをWebサイトからコピーします。ここでは１行分のデータのみ示していますが、通常は１００行以上のデータを扱います。
08/08/28 09:00 94.38 94.54 94.38 94.49
日付、時間、データ１，２，３，４です。
各データはスペースで区切られています。

このデータをシートのあるセルに貼り付けると、一つのセルに上記のデータがすべて入力されます。
それぞれを分割したいので、区切り位置指定ウィザードで区切ります。
すると、それぞれのデータが６列に分割されます。
分割後、先頭の日付は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))
質問１．なぜVBAでやると日付がおかしくなるのでしょうか。

この問題を解決するために元のデータを貼り付けた後にデータの先頭に20を付けて2008/08/28にしたいのですが、一度区切り位置指定ウィザードを使うと次回から貼り付けたデータは前回の設定で自動で区切られてしまい、データの先頭に20を付けたら日付が202028/8/8となってしまいます。
質問２．区切り位置指定ウィザードの設定をVBAでリセットすることはできないでしょうか。

うまく説明できませんが、本当に困っています。
よろしくお願い致します。

回答No.2

xls88 · ベストアンサー

(2)について
「列のデータ形式」に該当するオプションを、すべて外して「区切り位置」を「完了」してください。
マクロだと、「列のデータ形式」に該当するパラメータを、すべてFalseにして「区切り位置」マクロを実行します。

>With CB
>　.GetFromClipboard
>　.GetText
>End With
>Worksheets("test").Paste Destination:=Worksheets("test").Range("H1")
>
>この方法が行けないのでしょうか。
関係はないと思います。

余談ですが
たしかExcel200からの機能だと思いますが、Split関数を使えば区切り文字があるデータを簡単に分割取得できます。

お礼

xls88様、回答ありがとうございます。

仰る通りにしたところ、うまく区切ることができました。
再現性も問題ありません。

Split関数の件ですが、確かに存在するようです。
しかし、私の知識ではxls88様のアドバイスが簡単に理解でき、何かトラブルが起こっても対処できるように思います。

ありがとうございました。

回答No.3

Wendy02

こんにちは。

>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

>質問２．区切り位置指定ウィザードの設定をVBAでリセットすることはできないでしょうか。

それは、もう一度、ダミーをインポートすれば直りますが、そのような必要性はないはずです。正しいプロパティ値にすればよいだけのことですから。

お礼

Wendy02様、回答ありがとうございます。

2028/8/8となる理由は理解できました。
アメリカが基準なんですね。

アドバイス頂いたコードの内容は私には少々難しいようです。
xls88様にアドバイス頂いた区切り位置指定の設定をリセットする方法を毎回実行する方が何かトラブルがあった時に対処しやすいように思います。

Wendy02様にアドバイス頂いたコードは後ほど勉強させて頂きます。

ありがとうございました。

回答No.1

xls88

まず、(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になっています。
貼り付けたデータは自動で６データに区切られます。
ところがコードを使わずに手動（Ctrl+Vや右クリックの貼り付けなど）で貼り付けると2008/8/28となります。
この場合ももちろん自動でデータが区切られます。

全く、訳が分かりません。
Webサイトから取得するデータは単にテキスト選択をしてクリップボードにコピーしたものなので、DataObjectオブジェクトを使ってクリップボードからデータを取り出して貼り付けています。

With CB
　.GetFromClipboard
　.GetText
End With
Worksheets("test").Paste Destination:=Worksheets("test").Range("H1")

この方法が行けないのでしょうか。

アドバイス、よろしくお願い致します。