Saving Excel files

ls

Hello,
I should create a xls file to export data and save it in a given folder. How could I do it? I'm using 5.0SP1 version.

Thanks
ls

Sog

Automation is one approach.
create a form which asks the user the path to the folder
Create an excel app, give it a new workbook.
Give your workbook a name and give it a sheet.
export your data to the sheet.
Save the workbook in the folder

ls

I used automation and Excel Buffer Table (370) to fill my Excel file, then, to save it I wrote:

CREATE(XlApp); (also without creating Xlapplication it returns the same error)
XlWrkBk.SaveAs(NomeFile);

but it says me that the Automation variable has not been instantiated.

I could I solve this problem?
ls

Sog

This is a codesnippet from an codeunit I downloaded here at Mibuso

IF ISCLEAR(XlApp) THEN
  IF NOT CREATE(XlApp,TRUE) THEN
    ERROR(Text000);
XlApp.Visible(FALSE);
XlBook := XlApp.Workbooks.Add(-4167);

This is from the function createbook.
As you can see you forgot to add the workbook to your application

ls

Sorry, but I can't understand, in fact SaveAs is a method in Workbook and not in Workbook and I only can specify, XlApp.Workbooks and not XLApp.Workbook. So, I could add the Workbook to XlApp, but I can't save it. Where is my error?

ls

Sog

xlapp.workbooks is a collection of workbooks.
when you use xlWrkBk := xlapp.workbooks.add(-4167), you let your xlApp create a new workbook and he puts it in the workbooks collection, it also assignes the newly created workbook to your xlWrkBk.
Now you can save your xlWrkBk with the saveAs function.
edit

make sure the SaveAs parameter is something like "C:\map\wrkbk.xls"

Hanen

What should I do if I have to loop on saving Excel File

Repeat

FileName:='C:\'+IdSalesperson+'.xls';
ExcelBuffer.Save(FileName);

Until rec.Next =0;

and This is my Save Function That I created in the ExcelBuffer table:

CLEAR(XlApp);

IF NOT CREATE(XlApp,TRUE) THEN
//2- Create instance Excel
CREATE(XlApp);

XlApp.Visible(FALSE);
XlWrkBk := XlApp.Workbooks.Add;
XlWrkSht:= XlApp.ActiveSheet;

//--- SAVE EXCEL WORKSHEET ---
IF XlWrkBk.FullName = FileName THEN // Same filename
  XlWrkBk.Save
ELSE BEGIN
  IF FILE.EXISTS(FileName) THEN // Forced overwrite, in case the File already exists!
  IF ERASE(FileName) THEN;
  XlWrkBk.SaveAs(FileName);
END;

the problem is that I have to confirm all the time if my excel file exists, if I have 1000 records I'll have to click yes to the message do you want to save the existent file yes no cancel

Reciprocity

Yes, is there a workaround for the confirmation dialog from Excel to save changes? With the exception of cancel it really doesn't matter if I say yes or no to saving the changes. This code exists in a dataport so it immediately tries to use the txt file to import and if I choose cancel it will tell me that the file is already in use. I would like to avoid this dialog prompt if at all possible:

Command := (filepath\filename.xls);
IF WSHell.FileExists(Command) THEN
  WSHell.CopyFile(Command,(filepath1\filename.xls))
ELSE
  ERROR('File does not exist in the (filepath) subdirectory');


xlWorkbooks := xlApp.Workbooks;
xlWorkbooks.Open(Command);
xlApp.ScreenUpdating(FALSE);
xlApp.Visible(FALSE);
xlWorkbook := xlApp.ActiveWorkbook;
xlWorksheet := xlApp.ActiveSheet;

xlRange := xlWorksheet.Range('1:1');
xlRange.Delete();
xlRange := xlWorksheet.Range('AO:AO');
xlRange.Clear();
xlWorkbook.SaveAs((filepath\filename.txt),6);


xlWorkbook.Close;
xlApp.Quit;

WSHell.DeleteFile(Command,TRUE);