5

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()
user4157124
  • 2,809
  • 13
  • 27
  • 42
Copas
  • 5,921
  • 5
  • 29
  • 43

1 Answers1

4

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?

Community
  • 1
  • 1
Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • Is this a VBA macro? Because of the way this is done at my work it would be much handier for me if I could do it without adding any macros to the spreadsheet itself (unless this could be done automatically). Thanks for the response! – Copas Mar 04 '13 at 18:48
  • 1
    @Copas did you check the link I suggested? There are several more solutions. But... why did you provide the code on your own iv this is not an option for you?) – Peter L. Mar 04 '13 at 18:49
  • @Copas it is possible to keep that code in another workbook, from which you run macro, open target book and perform the above action. – Peter L. Mar 04 '13 at 18:55
  • The code I gave is not excel code. I could hav eit in a speperate workbook but I'm looking for something I can run on a schedule on a headless server. – Copas Mar 04 '13 at 19:12
  • Standard Windows task Scheduler easily runs macro code from within workbook - there are tons of `how-to` solutions for that in Google. – Peter L. Mar 04 '13 at 19:13