The following is programming code we frequently use when creating CSV files from Excel.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 |
