Date field not in date format

[Lorlai]

I have an excel spreadsheet that is being imported into access. I have many date fields included in this spreadsheet. For some reason, there are a few fields that do not show up properly in access. For example, I have a date in the excel file (08/25/2008) that shows up in access as 39685. Why is this happening?

When I create some vba code to change the date format in the excel, it works out just fine. My next problem is that I have had to hard code these specific fields in, and while the code works great for the first field, when the second field appears, the entire sheet is transferred into date format.

If ActiveCell.Value = "Project First Date" Then
            ActiveCell.Value = Replace(ActiveCell.Value, " ", "_")
            ActiveCell.EntireColumn.Select
            Selection.NumberFormat = "m/d/yyyy"
            ActiveCell.Offset(0, 1).Select
                End If
        If ActiveCell.Value = "Project Finish Date" Then
            ActiveCell.Value = Replace(ActiveCell.Value, " ", "_")
            ActiveCell.EntireColumn.Select
            Selection.NumberFormat = "m/d/yyyy"
            ActiveCell.Offset(0, 1).Select
                End If
        If ActiveCell.Value = "Project Forecast Finish Date" Then
             ActiveCell.Value = Replace(ActiveCell.Value, " ", "_")
             ActiveCell.EntireColumn.Select
             Selection.NumberFormat = "m/d/yyyy"
             ActiveCell.Offset(0, 1).Select
        End If

Any thoughts on this would be appreciated!

[boblarson]

Try changing this:

Selection.NumberFormat = "m/d/yyyy"

to this

Selection.NumberFormat = "m/d/yyyy;@"

[boblarson]

Oh, and by the way, the 39685 is the serial date of the date you showed.

[Lorlai]

Thanks for your response Bob. Unfortunately, when I hit the second if statement, the entire sheet still turns to the date format.

Any other ideas into why this might be happening? Or is there another way I go about to prevent this?

Thank you!

[boblarson]

Is this code in Access or Excel?

[Lorlai]

Running it through access, opening up an excel file and using this code to format excel before bringing the data into a spreadsheet.

[ssanfu]

PMFJI, instead of running the code to modify the formats in Excel, have you tried setting up an import specification in Access? It cut down the number of errors I had importing Excel and CSV files.

[Lorlai]

No, I hadn't thought of that. My current import process is pretty generic, in case there are new excel files that i hadn't seen. How might I go about incorporating an import specification for dates?

My import code is below:

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public Function CreateAccess()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object

OpenFile.lStructSize = Len(OpenFile)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "E:\"
OpenFile.lpstrTitle = "Choose a File"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile

With oApp
.Visible = False
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, "Project_Data", OpenFile.lpstrFile, True

End With
oApp.DisplayAlerts = False
oApp.Workbooks.Close
oApp.DisplayAlerts = True
oApp.Quit
Set oApp = Nothing
End Function

Thank you!

[ssanfu]

Now that I stuck my foot in my mouth, I wish I had some mustard and salt.

Import specs are not available when importing spreadsheets. I had problems (a few years ago) with importing spreadsheets, so I ended up, from Excel, exporting the data as a CSV file. Then I imported the CSV file using the import specs. I tried opening the spreadsheet, then looping through the columns and rows, but always seemed to have issues; the CSV file import seems to have less issues.

When trying to import the spreadsheet directly, I found this site:

Ken's Examples for Importing from EXCEL Workbook Files

at_____ http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

There are a lot of examples and code....

Again, sorry, I seem to suffer from CRS more and more......

[Lorlai]

Thanks anyway ssanfu, your emoticons and time are appreciated

[ssanfu]

Just found this mdb...... Maybe the code will help..

[boblarson]

The reason I asked about whether it was in Access or Excel, is that if it is in Access you really need to use instantiated objects for this code and not using code that is not tied to an instantiated object. So, like using

ActiveCell.Offset(0,1).Select

is not good. It should be tied to an instantiated object. So, like:

Dim oAppAs Object
 
Set oApp = CreateObject("Excel.Application")
oApp.ActiveCell.Offset(0,1).Select

Read my quick tutorial about this (it was also published by the Access Team on their Access Blog in September of 2010):
http://www.btabdevelopment.com/ts/excelinstance