I found a solution to my own problem.
In excel if you "save as" .CVS the text is saved as text buy the data is not
transfer complete eg:
in xls
123456789
in cvs
123456
So I use a vba code found on
http://www.mvps.org/access/. It works great!!
Function getdata()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Set objXL = New Excel.Application
strFile = "D:\Nicole\EOQ_SS.xls"
With objXL
' .Visible = True
.Workbooks.OpenText Filename:=strFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
2), _
Array(2, 2), Array(3, 2), Array(4, 2))
Set objWkb = .Workbooks(1)
Call fExportCommaDelimitedFile(objWkb.Worksheets(1), strFile & _
Dir(strFile) & ".csv")
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Function
Function fExportCommaDelimitedFile(objSht As Excel.Worksheet, _
strDestinationFile As String) _
As Boolean
'*******************************************
'Name: fExportCommaDelimitedFile (Function)
'Purpose: Writes a worksheet out as a CSV file
'Author: Dev Ashish
'Date: March 10, 1999, 12:21:10 PM
'Called by: Any
'Calls: sAppActivate
'Inputs: objSht - Excel Worksheet opened previously through Automation
' strDestinationFile - Path to destination CSV file
'Output: True on Success, false otherwise
'*******************************************
Dim intFileNum As Integer
Dim lngColCount As Long
Dim lngTotalColumns As Long
Dim lngTotalRows As Long
Dim lngRowCount As Long
Const conQ = """"
Const conERR_GENERIC = vbObjectError + 2100
intFileNum = FreeFile()
On Error GoTo ErrHandler
'Activate Access instance
Call sAppActivate
'If the target file exists, confirm that it should be deleted
If Len(Dir(strDestinationFile)) > 0 Then
If MsgBox("The target file specified " & vbCrLf & vbCrLf _
& strDestinationFile & vbCrLf & vbCrLf & " already exists." _
& vbCrLf & vbCrLf & "Are you sure you want to overwrite it?", _
vbQuestion + vbYesNo, "Please confirm") = vbYes Then
Kill strDestinationFile
Else
Err.Raise conERR_GENERIC
End If
End If
'Create the CSV file
Open strDestinationFile For Output As #intFileNum
With objSht
'Determine total number of columns
lngTotalColumns = .UsedRange.Columns.Count
'Determine total number of rows
lngTotalRows = .UsedRange.Rows.Count
'Initialize the progress meter
Call SysCmd(acSysCmdInitMeter, "Writing CSV file...", lngTotalRows)
'Go through all the rows
For lngRowCount = 1 To lngTotalRows
' Loop through each column
For lngColCount = 1 To lngTotalColumns
' Write current cell's text to file with quotation marks.
Print #intFileNum, conQ & RTrim$(.Cells(lngRowCount,
lngColCount).Value) & conQ;
' Check if cell is in last column.
If lngColCount = lngTotalColumns Then
'the end
Print #intFileNum,
Else
' Otherwise, write a comma.
Print #intFileNum, ",";
End If
Next lngColCount
Call SysCmd(acSysCmdUpdateMeter, lngRowCount)
'No need to hog the CPU for large worksheets
DoEvents
Next lngRowCount
End With
fExportCommaDelimitedFile = True
ExitHere:
On Error Resume Next
Call SysCmd(acSysCmdRemoveMeter)
Close #intFileNum
Exit Function
ErrHandler:
fExportCommaDelimitedFile = False
Resume ExitHere
End Function
