Hi Oorang,
For my own purposes, I had been meaning to write utility for myself whereby I can grab a range of cells and kick them into a CSV file. Your code prompted me to go ahead and write it. Using your code as a starting point I wrote the following for myself. I'll either add an option to my main FILE menu or an option to my CELL popup to call the function. Thanks for the kick in the pants.
Regards,
Greg<hr /><font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> WriteCSVwithoutFinalCRLF()
<SPAN style="color:#007F00">' Utility to take selected range or a worksheet's USEDRANGE</SPAN>
<SPAN style="color:#007F00">' and write to a CSV file. Simply saving a worksheet as CSV</SPAN>
<SPAN style="color:#007F00">' appends an extraneous CR/LF to the end of the file which</SPAN>
<SPAN style="color:#007F00">' may cause importing programs to erroneously process</SPAN>
<SPAN style="color:#007F00">' an empty line at the end of the file.</SPAN>
<SPAN style="color:#007F00">' Written by Greg Truby, Dec 2005</SPAN>
<SPAN style="color:#007F00">' Original idea from Aaron Bush (Oorang) 11/30/2005</SPAN>
<SPAN style="color:#007F00">' http://www.mrexcel.com/board2/viewtopic.php?t=181925</SPAN>
<SPAN style="color:#007F00">' Required references:</SPAN>
<SPAN style="color:#007F00">' * MS Scripting Runtime</SPAN>
<SPAN style="color:#007F00">' * MS Forms 2.0</SPAN>
<SPAN style="color:#00007F">If</SPAN> ActiveSheet.Type <> XlSheetType.xlWorksheet <SPAN style="color:#00007F">Then</SPAN>
MsgBox "This utility only works with worksheets, not charts.", _
vbCritical, "Aborting"
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">' |--¿xsub--></SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#007F00">' Scripting objects</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> fsoNew <SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject, _
filCSV <SPAN style="color:#00007F">As</SPAN> Scripting.File, _
txtNew <SPAN style="color:#00007F">As</SPAN> Scripting.TextStream
<SPAN style="color:#007F00">' Forms object</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> doClipboard <SPAN style="color:#00007F">As</SPAN> MSForms.DataObject
<SPAN style="color:#007F00">' Excel/Office objects/types</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strRecord <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> intResponse <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, fdSaveAsDlg As FileDialog, rngToWrite As Range
<SPAN style="color:#007F00">' initialize/instantiate</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fsoNew = <SPAN style="color:#00007F">New</SPAN> FileSystemObject
<SPAN style="color:#00007F">Set</SPAN> doClipboard = New DataObject
<SPAN style="color:#007F00">' put the selected range or usedrange into a string</SPAN>
<SPAN style="color:#007F00">' by copy to clipboard and getting from clipboard.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngToWrite = ActiveCell
<SPAN style="color:#00007F">If</SPAN> TypeName(Selection) = "Range" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> rngToWrite = Selection
<SPAN style="color:#00007F">If</SPAN> rngToWrite.Count = 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> rngToWrite = ActiveSheet.UsedRange
rngToWrite.Copy
<SPAN style="color:#00007F">With</SPAN> doClipboard
doClipboard.GetFromClipboard
strRecord = .GetText(1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#007F00">' replace the default delimiters (tabs) with commas</SPAN>
strRecord = Replace(strRecord, vbTab, ",")
<SPAN style="color:#007F00">' create default filename for CSV output</SPAN>
strFileName = Replace(ActiveWorkbook.FullName, ".xls", _
" - " & ActiveSheet.Name & ".csv")
<SPAN style="color:#007F00">' _____Use FILEDIALOG object to retrieve file name to write to</SPAN>
<SPAN style="color:#007F00">' the SAVEAS fd offers built-in overwrite confirmation._____</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fdSaveAsDlg = Application.FileDialog(msoFileDialogSaveAs)
<SPAN style="color:#00007F">With</SPAN> fdSaveAsDlg
.InitialFileName = strFileName
.InitialView = msoFileDialogViewDetails
.AllowMultiSelect = <SPAN style="color:#00007F">False</SPAN>
.FilterIndex = 11 <SPAN style="color:#007F00">' CSV filter</SPAN>
.Title = "Save range " _
& rngToWrite.Address(False, False) _
& " as"
<SPAN style="color:#00007F">If</SPAN> .Show <> -1 <SPAN style="color:#00007F">Then</SPAN>
MsgBox "Canceled per user request.", _
vbCritical, "Process aborted"
<SPAN style="color:#00007F">GoTo</SPAN> CleanUp <SPAN style="color:#007F00">' |--¿xsub?--></SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
strFileName = .SelectedItems(1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#007F00">' write the string back out to a file, sans the last two characters</SPAN>
<SPAN style="color:#00007F">If</SPAN> Dir(strFileName) <> vbNullString <SPAN style="color:#00007F">Then</SPAN> Kill strFileName
fsoNew.CreateTextFile strFileName
<SPAN style="color:#00007F">Set</SPAN> filCSV = fsoNew.GetFile(strFileName)
<SPAN style="color:#00007F">Set</SPAN> txtNew = filCSV.OpenAsTextStream(ForWriting, TristateUseDefault)
<SPAN style="color:#00007F">With</SPAN> txtNew
.Write Mid(strRecord, 1, Len(strRecord) - 2)
.Close
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
CleanUp:
<SPAN style="color:#00007F">Set</SPAN> txtNew = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> filCSV = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fsoNew = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngToWrite = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fdSaveAsDlg = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> doClipboard = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>