Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Scottsdale,AZ
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel Save as CSV Question

    I need to maintain a list of 425 e-mail address in a CSV file. I'm using an Excel spreadsheet to maintain the list.

    When I save as CSV from 1 column with all the e-addresses, I get a CSV file with line breaks after each address.

    In order to get a CSV file with only a comma separating the addresses, I have to put all the addresses in 1 row.

    Is it possible to get the same desired result (only commas separating) from saving as CSV when the the addresses are in 1 column.

    TIA for your help.

  2. #2
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,072
    Thanks
    0
    Thanked 259 Times in 248 Posts
    I don't get the point of doing it that way but sounds as if you want to have a spreadsheet containing only a single cell which would grow to the right as addresses were added. That will be a very long row. It probably could be done by deleting all the columns from B through the last to the right and all the rows from 2 to the bottom which would leave only the cell in Column A Row 1.

    After further consideration I think the data in that single cell would be easy to copy and paste into the To, CC or BCC address box in a message. But, and it's a big BUT, if that were to be the scenario it might run afoul of an ISPs SPAM filter or bulk messaging rules.
    Last edited by Berton; 2015-05-19 at 00:52.

  3. #3
    jwoods
    Guest
    I think a single row with 425 columns (now) will soon become un-managable.

    Thinking about it in a different way, if you create your spreadsheet using a common address book format, the CSV file you generate from that can be imported into many email programs.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 980 Times in 910 Posts
    If you put the addresses in individual cells on one row you will be able to achieve the result you require without making the list unwieldy.

    cheers, Paul

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Kcinaz,

    The following code placed in a standard module will create a CSV file from your column of email addresses. When viewed in Notepad it will be comma delimited instead of stacked with line breaks. You will need to change the path and the name to the name of the csv file you wish to create.

    HTH,
    Maud
    Kcinaz1.png

    kcinaz2.png

    Code:
    Public Sub ConvertToCSV()
    Application.ScreenUpdating = False
    Dim I As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To LastRow
        Cells(2, I) = Cells(I, 1)
    Next I
    Range("A3:A" & LastRow).ClearContents
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\Maudibe\Desktop\kcinaz.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..here's my version:
    Code:
    Sub transposeToCSV()
    [a1].CurrentRegion.Copy
    [b1].PasteSpecial Transpose:=True
    [a1].EntireColumn.Delete
    [a1].Select
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileFormat:=xlCSV
    Application.DisplayAlerts = True
    End Sub
    This will use the same filename as the source email file, but with a .csv extension.
    My routine doesn't close the csv file, so you can see the result (but could if required).

    I have attached a sample file of 500 dummy email records for testing.

    zeddy
    Attached Files Attached Files

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Zeddy,

    Yes, much cleaner. Nicely done!

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-05-19)

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    I stumbled on the line..
    ActiveWorkbook.SaveAs FileFormat:=xlCSV
    ..didn't know you could saveAs without specifying a filename until I tested it!!
    ..but I know now!

    As you well know, in Excel, anything that does the job is a good result - and yours was the first! For that, you should be thanked!

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •