We help IT Professionals succeed at work.

Excel: How to search for text in a column and delete it if it is found?

Stef Merlijn
Stef Merlijn asked
on
483 Views
Last Modified: 2010-04-05
Hi,

I need to search for an existing column that contains the value "MySearchText" on the first row in all worksheets within a Excel-file.
If this search is succesful, that whole column must be deleted from the Excel-file.

This is what I have sofar:

procedure TFImporteren.DeleteColumn;
var i  : integer;
    OleApplication3 : variant;
    OleWorkBook3    : variant;
    OleWorkSheet3   : variant;
    NaamWorksheet   : String;
begin
  OleApplication3 := CreateOleObject('Excel.Application');
  OleApplication3.visible := False;
  //open the excel file here
  OleWorkBook3    := OleApplication3.Workbooks.open('My excelfile.xls');
  for i := 1 to OleApplication3.ActiveWorkbook.WorkSheets.Count do
  begin
    NaamWorksheet := OleApplication3.ActiveWorkbook.WorkSheets[i].Name;
    // The first worksheet is opened.
    OleWorkBook3.Worksheets.item[NaamWorksheet].Activate;
    OleWorkSheet3 := OleWorkBook3.ActiveSheet;
    // Position into the first cell
    OleApplication3.Range['A1'].select;
    // Search for a cell containing the string: "MySearchText"

    // FROM HERE THE CODE IS WRONG OR INCOMPLETE

    If OleWorkSheet3.Cells.Find('MySearchText', OleWorkSheet3.Cells.Item[51,51], xlValues, xlWhole, xlByColumns, xlNext, False, False) then
    begin
      ShowMessage('Worksheet: '+ NaamWorksheet + Chr(13)
                 +'Column is found.')
      // Select and delete the whole column
      ???????
    end;
  end;
  try
    OleApplication3.Quit;
    OleApplication3 := UnAssigned;
    FreeAndNil(OleApplication3);
  except
  end;
end;
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Stef MerlijnDeveloper

Author

Commented:
This solution doesn't work. Can you please check the code?
It stops on:
   OleWorkSheet3.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;

Commented:
Hi !

I can't test it now, becouse where I am delphi is not installed.
I can check the code only tomorow.
Anyway try to change following :

Last_cell : variant;

replace :   OleWorkSheet3.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
                 x := OleAplication3.ActiveCell.Column;  // find last column
with :
      Last_cell := OleWorkSheet3.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam);    
      x := Last_Cell.Column;

Good luck,
 Steve
Stef MerlijnDeveloper

Author

Commented:
I've found it myself. Thank you very much for the help.

procedure TFImporteren.HaalWerkbladenOp;
var i, AantalKolommen, HuidigeKolom :integer;
    VerwijderKolom : boolean;
    OleApplication3 : variant;
    OleWorkBook3    : variant;
    OleWorkSheet3   : variant;
    NaamWorksheet   : String;
begin
  OleApplication3 := CreateOleObject('Excel.Application');
  OleApplication3.visible := False;
  OleWorkBook3    := OleApplication3.Workbooks.open(beOpenBestand.Text);
    for i := 1 to OleApplication3.ActiveWorkbook.WorkSheets.Count do
    begin
      NaamWorksheet := OleApplication3.ActiveWorkbook.WorkSheets[i].Name;
      OleWorkSheet3 := OleWorkBook3.Worksheets.Item[NaamWorksheet];
      OleWorkSheet3.Activate;
      OleWorkSheet3.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
      HuidigeKolom := 0;
      VerwijderKolom := False;
      AantalKolommen := OleApplication3.ActiveCell.Column;  // Zoek de laatste kolom
      repeat
        Inc(HuidigeKolom);
        if Pos('Foutmelding', OleWorkSheet3.Cells.Item[1,HuidigeKolom]) > 0  then
        begin
          VerwijderKolom := True;
          Break;
        end;
      until HuidigeKolom = AantalKolommen;
      if VerwijderKolom then
      try
        OleWorkSheet3.Columns[HuidigeKolom].EntireColumn.Delete(TOleEnum(xlShiftDown));
        OleWorkBook3.Save;
      except
      end;
    end;
  end;
  try
    OleApplication3.Quit;
    OleApplication3 := UnAssigned;
    FreeAndNil(OleApplication3);
  except
  end;
end;

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.