Getting Excel to export CSV with actual commas

Hi reedy2k,

You can manually print the info to a text file. The following code with write the activesheet to C:\myFile.txt

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Output = ""
    For j = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        Output = Output & "," & Cells(i, j).Value
    Next
Output = Mid(Output, 2) & Chr(13)
Print #1, Output
Next

Close #1

End Sub

Cheers,
MalicUK.

ok
so where do i stick that code? And then how do I access it

Hi reedy2k,

From Excel press Alt+F11, which will open the vba editor. Now do Insert -> Module, and paste that code into the window that appears. Press Alt+F11 to return to Excel.

Now in Excel all you need to do is have the sheet you want to output as the active sheet, and do Tools -> Macro -> Macros, select "PrintSheet" from the list and click "Run".

Cheers,
MalicUK.

i tried all this and it created the file. Its the same file as if I just went Save as CSV....i.e., no comma seperation

reedy2k,

The file created is 100% comma delimited, open it in notepad and you will see. The only part that isn't delimited is when it moves to a new row, however this is totally normal in delimited files - they still include line breaks.

If you have used data-> Text To Columns in the session of excel you have open, then excel may automatically apply this to any further data you bring in.

But what you have from that code is what you have asked for - a standard comma delimited text file. If you need something different then you need to explain what you currently have and what you want to get in more detail.

Cheers,
MalicUK.

The original question I asked was "Can I force Excel to seperate by commas?"

The file your script creates contains no commas between the data.

erm, it does...

        Output = Output & "," & Cells(i, j).Value

that line parses a comma (,) between every cell value on each row. When it reaches the end of a row it prints a comma delimited line to the file, and starts a new line.

You really need to open .txt files in notepad, nothing else, to view what they actually contain.

erm, no it doesn't...

whatever you think your code does, it doesn't. I opened myFile.txt in notepad (obviously) and it contains NO commas. Simple as that.

I've done some guess work and I feel the problem is your understanding of what a delimited FILE is. A delimited file will always contain 2 separate delimiters, the column delimiter (comma, tab, whatever) and also a delimiter at the end of every row (line break, semi-colon, etc). This is normal for all delimited files as usually they have to be read and returned into a column/row format. That is a delimited FILE.

If however, you are are looking for a delimited text STRING, this will have a single delimiter between each data item. This is because they don't need to be returned into a 2 dimentional array.

Amended code which gives you is a delimited string is:

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Output = ""
    For j = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        Output = Output & "," & Cells(i, j).Value
    Next
Next

Output = Left(Output, Len(Output) - 1)
Print #1, Output
Close #1

End Sub

Cheers,
MalicUK.

Look at my explaination above. The problem was in how you described your current situation and the required outcomes.

From pure guessing I've worked out you have a vertical list in excel, in one column only. Creating a comma delimited file from this will give no commas, for the reasons I have explained (2 dimentional file vs 1 dimentional string).

I know not everyone here is techies, but experts aren't mind-readers. We do expect that the asker is expecting a delimited file outcome if that is what they ask for.

To save future confusion it is probably worth expanding on your explainations with more detail, even uploading a test file for the experts to actually see.

MUK.

with this new code, I get a text file, with the last email address only, and a comma before it

I am going to apologise now :P

I did miss the "This is the only column I have", so I am sorry.

However there are conflicting messages from the original question, but I would have got there faster if I'd seen that.

MUK.

Gah, should finally do it:

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
    For j = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        Output = Output & "," & Cells(i, j).Value
    Next
Next

Output = Mid(Output, 2)
Print #1, Output
Close #1

End Sub

OK
I think we're getting closer

I now get a txt file with each cell seperated by a comma

However when trying to upload to the newsletter system I get the message:

"Do not use the seperator as a character inside a field."

Any ideas?

Huh? Barely a clue to be honest, it sounds like your data has a comma inside one of the data items?

You seem to be using some specialist software, so I'm guessing it has it's own rules. I'm really going to need an exact (even if it is faked) example of what your data currently looks like, and an exact example of what you are trying to get as an output (i.e. what the software requires as input).

with some research I have found what I need to produce:

http://faq.oneandone.co.uk/web_applications/newsletter_tool/importing_and_exporting_addresses/1.html

youre a star, thats worked. Cheers.

No problems, glad we got there in the end. Just shows how much having an accurate overview helps ;)

Thanks for the grade! :)