excel 2007 - how to save files as excel 97

DoomhammerDoomhammer Posts: 180Member
Hello

I have problem with creating excel sheets. I am using excel buffer to fill excel sheet and this code to save prepared excel:
XlApp.UserControl(TRUE);
XlWrkBk.Close(TRUE,FIleName);
XlApp.Quit;
CLEAR(XlApp);
this code will save prepared excel as default XML format.

our partner who receives excel files has some problem with office 2007 format and requests files in older 97 format.
can anybody tell me, how to change excel format?

I tried to modify my code this way:
XlApp.UserControl(TRUE);
XlWrkBk.SaveAs(FIleName,'43'); /// save as excel 97
XlWrkBk.Close(FALSE);
XlApp.Quit;
CLEAR(XlApp);
but it did not work. number 43 I took from this document: http://msdn.microsoft.com/en-us/library/office/bb241279(v=office.12).aspx and I tried it as number too.

any information will be most appreciated :)
thanks in advance.
Martin Bokůvka, Skil Olomouc

Comments

  • SavatageSavatage Posts: 7,134Member
    Perhaps you can introduce them to the compatability pack.
    Then they won't have trouble opening newer versions of files.

    http://office.microsoft.com/en-us/suppo ... 68676.aspx
  • DoomhammerDoomhammer Posts: 180Member
    yes, I know...
    but this is not sending office files to customer with human on other end - it is DB Schenker WMS which processes XLS files automaticaly :(
    at this time we created small VB script which is called after XLSX file creation, but I do not like this workaround
    Martin Bokůvka, Skil Olomouc
  • DoomhammerDoomhammer Posts: 180Member
    I am trying to use this vbscript as workaround...
    If Iľl run it from saved file, then conversion is performed. but from NAV, execution fails...
    _autVBScript.Language := 'VBScript';
    _charCR := 13;
    _txtCR := '';
    _txtCR[1] := 13;
    
    _txtCode := _txtCode + 'dim objShell' + _txtCR;
    _txtCode := _txtCode + 'Set objShell = CreateObject("Wscript.Shell")' + _txtCR;
    
    _txtCode := _txtCode + 'Set objExcel = CreateObject("Excel.Application" )' + _txtCR;
    _txtCode := _txtCode + 'Set objWorkbook = objExcel.Workbooks.Open("' +
                _txtXLSXFile + '")' + _txtCR;
    _txtCode := _txtCode + 'objExcel.Application.Visible = False' + _txtCR;
    _txtCode := _txtCode + 'objExcel.Application.DisplayAlerts = False' + _txtCR;
    _txtCode := _txtCode + 'objExcel.ActiveWorkbook.SaveAs ("' +
                COPYSTR(_txtXLSXFile,1,STRLEN(_txtXLSXFile) - 1) + '"), 56' + _txtCR;
    _txtCode := _txtCode + 'objExcel.ActiveWorkbook.Close' + _txtCR;
    _txtCode := _txtCode + 'objExcel.Application.DisplayAlerts = True' + _txtCR;
    _txtCode := _txtCode + 'objExcel.Application.Quit' + _txtCR;
    _txtCode := _txtCode + 'WScript.Quit' + _txtCR;
    _autVBScript.AddCode(_txtCode);
    _autVBScript.ExecuteStatement('OpenFile()' + _txtCR);
    
    for error message please see attached file.

    any help will be welcomed
    Untitled.png
    19K
    Martin Bokůvka, Skil Olomouc
Sign In or Register to comment.