CSV file from Excel with varying width

  • I have been trying any and everything I, as well as anyone in my office, can think of to solve this problem but to no avail.
    I have data in series of four rows, each with differing lengths:
    first row ends in cell A107
    second ends B75
    third ends C45
    fourth ends D17
    Then the series will begin again with alternative data, but in the same four row repeating sequence. This time:
    first row ends in cell E107
    second ends F75
    third ends G45
    fourth ends H17
    When I try to export this data to csv format, it returns commas for each row, as place holders, for all empty cells. I believe this is due to Excel viewing the data as a rectangle, as opposed to the stairstep format which I have. As you can assume, these extra commas are a problem.
    Does anyone know if there is some way to instruct Excel to limit each row to a set width, or any other solution to this problem? Thanks!!

  • Hi! Thanks for the welcome.
    The csv file attached is the ideal output from the 'play' worksheet from the 'play1' workbook.
    The highlighted cells indicate the fields where information maybe inserted, hence I need those places held. You will notice that some of the cells below the highlighted fields also contain data, with no results. When exporting to csv, there are commas which represent these cells as well, ideally there would be none.
    Thanks again for your help.

  • Hi,

    Try this routine.


  • I am speechless. That code is a thing of beauty, you have no idea how grateful I am.
    I appreciate all of your help with this and wish I could find some way to repay you.
    Thanks again.

  • Repeating Rows

    Thanks again for your help, everyone in my office thanks you.
    However, as always, with any solution comes one hundred more questions. Thus I am returning to you for further guidance.
    I was wondering if it were possible to run a similar macro, to perform a similar task, however this time I would like to have the macro perform the same function on the first row, then return the second and third rows repeating only these two rows for each new line entry. Then when there is no new line entries return the line designated 004, which I would place in row 10000, thus it would never be touched.
    I am thinking that I would have to make changes such as these
    Sub MyCSVExport()

    Dim strFilename As String
    Dim strBuffer As String
    Dim strColEnd(4) As String
    Dim lngRow As Long
    Dim intIndex As Integer
    Dim rngCell As Range
    Dim intUnit As Integer
    Dim strQuote As String
    Dim strDataRange As String

    strColEnd(1) = ":CR"
    strColEnd(2) = ":BI"
    strColEnd(3) = ":AW"
    strColEnd(4) = ":O"
    strQuote = """"
    strFilename = ThisWorkbook.Path & "\test.csv"
    intUnit = FreeFile
    Open strFilename For Output As intUnit

    lngRow = 1
    With ActiveSheet
    Do While .Cells(lngRow, 1) <> ""
    intIndex = intIndex + 1
    If intIndex > 3 Then intIndex = 2
    strDataRange = "A" & lngRow & strColEnd(intIndex) & lngRow
    strBuffer = ""
    For Each rngCell In .Range(strDataRange)
    If InStr(rngCell.Value, strQuote) > 0 Then
    strBuffer = strBuffer & strQuote & rngCell.Text & strQuote & ","
    strBuffer = strBuffer & rngCell.Text & ","
    End If
    Print #intUnit, Left(strBuffer, Len(strBuffer) - 1)
    lngRow = lngRow + 1
    End With
    Close intUnit
    End Sub

    However I am unsure how to tell the macro to return row 10000 when there is no more data in 'sheet1'
    Thanks again for your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!