fish has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I need to convert an excel-sheet from XLSX to XLS. I have Excel 2007 installed on my machine so I thought it must be possible to convert an xlsx-sheet to xls by using Excel2007 via OLE.

To be honest, I have no idea how to tell my Win32:OLE-Excelobject which format it should save.

Does somebody know how to do this?

Best regards,
fish

Replies are listed 'Best First'.
Re: Convert XLSX to XLS with OLE?
by Gangabass (Vicar) on Aug 04, 2009 at 09:20 UTC

    Here is how you can find solution by yourself:

    1. Start Excel
    2. Press Alt+F11 to start Microsoft Visual Basic
    3. Press F2 to start Object Browser
    4. Enter "SaveAs" in Search Text box and press Enter to search for this method
    5. Select any SaveAs method for class Workbook in the search results to get it params
    6. As you see it need FileFormat so enter this text in the Search Text box and press Enter
    7. Select XlFileFormat and get complete list of all avaiable formats
[reply]
      Thank you for this detailed description. To be honest I did not know the Object Browser before. This will probably help me a lot in future.

      Best regards,
      fish
[reply]
Re: Convert XLSX to XLS with OLE?
by roboticus (Chancellor) on Aug 04, 2009 at 12:57 UTC
    fish:

    Excel is pretty good about reading a file with the "wrong" extension, so if you're just trying to satisfy a naming convention, you might just try to rename the file with an ".xls" extension and see if Excel is happy with it.

    Of course, if you really want it in a binary ".xls" format, put Excel in macro recording mode and write the file in the format you want. Then you can edit the macro and see what functions it calls on the OLE object and what parameters it uses, which is probably enough to tell you what you need to know.

    ...roboticus
[reply]

Back to Seekers of Perl Wisdom