//
you're reading...
Programming

Open Method of Workbooks Class Failed

The Problem

I tried to open an excel file using Delphi 6. In my form, I had an Edit, an OpenDialog, and a Button. Basically, I wanted my app to open the file selected by the user.

Here is the code on the button’s OnClick

procedure TfUpload.btnPreviewClick(Sender: TObject);
var
  XLApp, XLSht: Variant;
begin
  try
    //create Excel instance
    XLApp := CreateOleObject('Excel.Application');
    XLApp.Workbooks.Open(Edit1.Caption, ReadOnly := True);
    XLSht := XLApp.Worksheets[SheetIndex];</code>

    //Some codes to show the data to StringGrid
    //....
    //....

  finally
    //free excel instance
    XLApp.DisplayAlerts := False;
    XLApp.Workbooks.Close;

    if not VarIsEmpty(XLApp) then
    begin
      XLApp.Quit;
      XLApp := Unassigned;
    end;
  end;
end;

When this code is executed, it often raises an “Open Method of Workbooks Class Failed” exception.

When I changed

XLApp.Workbooks.Open(Edit1.Caption, ReadOnly := True);

to

XLApp.Workbooks.Open('C:\some\path\to\file.xls', ReadOnly := True);

no exception is raised.

It also works fine when I tried something like this:

StrFileName := 'C:\some\path\to\file.xls';
XLApp.Workbooks.Open(StrFileName, ReadOnly := True);

However, when the StrFileName variable is given value from a control, it raises the goddamned exception.

StrFileName := Edit1.Caption; //or Memo1.Text or Label1.Caption
XLApp.Workbooks.Open(StrFileName, ReadOnly := True);

I don’t know why. I’ve tried to reinstall MS Office and Delphi, but it remains the same. The strange thing is that it happened only recently on all machines (WinXP, Vista, 7). It used to work fine before :s

The Workaround

Since I don’t have much time, I choose the dumbest solution ever. The user choose a file. The app copies & renames this file to a predefined name & path. The app then open the copied file.

CopyFile(PChar(Edit1.Caption),PChar('C:\some\path\to\file.xls'),False);
XLApp.Workbooks.Open('C:\some\path\to\file.xls', ReadOnly := True);

So far, this trick works fine, but I’m still looking for the real cause of this problem and the best practice to solve it.

Diskusi

Belum ada komentar.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: