I am trying to create a function that will automatically export a range of data from a workbook to a text file. As a user closes this XL book, I want a predetermined range to append to a text file. I modified some code from Microsoft and it works great except it overrides the data currently in the text file. Here is the code:
>>>>>>>>>>>
Sub QuoteCommaExport()
‘ Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
‘ Select the Range to Export.
Range(“A1:B9”).Select
‘ Target the Export file.
DestFile = “C:Datatest.csv”
‘ 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
>>>>>>>>>>>>
As indicated, if I do it more than once, the data exported the first time are overwritten. Can I modify this code to append or is there other code I should use?
Thanks!
