excel 2007 - how to save files as excel 97

DoomhammerDoomhammer Member Posts: 211
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, AxiomProvis

Comments

  • SavatageSavatage Member Posts: 7,142
    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 Member Posts: 211
    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, AxiomProvis
  • DoomhammerDoomhammer Member Posts: 211
    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
    Martin Bokůvka, AxiomProvis
Sign In or Register to comment.