title: Delphi: how to trim a excel range
body: <p>Although I am a great fan of stackoverflow and I found several good solutions, this is my first question.
  I am writing a Delphitool (D6... old but still working!) for making it easier to administrate our students at school.</p>
  
  <p>Delphi reads some (even older...) Visual Foxpro Dbase tables and collects the needed data in a query. This data should be exported to Excel.
  "Theoretically" everything is working, but I have several problems:</p>
  
  <ol>
  <li>query fields are padded with lots of leading spaces, this seems
  to be a FoxPro issue (I found this:
  <a href="http://www.ml-consult.co.uk/foxst-36.htm" rel="nofollow noreferrer">http://www.ml-consult.co.uk/foxst-36.htm</a>, don't know how to work
  with it)</li>
  <li>trimming through sql-commands does not work (whitespace
  is created by foxpro query, the tables are "clean")</li>
  <li>tried to get things done in Delphi with the help of "onGetText" Method of field
  names - that helps, but within the dataset the spaces are untouched 
  (only their field values in the grid are correct) - also no help
  because exporting to delphi uses the query</li>
  <li>so I have to get rid of the whitespaces from Delphi to Excel. I use this:
  <a href="https://stackoverflow.com/questions/17058563/exporting-data-from-a-dbgrid-to-excel">Exporting data from a DBGrid to Excel</a>
  and thought I could execute Excel's trim-funtion to manipulate the
  fields. So my question is: how can I execute vba-code? I found this:
  <a href="https://stackoverflow.com/questions/2964769/trim-cells-using-vba-in-excel">Trim Cells using VBA in Excel</a></li>
  </ol>
  
  <p>Any ideas? Thanks in advance!</p>
  
  <p>PS:
  I also looked for a simple "replace all spaces but not within words" way, thought of regexp, but found no way to get this done from within my Delphi code.</p>
  
  <p>As there is not very much text in the excel sheet (100 lines, 4 columns), I could walk through every single cell... but that seems to be not very elegant.
  <a href="https://stackoverflow.com/questions/14698745/how-does-trim-work-in-excel-vba">How does Trim work in Excel VBA?</a>
  <a href="https://stackoverflow.com/questions/3785551/how-to-trim-text-in-a-column-with-vba-excel">How to trim text in a column with VBA - Excel</a></p>
  
  <p>So there's the code for simple replacement of all spaces - unfortunately it also replaces necesserary space between 2 words in a cell:  </p>
  
  <pre><code> ovExcelApp := CreateOleObject('Excel.Application');
     try
     ovExcelApp.displayAlerts := false;
     ovExcelWorkbook := ovExcelApp.WorkBooks.Add;
     ovWS := ovExcelWorkbook.Worksheets.Item[1]; 
     ovWS.Activate;
     ovWS.Select;
     ovWS.Cells.NumberFormat := AnsiChar('@');
     ovRange := ovWS.Range['A1', 'A1']; //go to first cell
     ovRange.Resize[Data.RecordCount, Data.Fields.Count];
     ovRange.CopyFromRecordset(Data, Data.RecordCount, Data.Fields.Count); 
     ovExcelApp.Cells.Replace(
         What := ' ',
         Replacement := '', 
         LookAt := 2, 
         SearchOrder := 1, 
         MatchCase := False,
         SearchFormat := False,
         ReplaceFormat := False
         );
     end;
     // How could I do a regexp (only replace leading spaces) or do a trim over all cells withing ovWS?
     ovRange.EntireColumn.AutoFit;
     ovWS.SaveAs(DestName, 1, '', '', False, False);
  </code></pre>
  
  <p>Trying to trim field values brings an error:</p>
  
  <pre><code>procedure Tf_dbftools.qADO_einzelnAfterOpen(DataSet: TDataSet);
  begin
    with qADO_einzeln do begin
      first;
      while not eof do begin
        edit;
        Fields[0].AsString := trim(fields[0].asstring);
        Fields[1].AsString := trim(fields[1].asstring);
        Fields[2].AsString := trim(fields[2].asstring);
        Fields[3].AsString := trim(fields[3].asstring);
        next;
      end;
      //post -&gt; error
    end;
  end;
  </code></pre>
  
  <p>I just need to edit the data (from different tables) in the qADO Query resp. dataset to get things correctly exported to Excel. Do I really need to copy the data into a clientDataset to edit them?</p>