複数シートへのCopyFromRecordsetによる貼り付け時の日時の書式設定について

実現したいこと

複数シートへのCopyFromRecordsetによる貼り付け時の日時の書式設定をyyyy/mm/dd hh:mm:ssの形式にしたい。

発生している問題・分からないこと

最終ページはyyyy/mm/dd hh:mm:ssの書式に出来るが、途中のページはyyyy/mm/ddの書式になってしまう。

該当のソースコード

VBA
1Sub testcode()
2
3    Dim tSheet As Worksheet
4    Dim tRs As Object
5    Set tRs = CreateObject("ADODB.Recordset")
6    tRs.Fields.Append "aa", 135 ' 135:adDBTimeStamp
7    tRs.Open
8    tRs.AddNew
9    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
10    tRs.Update
11    tRs.AddNew
12    tRs.Fields("aa").Value = #5/5/2025 11:12:21 AM#
13    tRs.Update
14    tRs.MoveFirst
15    Set tSheet = ThisWorkbook.Sheets.Add
16    tSheet.Cells(1, 1).Value = "aa"
17    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
18    tSheet.Columns(1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss;@"
19    tRs.MoveFirst
20    Set tSheet = ThisWorkbook.Sheets.Add
21    tSheet.Cells(1, 1).Value = "aa"
22    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
23    tSheet.Columns(1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss;@"
24    tRs.Close
25    
26End Sub

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

copilotに聞いても解決しませんでした。

補足

OfficeのバージョンはMicrosoft365です。

上述の方法が上手くいかなかったので、今は以下のコードにしようかなと思っています。

VBA
1 Sub testcode2()
2 
3   
4    Dim tSheet As Worksheet
5    Dim tRs As Object
6    Set tRs = CreateObject("ADODB.Recordset")
7
8    tRs.Fields.Append "aa", 135 ' 135:adDBTimeStamp
9    tRs.Open
10    tRs.AddNew
11    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
12    tRs.Update
13    tRs.AddNew
14    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
15    tRs.Update
16    
17    Set tSheet = ThisWorkbook.Sheets.Add
18    Dim tQt As QueryTable
19    Set tQt = tSheet.QueryTables.Add(Connection:=tRs, Destination:=tSheet.Cells(1, 1))
20    tQt.Refresh
21    tQt.Delete
22    tRs.Close
23    tSheet.Columns(1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss;@"
24    
25    
26    tRs.Fields.Append "aa", 135 ' 135:adDBTimeStamp
27    tRs.Open
28    tRs.AddNew
29    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
30    tRs.Update
31    tRs.AddNew
32    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
33    tRs.Update
34    
35    Set tSheet = ThisWorkbook.Sheets.Add
36    Set tQt = tSheet.QueryTables.Add(Connection:=tRs, Destination:=tSheet.Cells(1, 1))
37    tQt.Refresh
38    tQt.Delete
39    tSheet.Columns(1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss;@"
40
41End Sub
42

こっちで出来そうだからいいんですが
初めの方のやり方も解決したいなという事での質問です。

また、実際にはSQLSERVERへ接続して結果のRecordSetをシートに貼り付けるという内容です。
問題となる事象が再現しているので、提示のコードでの質問となっています。

--追記--
解決しましたが、以下のような場合でも１シート目を日付型にしてしまうことがわかったので
蛇足ですが追記しておきます。

VBA
1Sub testcode()
2
3    Dim tSheet As Worksheet
4    Dim tRs As Object
5    
6    Set tRs = CreateObject("ADODB.Recordset")
7    tRs.Fields.Append "aa", 5 '5:adDouble
8    tRs.Open
9    tRs.AddNew
10    tRs.Fields("aa").Value = 1.11
11    tRs.Update
12    tRs.AddNew
13    tRs.Fields("aa").Value = 2.25
14    tRs.Update
15    tRs.AddNew
16    tRs.Fields("aa").Value = 3.25
17    tRs.Update
18    tRs.MoveFirst
19    Set tSheet = ThisWorkbook.Sheets.Add
20    tSheet.Cells(1, 1).Value = "aa"
21    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
22    tRs.Close
23    
24    Set tRs = CreateObject("ADODB.Recordset")
25    tRs.Fields.Append "aa", 135 ' 135:adDBTimeStamp
26    tRs.Open
27    tRs.AddNew
28    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
29    tRs.Update
30    tRs.AddNew
31    tRs.Fields("aa").Value = #5/5/2025 11:12:21 AM#
32    tRs.Update
33    tRs.MoveFirst
34    
35    Set tSheet = ThisWorkbook.Sheets.Add
36    tSheet.Cells(1, 1).Value = "aa"
37    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
38    tRs.Close
39
40End Sub

日付型でないデータであっても２シート目の出力の日付型の所を1シート目で書式変更してしまうようです。

行動規範の内容に同意します

回答2件

ベストアンサー

最終ページはyyyy/mm/dd hh:mm:ssの書式に出来るが、
途中のページはyyyy/mm/ddの書式になってしまう。

vba
1    tRs.MoveFirst
2    Set tSheet = ThisWorkbook.Sheets.Add
3    tSheet.Cells(1, 1).Value = "aa"
4    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
5    tSheet.Columns(1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss;@"

恐らく CopyFromRecordset メソッドの呼び出し時における一種の不具合なのではないかと。

こちらの環境でいろいろテストしてみた限り、次のような結果となりました。

1回目の Sheets.Add ～ CopyFromRecordset までの処理を一気に実行すると、直前にアクティブだった既存のワークシートの A2:A3 の表示形式がyyyy/m/dに変更される。A4 以降のセルは影響を受けない。
2回目の Sheets.Add ～ CopyFromRecordset までの処理を一気に実行すると、1 回目の処理によって挿入された（と同時にアクティブになっていた）ワークシートの A2:A3 の表示形式がyyyy/m/dに変更される。
フィールド[aa]のデータ型が日付/時刻データを扱うための型である場合のみ発生する。数値データや文字列データを扱う型である場合は発生しない。
それぞれの Sheets.Add メソッドの直後に Stop ステートメントを呼び出すなどしてコードの実行を一時停止させ、そのままコードの実行を再開して（つまり一旦間を置いてから） CopyFromRecordset メソッドが呼び出された場合は発生しない。
「新規シートの挿入」と「セル範囲の表示形式の設定」のみ実行した（ CopyFromRecordset メソッドを呼び出さない）場合では発生しない。
「新規シートの挿入」と「 CopyFromRecordset メソッドの呼び出し」のみを一気に実行した（「セル範囲の表示形式の設定」を実行しない）場合、「直前にアクティブだったシート」側の A2:A3 の表示形式がyyyy/m/dとなり、新規シート（本来の貼り付け先）側の A2:A3 の表示形式は「標準」のままとなる。しかし「新規シートの挿入」の直後にコードの実行を一時停止し（一旦間を置き）、コードの実行を再開して CopyFromRecordset メソッドを呼び出した場合は、新規シート側の A2:A3 （本来のコピー先）の表示形式がyyyy/m/dになり、「直前にアクティブだったシート」側の A2:A3 の表示形式は元のまま変更されない。
CopyFromRecordset メソッドによるレコードセットの出力先を「 Sheets.Add メソッドによって作成されたシート」ではなく「アクティブではない既存のシート」にした場合、CopyFromRecordset メソッドを呼び出す前にそのシートをアクティブにしない限り、本来のシートではなく「その時点でのアクティブシート」側の A2:A3 の表示形式がyyyy/m/dに変更される。
アクティブシートが別のブックのシートだった場合も上記と同様の結果となる。

以上のことから、恐らく次のような誤った処理が行われているものと推定されます。

Sheets.Add メソッドの呼び出しに際し、「空のワークシートの作成」と「作成したシートのアクティブ化」という2つのプロセスが発生する。この時、前者のプロセスと後者のプロセスの間（極めて短い時間）に「新規ワークシートが非アクティブ化されている状態」が生じることになる（また、Add メソッドそのものは非同期で実行されている可能性が高い）。
CopyFromRecordset メソッドが呼び出されると「レコードセット全体のデータを指定範囲に出力する処理」を実行してから「日付/時刻型のフィールドの出力範囲の表示形式をyyyy/m/dに初期化する処理」を行おうとする。ところが、後者の処理の実際の対象が「その時点でのアクティブシート」となっている。
したがって、「新規ワークシートが非アクティブ化されている状態」から「新規ワークシートがアクティブ化されている状態」に移行し終えるまでのごく短い時間に CopyFromRecordset メソッドが呼び出されていることにより、件の現象が発生しているものと考えられる。

回避策としては、 CopyFromRecordset メソッドが呼び出される前に
出力先となるブックとワークシートを明示的にアクティブにすることではないかと思います。

vba
1Set tSheet = ThisWorkbook.Sheets.Add
2tSheet.Parent.Activate
3tSheet.Select

投稿2025/03/21 02:40

編集2025/03/21 02:47

sk.exe

総合スコア1034

xail2222

2025/03/21 21:25 編集

調査ありがとうございます。 tSheet.Select を入れることで問題は解消しました。ただ、「ごく短い時間」というのが気になって DoEventsをループで回して１分待つようにしてみましたがこれでは再現し、改善はしませんでした。 Excel.Application.OnTimeを使って２秒でも待って処理すると再現せず改善しました（実際にはこんなコード採用するわけがないのですが）またF5での実行（一気に実行）だと再現しますが F8おしっぱだと再現しませんでした。 doeventsを入れて改善しない理由がいまいちわからないですが doeventsだけでは、行われない何らかの内部処理があるという事になるのでしょうか。あと Debug.Print ThisWorkbook.ActiveSheet.Name を「Set tSheet = ThisWorkbook.Sheets.Add」のすぐ後に書いたらこれは、ちゃんと新しいシート名が取れました。内部的な何かがおかしいのかもしれないですね。書式を指定しなかったら、日付型の所が数値のままになって２シート繰り返すと、１シート目の書式が変わるとか・・・なんかミスってるんじゃない？本来あるべき動作は、CopyFromRecordset で日付のデータのフォーマットはyyyy/m/dに変える事なんじゃないか、とか感じてしまいます。

sk.exe

2025/03/24 06:39

> ただ、「ごく短い時間」というのが気になって > DoEventsをループで回して１分待つようにしてみましたが > これでは再現し、改善はしませんでした。 > > Excel.Application.OnTimeを使って２秒でも待って処理すると > 再現せず改善しました前者「コードの実行そのものは中断されていない」、後者「（間接的に）コードの実行が一時中断された」という違いによるものかも知れません。 > Debug.Print ThisWorkbook.ActiveSheet.Name > を「Set tSheet = ThisWorkbook.Sheets.Add」のすぐ後に書いたら > これは、ちゃんと新しいシート名が取れました。だからこそ CopyFromRecordset の挙動が明らかに異常である、と言えます。 > 本来あるべき動作は、CopyFromRecordset で日付のデータのフォーマットは > yyyy/m/dに変える事なんじゃないかまさにそれが「本来あるべき動作」です。実際に（少なくとも私の環境では）、 Access のモジュールから「起動中の Excel アプリケーションを操作し、その時点で開かれているブックの 1 つに新規ワークシートを挿入し、CopyFromRecordset を呼び出して Access データベース上のテーブルの全てのレコードを出力する」というコードを実行してみると、日付/時刻型のフィールドのデータが出力された範囲の表示形式は正しく`yyyy/m/d`形式となります。（その時点においてどのブック/ワークシートがアクティブであろうとも）ところが、Excel のモジュールから同様のコードを実行してみると、回答で示した通り（出力先シートを明示的にアクティブ化しない限りは）「データの出力先シート」と「表示形式の変更対象シート」がずれるという結果となります。現時点では「 Excel のモジュールから CopyFromRecordset メソッドを呼び出した場合に発生し得る不具合」と評価せざるを得ませんね。

行動規範の内容に同意します

当方の環境で試してみて現象が再現できました。

'Debug.Print tSheet.Columns(1).NumberFormatLocal'
というデバッグコードをいろいろ挿入して確認してみたところ、CopyFromRecordsetを実行した直後に'NumberFormatLocal'がNullになってました。不思議な現象ですね。

スマートではないですが'NumberFormatLocal'の設定を一番最後にまとめてやることで解消できるようです。

vba
1Sub testcode()
2
3    Dim tSheet As Worksheet
4    Dim tSheets As New Collection
5    Dim tRs As Object
6    Set tRs = CreateObject("ADODB.Recordset")
7    tRs.Fields.Append "aa", 135 ' 135:adDBTimeStamp
8    tRs.Open
9    tRs.AddNew
10    tRs.Fields("aa").Value = #5/5/2025 3:14:21 PM#
11    tRs.Update
12    tRs.AddNew
13    tRs.Fields("aa").Value = #5/5/2025 11:12:21 AM#
14    tRs.Update
15    tRs.MoveFirst
16    
17    Set tSheet = ThisWorkbook.Sheets.Add
18    tSheets.Add tSheet
19    tSheet.Cells(1, 1).Value = "aa"
20    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
21    
22    tRs.MoveFirst
23    Set tSheet = ThisWorkbook.Sheets.Add
24    tSheets.Add tSheet
25    tSheet.Cells(1, 1).Value = "aa"
26    Call tSheet.Cells(2, 1).CopyFromRecordset(tRs)
27    tRs.Close
28    
29    For Each tSheet In tSheets
30        tSheet.Columns(1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss;@"
31    Next
32End Sub

投稿2025/03/19 23:54

hatena19

総合スコア34292

sk.exe

2025/03/21 03:49 編集

> CopyFromRecordsetを実行した直後に'NumberFormatLocal'がNullになってました。 2 つ以上のセルを参照する Range オブジェクトにおいて、全てのセルの表示形式が同一でなければ（表示形式が異なるセルが混在していれば）、その Range オブジェクトの NumberFormat プロパティおよび NumberFormatLocal プロパティは Null を返すのが仕様です。 > 'Debug.Print tSheet.Columns(1).NumberFormatLocal' A2:A3 の表示形式が「日付」（ yyyy/m/d ）で、それ以外のセルの表示形式が「標準」であるならば、「A 列全体を参照する Range オブジェクト」の NumberFormatLocal プロパティの値は必ず Null となります。

xail2222

2025/03/21 14:01 編集

回答ありがとうございます。不思議な現象ですよね。確かに、最後にフォーマット設定するのが手間でなければよいですね。あと 'Debug.Print tSheet.Columns(1).NumberFormatLocal' についてですが Debug.Print ThisWorkbook.Worksheets("1枚目のシート名").Cells(1, 1).NumberFormatLocal Debug.Print ThisWorkbook.Worksheets("1枚目のシート名").Cells(2, 1).NumberFormatLocal Debug.Print ThisWorkbook.Worksheets("1枚目のシート名").Cells(3, 1).NumberFormatLocal Debug.Print ThisWorkbook.Worksheets("1枚目のシート名").Cells(4, 1).NumberFormatLocal で、ログを確認した所書式を設定した直後は、ちゃんと入ってて CopyFromRecordset の後で、データ部分の所だけ上書きされてしまうようですね。

行動規範の内容に同意します

あなたの回答