Geek asks and answers

Saving only visible cells from Excel to CSV

Short version:

How can you save only visible xls cells as csv?

Longer version:

I have a large .xls file that contains some data spread over many sheets that I would like to make available for some old perl UNIX scripts. I have code that will monitor the file for modification, regenerate the csv files and send them FTP to where I need them. The problem is that people who support the spreadsheet do not want to delete old data, they just hide it from view for their last link. My save method in csv (as you would expect, in most cases of use) ends with all hidden data and visible side by side, not being able to find out what was hidden initially. I did some searches on the Internet and here and cannot find a way (which I can do) to do this programmatically. Below is the code I'm using.If it could be used as a starting point for an answer, it would be easier for me to understand.

Thanks in advance, you guys are always very helpful.

Below is a few VBScript . I found that does this work, but includes all the data (and not just the visible data).

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 really do this project in AutoIt below, this is the AutoIt code that I use to do the same. Unfortunately, it still has all the hidden data.

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()
+5
source share
1 answer

It may be too simple, but it does the job the way you would like to do it:

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

. , , / , CSV. . .

., SO: Excel CSV?

+4

More articles:


All Articles