Brad,
I use the ADO function to read data from excel.
So every excel tabsheet i handle as a database table and i can query on it.
perhaps this is a solution for your problem.
Below i have a example how you can do it.
Regards
R. Wiertz
Quality Assurance
Furore.com
Procedure ReadFromExcel(aExcelFile : String);
var
DBConnection : OleVariant;
Command : OleVariant;
RecordSet : OleVariant;
ConnectionString : String;
strQuery : String;
SheetName : String;
iFieldCount : Integer;
begin
ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ' + aExcelFile + ';Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"';
if Length(aConnectionString) > 0 then
begin
DBConnection := ADO.CreateConnection();
DBConnection.ConnectionString := aConnectionString;
try
DBConnection.LoginPrompt := False;
except
//do nothing, there is no login prompt
end;
try
DBConnection.Open;
SheetName := 'Your excel sheet name';
strQuery := 'Select * from ['+SheetName+'$]';
Command := ADO.CreateCommand();
Command.CommandText := Query;
Command.ActiveConnection := DBConnection;
RecordSet := Command.Execute();
while not aqConvert.VarToBool(RecordSet.EOF) do
begin
for iFieldCount := 0 to RecordSet.Fields.count -1 do
begin
Log.message(aqConvert.VarToStr(RecordSet.Fields(iFieldCount).name));
Log.Message(aqConvert.VarToStr(RecordSet.Fields(iFieldCount).Value));
end;
end;
RecordSet.Close;
DBConnection.Close;
end;