Member Avatar for tgifgemini

Good morning everyone - I am using VB6.
I need your assistance. I used the "CopyFromRecordset" function to populate my spreadsheet. Some of the fields in the access file has date values and some has number values (integers), but after importing into the spreadsheet, the numbers convert to date values(1/1/1900) and the date fields displays number values in the columns that was supposed to display dates.

I have used the code below to try and fix the problem and the numbers displays correctly, but the date values are still displaying numbers (integers).
My code:

Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit

<==This works fine.

Columns("L:L").Select
    xlApp.Selection.NumberFormat = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit

<==This does not work.
Thanks.
tgifgemini.

  • 3 Contributors
  • 6 Replies
  • 132 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by tgifgemini

Dani AI

Generated

Mixed date/number display after automation usually means Excel is receiving values whose types don’t match what Excel expects. reported exactly that and the thread shows two quick fixes: export dates as text at the source (as suggested) or apply a sheet-level date format (as suggested). Below are practical, reliable alternatives and checks that avoid intermittent parsing problems and work well when running the same code on different machines or locales.

First, confirm the source field type and, when needed, coerce the data to a true VB Date before handing it to Excel. Pulling the recordset into a 2‑D Variant array, converting the date column with CDate, then writing the array to the sheet in a single operation is both fast and keeps types consistent:

Dim data As Variant
data = rst.GetRows()
For i = LBound(data, 2) To UBound(data, 2)
  If Not IsNull(data(DateColIdx, i)) Then
    data(DateColIdx, i) = CDate(data(DateColIdx, i))
  End If
Next
' transpose/resize and assign to the sheet in one call

If type coercion in VB is not desirable, have Access return an unambiguous text date (ISO) so Excel parses it the same on any machine. Example SQL pattern:

strSQL = "SELECT ID, FORMAT(MyDateField, 'yyyy-mm-dd') AS DateText FROM MyTable"

Other useful tactics: assign numeric serials directly using Excel’s Range.Value2, or run Excel’s TextToColumns on the imported column to force conversion from text to date. Which of these to use depends on whether the imported values are currently text, COM Date variants, or plain numbers. See the Microsoft docs for Range.Value2 and Range.TextToColumns for details, and check ADO field types before conversion to decide whether to CDate, format in SQL, or use Excel-side conversion.

Recommended Answers

All 6 Replies

Member Avatar for AV Manoharan

Good morning everyone - I am using VB6.
I need your assistance. I used the "CopyFromRecordset" function to populate my spreadsheet. Some of the fields in the access file has date values and some has number values (integers), but after importing into the spreadsheet, the numbers convert to date values(1/1/1900) and the date fields displays number values in the columns that was supposed to display dates.

I have used the code below to try and fix the problem and the numbers displays correctly, but the date values are still displaying numbers (integers).
My code:

Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit

<==This works fine.

Columns("L:L").Select
   [B] xlApp.Selection.NumberFormat[/B] = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit

<==This does not work.
Thanks.
tgifgemini.

I have bold faced your problem. The methode of the selection object is wrong. In the earlier case it is correct as it returns the number, where as in the second case it has to be dateformat

AV Manoharan

Member Avatar for tgifgemini

Thanks for your input. I will re-run my module with "DateFormat" and get back to you.
Have a wonderful day.
tgifgemini

Member Avatar for tgifgemini

Good morning.

I tried using "DateFormat" in the code below, but I got error msg:
"Object does not support this property".

First, I tried this code:

xlApp.xlWbk.Sheets("Sheet1").Columns("H:H").DateFormat = "mm/dd/yyyy"

And I also tried this code:

xlWksht.Range("G:G,H:H,K:K,L:L").Select
Selection.DateFormat = "mm/dd/yyyy"

In all the codes above, I used both "DateFormat" and "NumberFormat", but the date fields in access file still returns numbers in my spreadsheet.
tgifgemini

Member Avatar for AV Manoharan

I have answered to you as below:

I have bold faced your problem. The methode of the selection object is wrong. In the earlier case it is correct as it returns the number, where as in the second case it has to be dateformat

AV Manoharan

Now excel has a uniqe way of importing everything into it as GENERAL format. But a date format of excel is little tricky. What the number that represent a date in Acceess need not be the number that represent a date in excell. So the best way is while drawing the date field from the ACCESS you dfraw it as a Characte Date NOT as a Number form of date. Then I think it has to work.

AV Manoharan

Member Avatar for QVeen72

Hi tgif,

use this Code:

xlApp.Range("L:L").NumberFormat = "mm/dd/yyyy"


Regards
Veena

Member Avatar for tgifgemini

Thanks a million. After much tweaking, your code worked.
Have a great day.
tgifgemini.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.