Saving Excel files

ls
ls Member Posts: 33
edited 2012-05-21 in NAV/Navision Classic Client
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

Comments

  • Sog
    Sog Member Posts: 1,023
    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
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • ls
    ls Member Posts: 33
    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
    Sog Member Posts: 1,023
    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
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • ls
    ls Member Posts: 33
    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
    Sog Member Posts: 1,023
    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"
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Hanen
    Hanen Member Posts: 281
    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 :cry::cry::cry:
    Regards

    Hanen TALBI
  • Reciprocity
    Reciprocity Member Posts: 1
    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);

Categories