I want to gather a small portion of an Excel spreadsheet and write the data to a comma delimited file for import into another program. So far, I have formatted the demilited records in new cells on sheet two. But I can not seem to find a way to save just these cells into a plain text file as I have already formated them as text strings (one delimited record per line).

[Save as CSV only allows me to save single sheet spreadsheets and then it saves the entire sheet. Save as .PRN file saves the whole sheet and not just the selected cells.] Suggestions?

As with most computer challenges, someone may have a one-step solution or Excel command I missed but in lieu of that, here are a few suggested approaches to head you in the right direction:

1. Create a macro to set up the range of cells that you wish to save. Next, copy this range to a new worksheet. Then, save this worksheet to a delimited text file or CSV. Since it will only contain the cells you have designated, you can now live with the limitation that saves the worksheet in its entirety.

2. Write a macro that loops through the range of cells, and writes out the records, one by one, to a text file. You can open a channel to a text output file and use Write# or Print#. The advantage of writing a text file is you can control formatting of columns and delimited characters.

3. Similar to approach #2, from within Excel, you can use ADO or DAO to loop through the range of cells and write out the records to a database table in Access. You would use the AddNew method to add each row of Excel data to the Access table as you progress through the cell range. Once in Access, the data can be formatted, retrieved or edited in any way you see fit.

4. The last approach is similar to #3 except that you simply dump the entire worksheet into Access. There are many ways to do this. You can save as a CSV and then call Access to import the data. You can just have Access use the TransferSpreadsheet/Database methods to bring in the worksheet directly from Excel. Or you can use ADO or DAO from within Excel to write the data to Access, one record at a time. Once the data is in Access, you just use a select query or filter or a delete-action query to remove the unwanted records.

If you need clarification on any of these approaches, let me know.

Joe G

Thanks Joe! I think that suggestion #2 is what I will try. It will avoid creating the second worksheet and give me control over which records to write out to the file exactly as I please. I am familiar with VB code and looping, but I have never looped through worksheet rows before.

By chance, do you have an example of to reference the row and column variables to loop through?

And, is opening a file for output the same in an Excel Macro as it is in VB6? I greatly appreciate any help you can offer. Thanks.

Sure! Here's a sample. Also see my comments after the code below...

First, I created a worksheet.

Then on sheet1, I typed in some sample data from A1 to B4. I put the first names in column A and the last names in column B:

john smith
james johnson
susan jones
mary hanson

Next, in VBA, I inserted a module.
In the module I placed the following code:

Option Explicit
Private Const strPath As String = "C:\Windows\Desktop\"
Private Const strFileName As String = "File1.txt"
Public Sub LoopThroughCells()

Dim objWorksheet As Excel.Worksheet
Dim objRange As Excel.Range
Dim lRowCount As Long
Dim lColumnCount As Long
Dim lCurrentRow As Long

Open strPath + strFileName For Output As #1

Set objWorksheet = Application.Sheets("Sheet1")

With objWorksheet

.Activate
.Range("A1").Activate
Set objRange = .Cells.CurrentRegion

lRowCount = objRange.Rows.Count
lColumnCount = objRange.Columns.Count

lCurrentRow = 1
For lCurrentRow = 1 To lRowCount
Write #1, .Cells(lCurrentRow, 1),'a comma = continued record
Write #1, .Cells(lCurrentRow, 2)
Next

End With

Close #1

Set objRange = Nothing
Set objWorksheet = Nothing

End Sub

Comments:
There are many ways to select cells and define ranges. Some people just create a range and iterate through the cells from left to right, row by row, such as A1, B1, A2, B2, etc.

The main thing to remember is that a range simply refers to a collection of cells, rows, columns, or anything else you wish to define. Once you give the parameters of the range, you can select any element you wish from it.

For example, my range had 4 contact names over 8 cells. I could have selected the 7th item in the range if I wished to pull this out.

Also, I chose CurrentRegion which selects all non-blank cells in a group of cells. But if you know explicity that you only want to return data from a particular starting and ending row, you don't need to use CurrentRegion.

Oftentimes I've also used the starting and ending arguments in the range command or used the offset method.

It may pay to use generic starting and ending points to keep things simple. I don't use the word "relative" because that refers to cell addresses

For example, your range may go from A20:A40. If you wanted the first row, you don't get A1. You get A20 because as far as the range is concerned, that is the first row. That is why setting up your variables and loops carefully ensures you don't pull data from the wrong location.

Now if you wanted data from only certain cells, all you need do is to test the value of the cell for whatever property you are testing for and if it satisfies your criteria, you could choose to include that data in the file you are writing out.

One last gotcha to watch for: If you do anything that changes the current range (such as deleting a row on the worksheet, or using an offset from your current position) be mindful that your range may not be the same - it may have collapsed or expanded. You may want to redefine your range by setting it again.

Let me know if you have any other questions. Happy Holiday!

Joe G

BTW: Here's the output contained in File1.txt...

"john","smith"
"james","johnson"
"susan","jones"
"mary","hanson"

You should experiment with using Write# and Print# which give slightly different results.