Export from Excel to text file without line breaks

Question

Hi, I have a regular Excel spreadsheet with only 1 column. I want to export it to a text file with a separator. I am not interested in separating columns, since I only have one. My concern is to export with separators instead of line breaks so that my final result is a simple list like this "item1","item2","item3"... and so on. item1, item2 and item3 were in the same column, in different rows in my Excel file. Is it possible to do this straight from Excel? I can only get the final file with line breaks. Thanks!

Answer 1

You will need to use "VBA" to loop over the cells in that column and print their content to a file.

Answer 2

If you don't want a vba solution and use max. 256 rows you can copy your used cells and paste them transposed to a different sheet in A1.

Now all the values are in a row and you can export/save the sheet as a *.csv. The cell values will be seperated by a semicolon but will not be quoted! Your texteditor should be able to replace ; with " ;".

The VBA loop is the better solution.

Answer 3

You may wish to use ADO and GetString.

Dim cn As Object
Dim rs As Object

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT F1 FROM [Sheet1$]"

rs.Open strSQL, cn

str = rs.GetString(2,,"|")

Further information: http://www.w3schools.com/ADO/met%5Frs%5Fgetstring.asp

Answer 4

If you want to be quick and inelegant, just use the cells to the right to concatenate with separation (semi-colon, say). Assuming your data starts in A2, put this in B2:

=A1

And put this in B3 and fill down:

=B2&";"&A3

The text you want is in the bottom cell of the B column.