Saving only visible cells from Excel to CSV

Question

How can you save only the visible cells of an xls as a CSV? I have a large .xls file 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.

People who maintain the spreadsheet don't want to remove old data (they simply hide it from view). My method of saving to CSV includes all the hidden data side by side with no way to know what was hidden originally. Below is the code I am using; used as the starting point for an answer would make it easier for me to understand:

VBScript

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open("S:\NetowrkFolder\SpreadSheet.xls")
Dim oSheet

If oBook.Sheets.count = 1 Then
  oBook.SaveAs "D:\output.csv", 6
else
  i=1
  aname=split("D:\output.csv",".",-1,1)
  For Each oSheet In oBook.WorkSheets
    fname = aname(0) & "_sheet" & Cstr(i)
    oSheet.SaveAs fname, 6
    i=i+1
  Next
End If

oBook.Close True
oExcel.Quit
WScript.Quit

I'm actually doing this project in AutoIt, below is the AutoIt code:

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()

Answer 1

Perhaps this is too straightforward, but it does the job as you'd like to have it done:

Sub Macro1()
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="D:\DOCUMENTS\visible.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
End Sub

The above code is generated using macro recorder. The clue is that we select only visible cells on the worksheet, copy / paste it to new workbook and then save it as CSV. Original data still intact. The rest of code is trivial and may be generated using macro recorder as well.

For more ideas see that SO thread: How do I export only visible cells from Excel to CSV?