How to collect more infos, save the file...?

Add-in Express™ Support Service
That's what is more important than anything else

How to collect more infos, save the file...?
 
iubito




Posts: 16
Joined: 2014-06-19
Hi !
Getting the name and saving is OK :)

But the Open throws an OLE error 800A01a8.
Same error opening a file, a network file or an URL


OleVariant((IWindow as Excel2000.Window).Application.Workbooks)
   .Open('C:ile.xls');


I tried the .DisplayAlerts := False before, still the same error.
I tried also to encapsulate everything in OleVariant:

OleVariant(
  OleVariant(
    OleVariant(
      IWindow as Excel2000.Window
    ).Application
  ).Workbooks
).Open('...')


What I'm doing wrong ?
Sylvain

DEV: Delphi 7, Win XP, Office 2007
TARGET: Win XP-7-2012 x64, Office 2007-2010
Posted 04 Aug, 2014 05:37:20 Top
Andrei Smolin


Add-in Express team


Posts: 14114
Joined: 2006-05-11
Hello Sylvain,

In what event do you perform this? With obvious modifications, this works correctly when called form the Click event of a Ribbon button.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 04 Aug, 2014 06:35:56 Top
iubito




Posts: 16
Joined: 2014-06-19
On click a ribbon button, I do :
- workbook.saveAs file1
- workbook.saveCopyAs file2
- call an external EXE with file2
- wait until this exe terminates
- delete file2
- workbook.close
- delete file1
- open file3
Sylvain

DEV: Delphi 7, Win XP, Office 2007
TARGET: Win XP-7-2012 x64, Office 2007-2010
Posted 04 Aug, 2014 06:54:21 Top
iubito




Posts: 16
Joined: 2014-06-19
After simplification, here is my complete code, and it still doesn't work

procedure TAddInModule.HopHopHopClick;
var
  IWindow: IDispatch;
  Excel: Excel2000.ExcelWorkbook;
  S: WideString;
  CRLF: string;
  I: Integer;
  fileName: string;
  files: TStringList;
  Param, ReturnFilePath: string;
  ReturnFile: TextFile;
begin
  CRLF := #10#13;
  try
    // Word raises an exception if there's no document open
    IWindow := HostApp.ActiveWindow;
  except
    MessageDlg('Il n''y a pas de document ouvert', mtError, [mbOK], 0);
    Exit;
  end;
  try
    case HostType of
      ohaExcel:
        if IWindow <> nil then
        begin
          Excel := (IWindow as Excel2000.Window).Parent
            as Excel2000.ExcelWorkbook;
          Param := '';
          fileName := OleVariant(Excel).Fullname;
          if (Copy(fileName, 1, 4) = 'http') or (Copy(fileName, 1, 2) = '\') then
          begin {URL or network share}
            //Special detection
          end
          else if (pos('', fileName) = 0) or (pos('/', fileName) = 0) then
          begin
              //File just created -> save as XLSX
              //www.rondebruin.nl/win/s5/win001.htm
              //51 = xlOpenXMLWorkbook (xlsx without macro)
              //52 = xlOpenXMLWorkbookMacroEnabled (xlsm)
              //50 = xlExcel12 (xlsb Excel binary 2007-2013 with/without macro)
              //56 = xlExcel8 (xls 97-2003 in 2007-2013)
            fileName := GetTempDir + '' + fileName + '.xlsx';
            OleVariant(Excel).SaveAs(fileName, 51);
          end;
          Param := Param + ' "' + fileName + '"';
          ReturnFilePath := GetTempFile('return'); //returnXYZ.tmp
          Param := Param + ' "' + ReturnFilePath + '"';
          ShellExecuteAndWait('"MyApp.exe" ' + Param);
          //MyApp give a reply in the tempfile returnXYZ.tmp
          AssignFile(ReturnFile, ReturnFilePath);
          try
            Reset(ReturnFile);
            S := '';
            if not (eof(ReturnFile)) then
            begin
              ReadLn(ReturnFile, S);
              if (Copy(S, 1, 6) = 'COUPER') or (Copy(S, 1, 6) = 'COPIER') then
              begin
                OleVariant(Excel).Close();
                if (Copy(S, 1, 6) = 'COUPER') then
                begin
                  DeleteFileToRecycleBin(fileName);
                end;
                ShowMessage(Copy(S, 7, Length(S)));
//Until here everything is OK
//Here arrive the OLE error message
                OleVariant(IWindow as Excel2000.Window).Application
                  .Workbooks.Open('C:	able.xls');
              end;
            end;
          finally
            CloseFile(ReturnFile);
            if (not DeleteFile(PChar(ReturnFilePath))) then
              MoveFileEx(PChar(ReturnFilePath), nil, MOVEFILE_DELAY_UNTIL_REBOOT);
          end;

        end;
  //...
Sylvain

DEV: Delphi 7, Win XP, Office 2007
TARGET: Win XP-7-2012 x64, Office 2007-2010
Posted 04 Aug, 2014 08:47:47 Top
iubito




Posts: 16
Joined: 2014-06-19
Ohoh, I found a trick
instead of :
1) closing the workbook (the old file)
2) conditionnaly delete it
3) open the new file

I do :
1) workbook.saveAs(new file)
2) conditionnaly delete the old file
Sylvain

DEV: Delphi 7, Win XP, Office 2007
TARGET: Win XP-7-2012 x64, Office 2007-2010
Posted 05 Aug, 2014 09:06:51 Top
Andrei Smolin


Add-in Express team


Posts: 14114
Joined: 2006-05-11
Sylvain,

Another way is to open the new file before you close the old one.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Aug, 2014 09:12:09 Top