In Excel - Can I save a range of cells as a csv file ?

[mspectre]

We receive an Excel file daily from another
agency. The file is a spreadsheet that includes
header ( titles, column headings, etc. ) and
footer (totals, etc.) information.

I need to save this file as a csv file for use in
another application. However I have only been able
to save the entire file,
including header and footer info.

How can I specify a range of cells to save to the
csv file. I have tried hi-liting the range, but it
still writes everything to the csv file. Clarion 5.5 ; and 5; and 4 experience

 

[dsi]

I have two possible suggestions:

1. Copy the selected data to a new worksheet and save as a csv file.

2. If the format is constant, you could write a quick VBA program to automatically export the data for you. If you are interested in this method, just let us know.

 

[JVFriederick]

If you are willing to put up with some "error" messages each day, create a separate worksheet, reference your data, and save the new sheet in csv format.

Alternatively, create a formula on a new sheet that concatenates the cells in the desired order and format into a single column. Copy the cell to include the total number of data rows. You can then save the sheet in .TXT format. This will give you more control over the final format.

 

[Neddy]

Alternatively, you can use this macro, which takes the range of cells you've selected, opens a new workbook, pastes the cells into the first sheet, deletes all the rest of the sheets (you can avoid having to use this by ensuring that your workbooks only contain one sheet by default) and then saves the new workbook as a CSV file. You'll no doubt want to change the filename I've used.

Sub SaveAsCSV()

' Macro written 29/08/2001 by Neddy

Dim SheetCount As Integer
Dim Counter As Integer

Selection.Copy
Workbooks.Add
ActiveSheet.Paste
'Gets rid of excess worksheets, which can't be saved in CSV file
SheetCount = Sheets.Count
For Counter = SheetCount To 2 Step -1
Sheets(Counter).Delete
Next

'Saves workbook as xxx.CSV
ActiveWorkbook.SaveAs Filename:="C:\My Documents\Whatever.csv", FileFormat:= _
xlCSV, CreateBackup:=False
End Sub

 

[bryanbayfield]

Perhaps it would just be easier to read the range into a two-dimensional array then use the Open method to write this to a text file?