Options

Saving Excel files

lsls Member Posts: 33
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

  • Options
    SogSog 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|
  • Options
    lsls 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
  • Options
    SogSog 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|
  • Options
    lsls 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
  • Options
    SogSog 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|
  • Options
    HanenHanen 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
  • Options
    ReciprocityReciprocity 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);
    
Sign In or Register to comment.