Saveing Only Visible Cells From Excel to CSV.

[Skizmata]

Short Version:

How can you save only the visible cells of an xls as a csv?

Longer Version:

I have a large .xls file that contains some data spread over many sheets that I would like to make available to some old UNIX perl scripts. I have some code that will watch the file for a modification, regenerate the csv files and FTP them to where I need them. The problem is the people who maintain the spreadsheet don't want to remove old data, they simply hide it from view for their latter reference. My method of saving to csv (as you would expect it should in most use cases) ends up with all the hidden data and visible side by side with no way to know what was hidden originally. I've did some searching on the web and here and can't find any way (that I'm able to follow) of doing this programmatically. Below is some of the code I am useing. If it could be used as the starting point for an answer it would make it easier for me to understand.

Thanks in advance, you guys are always very helpful.

Local $oExcel = ObjCreate("Excel.Application")
Local $oBook = $oExcel.Workbooks.Open("Y:\NetworkLocation\File.xls")
Local $oWorkSheets = $oBook.WorkSheets
Local $i = 1
For $oSheet In $oWorkSheets
    $oSheet.Auto
    $oSheet.SaveAs(@ScriptDir & '\csv\Sheet' & $i & '.dat', 6)
    $i += 1
Next
$oBook.Close(False)
$oExcel.Quit()

Edited March 2, 2013 by Skizmata

[Skizmata]

Since a couple days have passed without any responses, and since I really like StackOverflow, I posted this question to it. If you are a SO user and want to weigh in there please do!

http://stackoverflow.com/questions/15202862/saveing-only-visible-cells-from-excel-to-csv

[iamtheky]

As long as there is no unhidden data in rows beyond a hidden row, or columns beyond a hidden column, _ExcelReadSheetToArray will not return the hidden items in the resulting array.

Edited March 4, 2013 by boththose