Here's what I use to (semi-)intelligently export all worksheets in a workbook to CSV files. In VBA:
Sub BulkCSVExport()
' Export all worksheets to .CSV files.
' Surround text (not numbers!) with double quotes.
' Handle text that contains double quotes, as in: 18" X 11" X 11
+"
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim st As Integer
Dim strTemp As String
Dim wbk As Workbook
Dim sht As Worksheet
Dim intSheetCount As Integer
Application.ScreenUpdating = False
Set wbk = Application.ActiveWorkbook
For intSheetCount = 1 To wbk.Sheets.Count
Set sht = wbk.Sheets.Item(intSheetCount)
sht.Activate
DestFile = wbk.Name & "." & sht.Name & ".CSV"
FileNum = FreeFile()
On Error Resume Next
Open DestFile For Output As #FileNum
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
On Error GoTo 0
ActiveSheet.UsedRange.Select
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
If IsNumeric(Selection.Cells(RowCount, ColumnCount
+)) Then
Print #FileNum, Selection.Cells(RowCount, _
ColumnCount).Value;
Else
'
strTemp = Selection.Cells(RowCount, ColumnCoun
+t).Text
' Fix double quotes, lest they confuse the .CS
+V format
If InStr(strTemp, CStr(Chr$(34))) <> 0 Then
st% = 1
While st% <= Len(strTemp) And InStr(st%, st
+rTemp, CStr(Chr$(34))) > 0
st% = InStr(st%, strTemp, CStr(Chr$(34))
+)
strTemp = Left$(strTemp, st%) & CStr(Chr
+$(34)) & Mid$(strTemp, st% + 1)
st% = st% + 2
Wend
End If
'
Print #FileNum, CStr(Chr$(34)) & strTemp & CSt
+r(Chr$(34));
End If
If ColumnCount = Selection.Columns.Count Then
Print #FileNum,
Else
Print #FileNum, ",";
End If
Next ColumnCount
Next RowCount
Close #FileNum
Next
Application.ScreenUpdating = True
End Sub
Modifications, calling from Perl, and diffing the output are all left as exercises for the reader.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.