[SOLVED] From Excel file to StringGrid

[mari_ia]

Hi there

Please explain how to resolve "Unable to get the SpecialCells property of the Range class" in this code:

Code: Pascal  [Select][+][-]

1. procedure OpenStringGrid(StringGrid: TStringGrid; const FileName: TFileName);
2. const
3.   xlCellTypeLastCell = $0000000B;
4. var
5.   ExcelApp, ExcelSheet : OLEVariant;
6.   RangeMatrix, EmptyParam: Variant;
7.   x, y, i, j: Integer;
8. begin
9.     ExcelApp := CreateOleObject('Excel.Application');
10.     try
11.     ExcelApp.Visible := False;
12.     ExcelApp.WorkBooks.Open(FileName:=FileName);
13.     //ExcelSheet:= ExcelApp.Workbooks[1].WorkSheets[1];
14.     ExcelSheet:= ExcelApp.Workbooks[ExtractFileName(FileName)].WorkSheets[1];
15.  
16.     ExcelSheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
17.  
18.     x:=ExcelApp.ActiveCell.Row;
19.     y:=ExcelApp.ActiveCell.Column;
20.     StringGrid.RowCount:=x+1;
21.     StringGrid.ColCount:=y;
22.     RangeMatrix:= ExcelApp.UsedRange['A1', ExcelApp.Cells.Item[X, Y]].Value;
23.  
24.      for j:=0 to StringGrid.ColCount-1 do
25.       begin
26.        for i:=0 to StringGrid.RowCount-2 do
27.         begin
28.           StringGrid.Cells[j, i+1]:=RangeMatrix[i, j];
29.         end;
30.       end;
31.    Rangematrix:= Unassigned;
32.     finally
33.     ExcelApp.Quit;
34.     ExcelApp := Unassigned;
35.     ExcelSheet := Unassigned;
36. end;
37. end;
38.  
39. procedure TForm1.Button10Click(Sender: TObject);
40. begin
41.   if OpenDialog1.Execute then
42.   OpenStringGrid(StringGrid1, OpenDialog1.FileName);
43. end;
44.  

Thank you in advance!!!

[rvk]

Just a guess but I don't think you can get the correct workbook by using the filename as index for the workbook array.

ExcelApp.Workbooks[ExtractFileName(FileName)]

[mari_ia]

I have corrected the code like:

Code: Pascal  [Select][+][-]

1.  ExcelApp.WorkBooks.Open(FileName);
2.  ExcelSheet:= ExcelApp.Workbooks[1].WorkSheets[1];
3.  

but the error is still there

[rvk]

Is the file actually loaded?
Check with

Code: Pascal  [Select][+][-]

1. ShowMessage( ExcelApp.Workbooks[1].Name );

You can also (probably) access the worksheet directly via ExcelApp if it's the active workbook.

Code: Pascal  [Select][+][-]

1. ExcelSheet:= ExcelApp.WorkSheets[1];

(I can't try it out now, maybe later this morning)

[mari_ia]

Seems it loads because it shows the name of file in the message window. But the error is still in the code.

I have decided to check that selected file. I could open it only in the read-only mode (without editing). No idea what my program does with that file.

[rvk]

I have decided to check that selected file. I could open it only in the read-only mode (without editing). No idea what my program does with that file.

Then the file will probably open ok but the ExcelSheet.Cells.SpecialCells doesn't work.

It's a known limitation of SpecialCells function that it doesn't work on protected sheets (and probably also not on read-only files).

You could try UsedRange:

Code: Pascal  [Select][+][-]

1. x := ExcelSheet.UsedRange.Row + ExcelSheet.UsedRange.Rows.Count - 1;
2. y := ExcelSheet.UsedRange.Column + ExcelSheet.UsedRange.Columns.Count - 1;

(not tested)

And find out why your file is read-only.
Do you have it open on another screen?
Maybe reboot your computer and try again (you could have several instances of invisible Excel running with that file still open).
(you can check that in the task manager)

[mari_ia]

it doesn't work

[rvk]

it doesn't work

Pitty.

But you not telling what exactly doesn't work isn't helping either.

The x/y lines I showed should work fine.
(you can put a Showmessage(x.tostring + ' ' + y.tostring); after them to see the values.)

Next you do something with UsedRange on ExcelApp (which isn't correct because you should use ExcelSheet).
And I'm not sure what this code is supposed to do.

The SpecialCells error message should be solved now (because you should have commented it out in favor of the x/y lines).

[rvk]

Were you after something like this??

Code: Pascal  [Select][+][-]

1. uses ComObj;
2.  
3. procedure OpenStringGrid(StringGrid: TStringGrid; const FileName: TFileName);
4. const
5.   xlCellTypeLastCell = $0000000B;
6. var
7.   ExcelApp, ExcelSheet: olevariant;
8.   x, y, i, j: integer;
9. begin
10.   ExcelApp := CreateOleObject('Excel.Application');
11.   StringGrid.BeginUpdate;
12.   try
13.     // ExcelApp.Visible := False;
14.     ExcelApp.WorkBooks.Open(FileName := FileName);
15.     ExcelSheet := ExcelApp.Workbooks[ExtractFileName(FileName)].WorkSheets[1];
16.     x := ExcelSheet.UsedRange.Row + ExcelSheet.UsedRange.Rows.Count - 1;
17.     y := ExcelSheet.UsedRange.Column + ExcelSheet.UsedRange.Columns.Count - 1;
18.     StringGrid.RowCount := x;
19.     StringGrid.ColCount := y;
20.     for j := 0 to StringGrid.ColCount - 1 do
21.       for i := 0 to StringGrid.RowCount - 1 do
22.         StringGrid.Cells[j, i] := ExcelApp.Cells.Item[i + 1, j + 1].Value;
23.   finally
24.     StringGrid.EndUpdate;
25.     ExcelApp.Quit;
26.     ExcelApp := Unassigned;
27.     ExcelSheet := Unassigned;
28.   end;
29. end;

[mari_ia]

rvk, I have done everything according to your recommendations, but the error still appears.

Once I will copy and run your last code, I will let you know.

Thank you so much again!

[Zvoni]

Hi there

Please explain how to resolve "Unable to get the SpecialCells property of the Range class" in this code:

Code: Pascal  [Select][+][-]

1. //*snipp*
2.     ExcelSheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
3.  
4. // *snipp*
5.  

Thank you in advance!!!

https://learn.microsoft.com/en-us/office/vba/api/excel.range.specialcells

Parameters
Name    Required/Optional    Data type    Description
Type    Required    XlCellType    The cells to include.
Value    Optional    Variant    If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Try

Code: Pascal  [Select][+][-]

1. ExcelSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate;
2.  

[mari_ia]

rvk, you're super! It works! Thank you a lot