We need a Unix-based program to be able to read a lot of information
from a large spreadsheet, every day.

To do this, we need to know how to run some soft of script or macro with Excel.



Currently, every day:
- We download an Excel spreadsheet that is about 800KB in size.
- We open the Excel spreadsheet in Excel.
- We click on File-->Save As, and chose "Save as type: XML Spreadsheet (*.xml)".
- We feed that XML spreadsheet into our Unix-based program, and it is
able to read the information it needs.

We would like to fully automate this process.  I can easily
automatically download the file, but I don't know how to automatically
open it in Excel, and save it as a different type.

We want to actually use Excel to do the conversion.  We trust it, and
know it works.  There are programming libraries which allow an
application to read from an Excel file, convert Excel to CSV, and
convert Excel to XML.  However, due to the size of this Excel file and
some of the bad ways it is written, we cannot get any of the existing
libraries we have tried to properly work.  Some of them have given us
bad data on this complex of an Excel spreadsheet, and others crash or
use up all available memory - 2GB.

We would really like to just automate Excel on a Windows machine to do this for us.

Anyone have any ideas?

See the code here

http://www.meadinkent.co.uk/myxml/XL_to_XML.txt

The description is on this web page.

http://www.meadinkent.co.uk/xl_xml1.htm

Basically you want to copy the code in the spreadsheet so it becomes a
Macro/function in your spreadsheet.

I assume you get an Excel spreadsheet every day that is of the same
name. You want to creat a new spreadsheet that contains above
mentioned macro. you will need to add to the above macro code to read
the data from the downloaded sheet (if the name of downloaded Excel
sheet is same same every day then it is easier or else you would need
code to create the name dynamically.)

You can create a .bat file that will run the macro. You can schedule
the batch file using NT scheduler. I will include the bat file example
in next comment.

You want to convert the file to XML first then import to UNIX. 

Thanks, Let me know if you need more clarification.

Here is another alternative, much simpler, you don't need to mess with
all the Macro business.

you can put this code VB.Net application and compile it to run as you
want it. or based on this you can write code in other language.

Module Module1
    Const SourceExcelFileName = "C:\SourceFolder\SourceFile.xls"

    Sub Main()
        On Error Resume Next
        Dim oExcel As Excel.ApplicationClass
        Dim oBook As Excel.WorkbookClass
        Dim oBooks As Excel.Workbooks

        'Start Excel and open the workbook.
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oBooks = oExcel.Workbooks
        oBook = oBooks.Open(SourceExcelFileName)
' The following line does the trick for you!!
        oBook.SaveAs("C:\FolderName\SaveFilename.XML",
Excel.XlFileFormat.xlXMLSpreadsheet)

        'Clean-up: Close the workbook and quit Excel.
        oBook.Close(False)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
        oBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
        oBooks = Nothing
        oExcel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        oExcel = Nothing
        Exit Sub
    End Sub
End Module

For automation in Windows:

Winrunner.  It's more testing application functionality.  Another
downside is that it's quite expensive.
http://www.mercury.com/us/products/quality-center/functional-testing/winrunner/

Rational Robot.  Never used it.  Have no idea how it works.  I just
know that it's another automation tool. 
http://www-306.ibm.com/software/awdtools/tester/robot/

Macro scheduler.  Used it briefly.  I think it's cheaper than the
other two.  http://www.mjtnet.com/

AutoIt.  Freeware.  Since it's free, I'd use this one.  Not as user
friendly in that you'd need to code the script yourself instead of
recording your own clicks and keystrokes.  So it'll require more
legwork.  But still worth the cost IMO. 
http://www.hiddensoft.com/AutoIt/

How do you compile the following code? I get BC30002 errors.



Module Module1
    Const SourceExcelFileName = "C:\SourceFolder\SourceFile.xls"

    Sub Main()
        On Error Resume Next
        Dim oExcel As Excel.ApplicationClass
        Dim oBook As Excel.WorkbookClass
        Dim oBooks As Excel.Workbooks

        'Start Excel and open the workbook.
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oBooks = oExcel.Workbooks
        oBook = oBooks.Open(SourceExcelFileName)
' The following line does the trick for you!!
        oBook.SaveAs("C:\FolderName\SaveFilename.XML",
Excel.XlFileFormat.xlXMLSpreadsheet)

        'Clean-up: Close the workbook and quit Excel.
        oBook.Close(False)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
        oBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
        oBooks = Nothing
        oExcel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        oExcel = Nothing
        Exit Sub
    End Sub
End Module