Jan 14

Write to CSV file

The following is programming code we frequently use when creating CSV files from Excel.

Sub WriteToCSVFile()

    'Captures date/time stamp for appending to file name
    strTime = Replace(Format(Now(), "mm-dd-yy hh:mm"), ":", "-")

    'Prompts user to specify a file name
    strFile = Application.GetSaveAsFilename("File Name " & strTime, "*.csv,*.csv", , "Specify File Name")

    'Terminates routine if user clicks Cancel
    If strFile = False Then Exit Sub

    'Ensures text file is closed
    Close #1

    'Opens text file for output
    Open strFile For Output As #1

    'Determines number of rows to write
    numRows = Range("A1").CurrentRegion.Rows.Count

    'Determines number of colums to write
    numCols = Range("A1").CurrentRegion.Columns.Count

    'Loops through all rows...
    For i = 1 To numRows

        '...as well as all except for last column
        For j = 1 To numCols - 1

            'Include semi-colon to prevent Excel from adding a line-break
            Write #1, Cells(i, j).Value;

        Next

        'Write last columnn for given row, omit semi-colon so Write adds a line-break.
        Write #1, Cells(i, numCols).Value

    Next

    'Close text file
    Close #1

    'Notify user
    MsgBox "The upload file " & strFile & " has been created.", vbInformation, "Upload file created"

End Sub

 

Lauren Lummus