Recent

Author Topic: EVariantInvalidOpError  (Read 1187 times)

draggon

  • New member
  • *
  • Posts: 40
EVariantInvalidOpError
« on: October 25, 2018, 01:56:39 pm »
Hi, when trying to delete and Save As excel file I encounter the follofing message:

exception of class 'EVariantInvalidOpError' with message: Invalid variant operation

Code: Pascal  [Select]
  1. XLApp.Workbooks[dir + '/' + tmp_year + '/' + file_name + '.xlsx'].Delete;

Thanks

Cyrax

  • Hero Member
  • *****
  • Posts: 624
Re: EVariantInvalidOpError
« Reply #1 on: October 25, 2018, 03:13:31 pm »
Show us more of your source code. What type are dir, tmp_year and file_name variables?

Thaddy

  • Hero Member
  • *****
  • Posts: 7182
Re: EVariantInvalidOpError
« Reply #2 on: October 25, 2018, 05:17:10 pm »
Yes we need more code.
But when you debug, the obvious two variables to look at are:
- dir
- file_name

because both of them can contain extra slashes.
Also note that Windows uses backward slashes \ , not forward slashes /....
There are normalizing functions (like excludetrailingpathdelimiter and family) and variables (PathDelimiter)  for that in sysutils, I believe.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

draggon

  • New member
  • *
  • Posts: 40
Re: EVariantInvalidOpError
« Reply #3 on: October 26, 2018, 08:24:47 am »
Thanks, more code added, back slashes do not help, forward slashes for dir_exist worked.

file_name, dir I didn't discover any extra slashes. Thanks

Code: Pascal  [Select]
  1.  {Checks for existing data in tables costs and breakdown and confirmation to replace it}
  2.         if MessageDlg('Data save', 'Data generated previously will be destroyed.' + sLineBreak + 'Do you want to save it to separate file?', mtConfirmation,
  3.         [mbYes, mbNo],0) = mrYes then
  4.          begin
  5.  
  6.               {Checks for directory exists}
  7.               dir_exists(dir + '\' + tmp_year, data_save);
  8.  
  9.               if data_save = False then
  10.               begin
  11.                    If Not CreateDir (dir + '\' + tmp_year) Then
  12.                    begin
  13.                        ShowMessage('Failed to create directory !');
  14.                    end
  15.               else
  16.                    ShowMessage('Created sub directory ' + tmp_year);
  17.               end;
  18.               file_name:= months_list[start_month_index] + '_' + months_list[end_month_index] + '_' + tmp_year;
  19.  
  20.               {Checks for file exists}
  21.               if FileExists(dir + '\' + tmp_year + '\' + file_name + '.xlsx') Then
  22.  
  23.               begin
  24.                   if MessageDlg('Data save', 'File exists.' + sLineBreak + 'Do you want to replace it?', mtConfirmation,
  25.                   [mbYes, mbNo],0) = mrYes then
  26.                   begin
  27.  
  28.                        {Deletes existing file and saves new one}
  29.                        XLApp.Workbooks[dir + '\' + tmp_year + '\' + file_name + '.xlsx'].Delete;
  30.                        XLApp.Workbooks[dir + '\project_report_pas.xlsx'].SaveAs(dir + '\' + tmp_year + '\' + file_name + '.xlsx');
  31.                   end;
  32.               end;
  33.  
  34.          end;

draggon

  • New member
  • *
  • Posts: 40
Re: EVariantInvalidOpError
« Reply #4 on: October 27, 2018, 02:27:03 pm »
Hi, I added ComObj into Uses section, I expected it was available since it is in another Unit my Unit refers to. I modified code as follows, now I got another message:
EOleException with message Invalid Index. The same appears if I replace the formula with string of real path to the file.
Thanks

Code: Pascal  [Select]
  1. {Checks for existing data in tables costs and breakdown and confirmation to replace it}
  2.         if MessageDlg('Data save', 'Data generated previously will be destroyed.' + sLineBreak + 'Do you want to save it to separate file?', mtConfirmation,
  3.         [mbYes, mbNo],0) = mrYes then
  4.          begin
  5.  
  6.               {Checks for directory exists}
  7.               dir_exists(dir + '\' + tmp_year, data_save);
  8.  
  9.               if data_save = False then
  10.               begin
  11.                    If Not CreateDir (dir + '\' + tmp_year) Then
  12.                    begin
  13.                        ShowMessage('Failed to create directory!');
  14.                    end
  15.               else
  16.                    ShowMessage('Created sub directory ' + tmp_year);
  17.               end;
  18.               file_name:= months_list[start_month_index] + '_' + months_list[end_month_index] + '_' + tmp_year;
  19.  
  20.  
  21.               XLApp:= CreateOleObject('Excel.Application');
  22.               XLApp.Visible:= False;
  23.               XLApp.DisplayAlerts:= True;
  24.  
  25.               ShowMessage(dir + ' ' + file_name);
  26.               {Checks for file exists}
  27.  
  28.               if FileExists(dir + '\' + tmp_year + '\' + file_name + '.xlsx') Then
  29.  
  30.               begin
  31.  
  32.                    if MessageDlg('Data save', 'File exists.' + sLineBreak + 'Do you want to replace it?', mtConfirmation,
  33.                   [mbYes, mbNo],0) = mrYes then
  34.  
  35.                   begin
  36.  
  37.                       {Deletes existing file and saves new one}
  38.                      
  39.                       XLApp.Workbooks(dir + '\' + tmp_year + '\' + file_name + '.xlsx').Delete;
  40.                       XLApp.Workbooks(dir + '\project_report_pas.xlsx').SaveAs(dir + '\' + tmp_year + '\' + file_name + '.xlsx');
  41.                       //XLApp.Workbooks('C:\Users\marek\Desktop\rd_costs_pas\2018\Jan_Dec_2018.xlsx').Delete;
  42.                      
  43.                   end;
  44.               end
  45.               else
  46.               begin
  47.                    XLApp.Workbooks(dir + '\project_report_pas.xlsx').SaveAs(dir + '\' + tmp_year + '\' + file_name + '.xlsx');
  48.               end;
  49.               XLApp.Quit;
  50.               XLApp:= Unassigned;
  51.          end;
« Last Edit: October 27, 2018, 05:01:00 pm by draggon »

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1227
Re: EVariantInvalidOpError
« Reply #5 on: October 27, 2018, 04:07:20 pm »
For the life of me I can't see where you are opening the XLS file, and I'm struggling to remember the behaviour of OLE

You take control of Excel via OLE
You try to delete a workbook

This may be because you're taking control of an instance of Excel that's already open and already has your workbook in it.  You can't guarantee this, so I'd add some defensive code.

Can't see that being your error though - suspect you'd get a different error message for that.  But for now, can you try the following?

Code: Pascal  [Select]
  1. Var
  2.   varTemp: Variant;
  3.  
  4. ...
  5.   varTemp := dir + '\' + tmp_year + '\' + file_name + '.xlsx';
  6.   XLApp.Workbooks(varTemp).Delete;

Sure, I'm guessing :-)  A guess entirely based on this file: https://sourceforge.net/p/lazarus-ccr/svn/4536/tree//components/fpspreadsheet/tests/exceltests.pas

FYI: http://wiki.freepascal.org/Office_Automation#Reading.2FWriting_an_Excel_file_using_OLE
« Last Edit: October 27, 2018, 04:11:55 pm by Mike.Cornflake »
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

draggon

  • New member
  • *
  • Posts: 40
Re: EVariantInvalidOpError
« Reply #6 on: October 28, 2018, 09:35:56 am »
Thanks, I stopped running just before deleting the file and found in Task Manager two Excel task. I have no idea why, all Excel operations were closed before.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1227
Re: EVariantInvalidOpError
« Reply #7 on: October 28, 2018, 10:22:15 am »
One of the reason I stopped using OLE and controlling Excel.  Discovering fpSpreadsheet (a well-written library for direct reading and writing spreadsheets, located in Lazarus CCR) was a breath of fresh air :-)

http://wiki.freepascal.org/FPSpreadsheet
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

draggon

  • New member
  • *
  • Posts: 40
Re: EVariantInvalidOpError
« Reply #8 on: October 28, 2018, 01:35:32 pm »
Unfortunately this is not the case since Excel is default for those users.

Thaddy

  • Hero Member
  • *****
  • Posts: 7182
Re: EVariantInvalidOpError
« Reply #9 on: October 28, 2018, 02:29:23 pm »
You should learn how to control ms office applications with early binding (COM + tlb) instead of using OLE.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

draggon

  • New member
  • *
  • Posts: 40
Re: EVariantInvalidOpError
« Reply #10 on: October 31, 2018, 02:44:01 pm »
I think it is not OLE related issue, because I am reading/ writing data from/ to Excel files through entire application without problems. For deleting files works DeleteFile from Fileutil, what does not work is CopyFile(src, dest). It works e.g. for *.txt files but for *.xlsx. I didn't find any information about limitation to particular file types.

Thanks

Thaddy

  • Hero Member
  • *****
  • Posts: 7182
Re: EVariantInvalidOpError
« Reply #11 on: October 31, 2018, 03:12:13 pm »
CopyFile is file type agnostic so that can not be the cause, ever.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

Zoran

  • Hero Member
  • *****
  • Posts: 1283
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: EVariantInvalidOpError
« Reply #12 on: October 31, 2018, 03:34:46 pm »
Unfortunately this is not the case since Excel is default for those users.

You don't understand, FPSpreadSheet works fine with excel files (.xls and .xlsx) directly. Your user can use excel and your program can use FPSpreadSheet to read, make changes and write to these files.

Wiki contains several tutorials, see the links in wiki category -- http://wiki.freepascal.org/Category:FPSpreadsheet

Use svn version (the repository: https://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet), as it is actively maintained and constantly improving.

Thaddy

  • Hero Member
  • *****
  • Posts: 7182
Re: EVariantInvalidOpError
« Reply #13 on: October 31, 2018, 03:54:26 pm »
Indeed Zoran, we gave him all the answers. But he has tunnel vision.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.