By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,458 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

Date formats when exporting to excel via VB

P: n/a
I export a recordset to excel via VB code (see code below). My problem
is the date fields import as an integer into excel (11/14/05 becomes
38670). How do I go about addressing this?

Public Function Export2XL(SqlQuery As String) As Long

Dim iCol As Integer, iRow As Integer, fldCount As Integer, recCount
As Integer, rs As New ADODB.Recordset
Dim x_APP As Excel.Application, x_wbk As Excel.Workbook, x_wsh As
Excel.Worksheet
rs.Open SqlQuery, CurrentProject.Connection, adOpenKeyset
Set x_APP = New Excel.Application
Set x_wbk = x_APP.Workbooks.Add
Set x_wsh = x_wbk.Worksheets.Add

fldCount = rs.Fields.Count
For iCol = 1 To fldCount
x_wsh.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
x_wsh.Select
x_wsh.Cells(2, 1).CopyFromRecordset rs
x_APP.Visible = True
x_APP.UserControl = True
Set x_wsh = Nothing
Set x_wbk = Nothing
Set x_APP = Nothing
If rs.STATE = 1 Then
rs.Close
End If

End Function

Nov 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
wy*****@gmail.com wrote:
I export a recordset to excel via VB code (see code below). My problem
is the date fields import as an integer into excel (11/14/05 becomes
38670).
Excel stores dates internally as a number specifying (i think) number of
seconds since a known date (1900-JAN-01, I think). Anyway, this is
fairly easily handled.
How do I go about addressing this?
In the Excel worksheet, highlight the column(s) that contain the dates
that are incorrectly showing up as numbers. On the menu, Format|Cells
choose "Number" tab. Under category, highlight "Date" and then select
which format you want to use from the "Type" listbox.

Alternately, to do this in code (using the objects from your code):

x_wsh.Columns("P:P").Select
x_App.Selection.NumberFormat = "m/d/yy h:mm;@"
Public Function Export2XL(SqlQuery As String) As Long

Dim iCol As Integer, iRow As Integer, fldCount As Integer, recCount
As Integer, rs As New ADODB.Recordset
Dim x_APP As Excel.Application, x_wbk As Excel.Workbook, x_wsh As
Excel.Worksheet
rs.Open SqlQuery, CurrentProject.Connection, adOpenKeyset
Set x_APP = New Excel.Application
Set x_wbk = x_APP.Workbooks.Add
Set x_wsh = x_wbk.Worksheets.Add

fldCount = rs.Fields.Count
For iCol = 1 To fldCount
x_wsh.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
x_wsh.Select
x_wsh.Cells(2, 1).CopyFromRecordset rs
x_APP.Visible = True
x_APP.UserControl = True
Set x_wsh = Nothing
Set x_wbk = Nothing
Set x_APP = Nothing
If rs.STATE = 1 Then
rs.Close
End If

End Function

Nov 17 '05 #2

P: n/a
Thanks! I used the code you gave me (with adjustent to translate
column number to column letter). I have just one more problem. This
bit of code is used by both US and Japan, so how do I set it to local
date settings ("Short date") as opposed the "m/d/yy" or 'd/m/yy"?

Nov 17 '05 #3

P: n/a
wy*****@gmail.com wrote:
Thanks! I used the code you gave me (with adjustent to translate
column number to column letter). I have just one more problem. This
bit of code is used by both US and Japan, so how do I set it to local
date settings ("Short date") as opposed the "m/d/yy" or 'd/m/yy"?


http://groups.google.com/group/micro...e9214be293b9ae

Glad I could help. As far as I could discover (see the Google link),
"m/d/yy" is the format that tells Excel to pick up the local settings.
If that's not working for you, you could do the following to find out
what the Japanese NumberFormat should be:

1. In a blank Excel worksheet, enter a column of 3 or 4 dates.
2. Highlight the column.
3. Select from the menu Format|Cells
4. On the Number tab, choose "Date" from the Category listbox.
5. Under the Type listbox there is a "Locale" combobox. Set the format
to the Type you desire and select "Japanese" from the combobox.
6. Right a short Public Sub that echoes the NumberFormat for the column
with the dates in it.

You can then use the format that is echoed in the code that sets
x_App.Selection.NumberFormat. Good luck figuring it out.

Nov 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.