exporting data from Excel to a load file
top of page
Search
    • Mar 11

exporting data from Excel to a load file

It's not so easy to export data from Excel to a load file with a quote qualifier and comma delimiter - the format required by many applications. Saving the file in the .csv format will omit the quotation marks:




. . . it's a pain to accurately add in the missing quotation marks by altering the file in a text editor, and Excel doesn't provide an easy way to add them in the export. Luckily, Microsoft has posted a macro here, which will export data from an Excel spreadsheet in the correct format.



Enter the below code in a module for the worksheet with the data:



Select the data on the worksheet that you want to export to a new file. Run the macro and it will prompt you to enter a path for the new file:




A new file with comma delimiters and quotation mark qualifiers appears:



This is the complete code but it is not formatted correctly so use the code at this link or edit the code before running it.


Sub QuoteCommaExport()

' Dimension all variables.

Dim DestFile As String

Dim FileNum As Integer

Dim ColumnCount As Long

Dim RowCount As Long

' Prompt user for destination file name.

DestFile = InputBox("Enter the destination filename" _

& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number.

FileNum = FreeFile()

' Turn error checking off.

On Error Resume Next

' Attempt to open destination file for output.

Open DestFile For Output As #FileNum

' If an error occurs report it and end.

If Err <> 0 Then

MsgBox "Cannot open filename " & DestFile

End

End If

' Turn error checking on.

On Error GoTo 0

' Loop for each row in selection.

For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.

For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.

Print #FileNum, """" & Selection.Cells(RowCount, _

ColumnCount).Text & """";

' Check if cell is in last column.

If ColumnCount = Selection.Columns.Count Then

' If so, then write a blank line.

Print #FileNum,

Else

' Otherwise, write a comma.

Print #FileNum, ",";

End If

' Start next iteration of ColumnCount loop.

Next ColumnCount

' Start next iteration of RowCount loop.

Next RowCount

' Close destination file.

Close #FileNum

End Sub

bottom of page