Command Line Questions

[brianscottnole]

Hello friends -

I've got a third party vendor application that creates spreadsheets really quite nicely. I've got a need to take some of those sheets, read them electronically, and spit the results back out somewhere else. I've successfully used the Apache POI APIs for this purpose in the past.

However, the spreadsheets that are created by the vendor application cause the POI component to fail when they try to open the workbook with a "DimensionsRecord was not found" error.

The tricky part is that if I manually open the spreadsheet, then save it without making any changes, my POI process has no problems and can read the file fine. I also notice that when I open the spreadsheet, then save it, it losses about half its size on the disk.

So, the entire undertaking is about making it so folks don't have to go and open a spreadsheet. A command line solution to open excel with a particular file doesn't seem to difficult, but what I really need to do is command line to open the file, then save it again, (hopefully) causing the mysterious conversion to take place that allows my automated process to manipulate the spreadsheet.

Any insight is appreciated.

- brian

 

[brianscottnole]

Bump. Bump. Bump. Can anyone help? Gurus? Someone please save me from having to communicate to some strange embedded database with proprietary ODBC drivers that can only be installed at the sewer treatment plant. Please!

brian

 

[Joe4]

Why not create an Excel Macro that opens the offending file, saves it, closes it, and exits the Excel application? If you add that macro code to the Workbook_Open event in Excel, it will be kicked off automatically when that Excel macro file is opened.

You could then use Scheduling software (like Windows Scheduler that comes with Windows) to open the Excel file at your specified date/times, so that the entire process is automated.

 

[brianscottnole]

Hi Joe -

Thank you for your response.

The spreadsheets in question are being generated by an external application, one each day; in other words, there is a brand new spreadsheet to be evaluated every day. Is there a way that I can pass through a macro to be run at workbook_open time as a parameter? My options as far as getting the vendor to change how they do things is very limited (or non existent).

brian

 

[Joe4]

I am not sure I understand. What do you want to send as the parameter?

 

[brianscottnole]

Hi Joe4 -

My apologies for being unclear.

My thought was, considering my target is a new spreadsheet that got created externally daily, it didn't seem like an option for me to alter the spreadsheet to have a macro defined for it at open_workbook time. It did occur to me, however, that it might be possible to submit a macro to be executed for this event via command line. That macro would be, at open time, just save back to the OS, at which point, I have libraries that can read the file.

Maybe my understanding of macros is invalid.

Thanks again for helping out.

brian

 

[Joe4]

I am not sure I follow your work flow or all the external processes that you are talking about, but the Excel macro would NOT be in the file you are wanted to open/save, it is its own separate file. Excel macros can be made to run on other Excel files.

If you open this Excel macro file via command line, you would, in essence, be running the macro with the command line (via Excel).