Date problem with ADO and Excel

[RyanEK]

Hi,

Some help would be really appreciated. I have an ADO connection to an excel file:

adc.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0;'

When I do an ADO query on an excel sheet, it doesn't recognise fields where the format is a 'Date'. It returns as empty.

Any ideas?

Ry

 

[CamaroLT]

Open a new project, drop an ADO component, as well as a stringgrid on your form. Double-click on the form itself, and paste this code in:

procedure TForm1.FormCreate(Sender: TObject);
var
  rs:TADODataSet;
  x,y:integer;
  ListOfTables:tstringlist;
begin
  ListOfTables:=tstringlist.create;
  rs:=TADODataSet.Create(nil);
  rs.Connection:=Form1.ADOConnection1;
  rs.Connection.Open;
  Form1.ADOConnection1.GetTableNames(ListOfTables);

  rs.CommandText:='select * from ['+ListOfTables.Strings[0]+']';
  rs.Open;
  x:=0;
  while not rs.eof do begin
    StringGrid1.ColCount:=rs.fields.Count;
    StringGrid1.RowCount:=x+1;
    for y:=0 to rs.fields.count-1 do begin
      if y=0 then
        StringGrid1.Cells[y,x]:=inttostr(rs.fields.Fields[y].AsInteger);
      if y=1 then
        StringGrid1.cells[y,x]:=FormatDateTime('mmm d, yyy',rs.Fields.Fields[y].AsDateTime);
      if y=2 then
        StringGrid1.Cells[y,x]:=rs.Fields.Fields[y].AsString;
    end;
    inc(x);
    rs.Next;
  end;
  rs.close;
end;

Create a new excel spreadsheet for simplicity sake:

A1=1
A2=2
A3=3
A4=4
B1=1/1/2004
B2=2/2/2005
B3=3/3/2006
B4=4/4/2007
C1=ABC
C2=CDE
C3=EFG
C4=GHI

Save the XLS file somewhere (C:\Book1.xls will do) and setup the ADO component the same as you did above.

This worked for me without issue. The source code is intended to show what you can do with datetime and how to handle different expected field types.

Good start to look at:

http://delphi.about.com/od/database/l/aa090903a.htm

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job