从Access导入Excel时日期格式混乱问题求助

Hey there, let's break down exactly why you're seeing those mixed-up dates and inconsistent data types, plus how to fix it for good.

What's Causing the Problem? #

Your issue boils down to two key things:

1. Regional Mismatch: Access and Excel are interpreting date formats differently. Since your dates follow French-style dd/mm/yyyy, but Excel might default to a regional setting like US English (mm/dd/yyyy). When the day value is ≤12 (e.g., 02/10/2017), Excel incorrectly reads it as month/day instead of day/month. For days >12 (29/09/2017), Excel can't treat 29 as a valid month, so it falls back to storing the value as a string instead of a proper date.
2. Automatic Type Guessing: Using GetRows + Transpose to bulk-paste data forces Excel to "guess" each cell's data type. This leads to the inconsistent behavior you're seeing—some dates become strings, others get their day/month flipped.

Step-by-Step Fixes #

Option 1: Format Dates in the SQL Query (Quick Win) #

Modify your Access SQL to output dates in an ISO standard format (yyyy-mm-dd). Excel will always recognize this format correctly, no matter the regional settings.

Update your SQL string to include a formatted date field:

' Replace "YourDateField" with the actual name of your date column in Histo
str_req = "SELECT " & param_champs & ", Format(a.YourDateField, 'yyyy-mm-dd') AS FormattedDate FROM Histo a, Referential b WHERE a.productID = b.ID AND a.Isin IN " & sicoList

Then, after importing the data into Excel, convert the formatted string column to a proper date format:

Dim ws As Worksheet
Set ws = Worksheets("test")

' Assume FormattedDate is in column colref + 3 (adjust based on your param_champs)
With ws.Columns(colref + 3)
    .NumberFormat = "dd/mm/yyyy" ' Set your desired date display format
    .Value = .Value ' Convert the string to an actual date value
End With

Option 2: Write Records Line-by-Line (Most Reliable) #

Avoid bulk-pasting with GetRows entirely. Instead, loop through the ADODB recordset and write each row directly to Excel, pre-setting the cell format to ensure dates are recognized correctly. This eliminates Excel's automatic type guessing.

Replace your bulk assignment code with this:

Dim ws As Worksheet
Dim rowNum As Integer
Set ws = Worksheets("test")
rowNum = 2 ' Start at row 2 as you did before

' First, set the target column's format to your desired date style (dd/mm/yyyy)
' Replace colref + 1 with the actual column index where your date will go
ws.Columns(colref + 1).NumberFormat = "dd/mm/yyyy"

' Loop through the recordset
Do While Not recordset.EOF
    ' Write the date field directly (Excel will honor the pre-set format)
    ws.Cells(rowNum, colref + 1).Value = recordset.Fields("YourDateField").Value
    
    ' Write your other fields here (match them to your param_champs)
    ' Example: ws.Cells(rowNum, colref).Value = recordset.Fields("productID").Value
    
    recordset.MoveNext
    rowNum = rowNum + 1
Loop

This method guarantees all dates are stored as proper date values (not strings) and displayed correctly, regardless of regional settings.

内容的提问来源于stack exchange，提问作者mehdi