Hello everyone,
I need some assistance in finding an error in my Delphi code. (I am new to the Delphi programming). I am using a Delphi application to build an excel spreadsheet.
When a part_number is entered for the first time(assuming it exists in db) application always runs , the second time the spreadsheet comesback with no data. Like my code is skipped ( while QRY.eof = False do -line is skipped). PLease help, below is my code.
/////////////////////////////////////////////////////////////////

procedure TForm1.BuildButtonClick(Sender: TObject);
var
SheetName: String;
x:      Integer;
y:      Integer;
Sheet:  Variant;
ColumnRange: Variant;
fldcnt: Integer;
begin
///////////////////////////

  QryC.Close();
  QryC.SQL.Clear();
  QryC.SQL.Add('select item_no from ITEM_MASTER where item_no =trim(:v_item)');
  QryC.Parameters.ParamByName('v_item').Value := PartNumberEdit.Text;
  QryC.Open();

  if QryC.RecordCount <> 1 then
         begin
        ShowMessage('Couldn''t find part number ' + PartNumberEdit.Text + '.');
        PartNumberEdit.text :='';
        PartNumberEdit.SetFocus();
        end
   else if QryC.RecordCount = 1 then
   begin
// Execute the stored procedure to prep the query.
        Proc.ProcedureName := 'CostAnalyzerViewBuild_KANBAN';
        Proc.Parameters.CreateParameter('v_partnumber', ftString, pdInput, 24, 0);
        Proc.Parameters[0].Value := PartNumberEdit.Text;
        Proc.ExecProc();
        Proc.Parameters.Clear();

        QRY.Close();
        QRY.Open();
        gauge.Visible := True;
        gauge.Min := 0;
        gauge.Position := 0;
        gauge.Max := QRY.RecordCount;

        //if VarIsEmpty(XLApp) then
        XLApp:= CreateOleObject('Excel.Application');
        //XLApp.Visible := True;
        XLApp.Workbooks.Add(xlWBatWorkSheet);
        SheetName := 'BOM Summary ' + IntToStr(SheetNumber);
        XLApp.Workbooks[1].WorkSheets[1].Name := SheetName;
        Sheet := XLApp.Workbooks[1].WorkSheets[SheetName];
        ColumnRange := XLApp.Workbooks[1].WorkSheets[SheetName].Columns;
        fldcnt := QRY.FieldDefs.Count; // the number of columns in the result set

        for y := 0 to fldcnt - 1 do
             begin

                XLApp.Workbooks[1].Worksheets[1].Range['A1', 'A1'].EntireColumn.NumberFormat := '#,###,###,##0.0#_);[Red](#,###,###,##0.0#_)';
                XLApp.Workbooks[1].Worksheets[1].Range['B1', 'B1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
                XLApp.Workbooks[1].Worksheets[1].Range['C1', 'C1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
                XLApp.Workbooks[1].Worksheets[1].Range['D1', 'D1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
                XLApp.Workbooks[1].Worksheets[1].Range['E1', 'E1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
                XLApp.Workbooks[1].Worksheets[1].Range['F1', 'F1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
                XLApp.Workbooks[1].Worksheets[1].Range['G1', 'G1'].EntireColumn.NumberFormat := '#,###_);[Red](#,###)';

                ColumnRange.Columns[y+1].AutoFit;
                Sheet.Cells[1, y+1] := QRY.FieldDefs.Items[y].Name;
               end;

        x := 2;
       while QRY.eof = False do

        begin
                for y := 0 to fldcnt - 1 do
                begin
                        Sheet.Cells[x, y+1] := QRY.Fields.Fields[y].AsString;
                end;
                x := x + 1;
                // NEW SHEET?
                if x >= 65530 then
                begin
                        for y := 0 to fldcnt - 1 do
                        begin
                                ColumnRange.Columns[y+1].AutoFit;
                        end;
                        x := 2;
                        SheetNumber := SheetNumber + 1;
                        SheetName := 'BOM Summary ' + IntToStr(SheetNumber);
                        XLApp.Workbooks[1].Sheets.Add();
                        XLApp.Workbooks[1].Sheets[1].Name := SheetName;
                        Sheet := XLApp.Workbooks[1].Sheets[1];
                        ColumnRange := Sheet.Columns;
                        for y := 0 to fldcnt - 1 do
                        begin
                                ColumnRange.Columns[y+1].NumberFormat := '#,###,###,##0.0#_);[Red](#,###,###,###.00)';
                                ColumnRange.Columns[y+1].AutoFit;
                                Sheet.Cells[1, y+1] := QRY.FieldDefs.Items[y].Name;
                        end;
                  end;
                Application.ProcessMessages();
                gauge.Position := gauge.Position + 1;
                QRY.Next();
                PartNumberEdit.Text :=' ';
                PartNumberEdit.SetFocus();
        //end;
        for y := 0 to fldcnt - 1 do
        begin
                ColumnRange.Columns[y+1].AutoFit;
        end;
        end;
        Beep();
        QRYC.Close();
        QRY.Close();

        XLApp.Visible := True;
        SheetNumber := SheetNumber + 1;
        gauge.Position := 0;
        gauge.Visible := False;

       end;
 end;

///////////////////////////////////////////////////////
Thank you in advance,
Sonya

Recommended Answers

All 4 Replies

Try

while not (eof(QRY)) do

eof is a function rather than an atribute to the file.

Hope this helps,

Tom

Thank you Tom,
I changed the code to
while not QRY.eof do
The first record run fine and the second retuns nothing in the excel speadsheet.
Any other recommendations?

Thank you again,
Sonya

I meant that the eof should look like this

eof(QRY)

If this comes up as an error try adding system to the uses,

Tom

I figured out what was wrong: applied RightTrim and LeftTrim functions to part number text field. For some unknown reason, a program was adding an extra space to each second part. Sonya

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.