Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2008
    Location
    Minnesota, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    .csv export not working (Excel 2003)

    I'm seeing a problem in Excel 2003 with exporting an Excel spreadsheet to .csv format. I can make a spreadsheet with header fields across the top row - say 8 fields.
    Now I have say 300 records - first column down filled then all other fields blank except for the fourth column which I have filled down with data.
    Then I save the file in .csv format.
    When I open this in notepad only the first fifteen lines of the file contain the comma's representing all blank fields - from there on down it stops listing commas for everything after the fourth column in records sixteen down.
    I can do this fine on a pc with Excel 2007 - however the users that need this functionality are all on 2003. Anyone know of a fix?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: .csv export not working (Excel 2003)

    Welcome to Woody's Lounge!

    This is a very old problem that has existed since Excel version 3 for OS/2 - see Column delimiters are missing in an Excel spreadsheet that is saved as text for an explanation and a clumsy workaround.

    An alternative would be to use a macro to write the .csv file - post back if you want to go that way.

  3. #3
    New Lounger
    Join Date
    Nov 2008
    Location
    Minnesota, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .csv export not working (Excel 2003)

    If you could educate me on how to write a macro that would be great Hans!

    - Jim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: .csv export not working (Excel 2003)

    One of our WMVPs, Legare Coleman, has written a tutorial on creating a first macro and on how to store macros so that they will be available in all workbooks: <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post>. I'd take a look at it first.
    You can then copy the following macro into a module in Personal.xls:
    <code>
    Sub Export2CSV()
    Dim varFile As Variant
    Dim f As Integer
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long
    Dim lngMaxCol As Long
    Dim strLine As String
    Dim strSeparator As String

    On Error GoTo ErrHandler

    varFile = Application.GetSaveAsFilename("*.csv", "CSV files (*.csv),*.csv")
    If VarType(varFile) = vbBoolean Then
    MsgBox "You didn't specify a filename", vbExclamation
    Exit Sub
    End If

    lngMaxRow = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    lngMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    strSeparator = Application.International(xlListSeparator)

    f = FreeFile
    Open varFile For Output As #f

    For lngRow = 1 To lngMaxRow
    strLine = ""
    For lngCol = 1 To lngMaxCol
    strLine = strLine & strSeparator & Cells(lngRow, lngCol).Text
    Next lngCol
    Print #f, Mid(strLine, 2)
    Next lngRow

    ExitHandler:
    On Error Resume Next
    Close #f
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    When you have done that, you can run the macro by selecting Tools | Macro | Macros... in Excel (or by pressing Alt+F8), selecting the Personal.xls!Export2CSV macro in the list and clicking Run.

  5. #5
    New Lounger
    Join Date
    Nov 2008
    Location
    Minnesota, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .csv export not working (Excel 2003)

    Hans, you are fantastic - works like a charm! Thank you very much!!!
    Have a great weekend and Happy Holidays!

    - Jim

Posting Permissions

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