Hi Mr Excel,
i am trying to get data from Ms access to excel Spreadsheet. the table filed in acess is decleared as text and i have stored text,number and decimal(1.5) as well. but when it comes to excel spreadsheet decimal number converts into Date format(30-Dec-1899) automatically.
below is the code for ur reference---
sql= "Select * from ABC"Call dbDisConnect
Dim RecCount As Long
Dim FldCount As Long
Dim RecVal As String
Dim i As Long
Dim j As Long
Call dbConnect
Rst.Open Sql, Con, adOpenKeyset, adLockPessimistic
If Sql = "" Then MsgBox "Invalid Operation", vbInformation, "Caution": Exit Sub
If Rst.RecordCount <= 0 Then MsgBox "No Data", vbInformation, "Display": Exit Sub
RecCount = Rst.RecordCount
FldCount = Rst.Fields.Count - 1
For i = 0 To FldCount
Sheet1.Cells(1, i + 1) = Rst.Fields(i).Name
Next
For i = 1 To RecCount
For j = 0 To FldCount
If Not VBA.IsNull(Rst.Fields(j).Value) Then RecVal = Rst.Fields(j).Value Else RecVal = ""
If VBA.IsDate(RecVal) Then
RecVal = VBA.Format(VBA.CDate(RecVal), "dd-mmm-yyyy")
End If
Dim RecCount As Long
Dim FldCount As Long
Dim RecVal As String
Dim i As Long
Dim j As Long
Call dbConnect
Rst.Open Sql, Con, adOpenKeyset, adLockPessimistic
If Sql = "" Then MsgBox "Invalid Operation", vbInformation, "Caution": Exit Sub
If Rst.RecordCount <= 0 Then MsgBox "No Data", vbInformation, "Display": Exit Sub
RecCount = Rst.RecordCount
FldCount = Rst.Fields.Count - 1
For i = 0 To FldCount
Sheet1.Cells(1, i + 1) = Rst.Fields(i).Name
Next
For i = 1 To RecCount
For j = 0 To FldCount
If Not VBA.IsNull(Rst.Fields(j).Value) Then RecVal = Rst.Fields(j).Value Else RecVal = ""
If VBA.IsDate(RecVal) Then
RecVal = VBA.Format(VBA.CDate(RecVal), "dd-mmm-yyyy")
End If
Sheet1.Cells(i + 1, j + 1) = RecVal
Next
Rst.MoveNext
Next
Rst.Close
Call dbDisConnect
Rst.MoveNext
Next
Rst.Close
Call dbDisConnect
please help.............
Last edited: