Exporting Selected Range to TXT file

[K_Man95]

Can someone PLEASE take a look at this code and tell me what I am doing wrong???? Here is the history, I can select to desired Range... NO PROBLEM.... I can even create the File I want to save the data in selected Range.... BUT... I can not get my data to paste into that newly created file. Can someone please help.

Sub Selected_Range()

Dim wsSheet As Worksheet
Dim rnArea As Range

Sheets("Sheet2").Select

Set wsSheet = ThisWorkbook.Worksheets("Sheet2")

With wsSheet
Set rnArea = .Range(.Range("D3"), Range("D" & .Range("D65536").End(xlUp).Row))
End With

rnArea.Select
Selection.Copy


ColFileName = "A:\ShaIn.txt"

Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=ColFileName, FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close

End Sub

I have even tried

'Open "C:\Documents and SettingsmayfieldDesktopSha1 Text FilesSha_In.txt" For Output As #1

and gone that route... but again, the data will not copy. And I have searched this board for help that I could understand... but none of it seems to be what I am looking for. Maybe I have overlooked something.

HELP!!!?????!!!!
This message was edited by K_Man95 on 2002-08-29 17:30

 

[MarkHenri]

I wrote this based on your title "Exporting Selected Range to Text File". It seemed like a fun idea though your example was all about creating a new worksheet and copying the data into it.<pre>Sub ExportRangeToTextFile()

'get the file name to write to
Dim FName As Variant
FName = Application.GetSaveAsFilename("ExcelData.txt", _
"Text File (*.txt),*.txt,ASCII File (*.asc),*.asc", _
1, "Export Range to Text File")
If FName = False Then
MsgBox "You didn't enter a file name."
Exit Sub
End If

'open the file for writing
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(FName, True)

'write the data from the range
Dim row, col
row = 0
col = 0
Dim cell As Range
For Each cell In Selection.Cells
If cell.row <> row Then
If row <> 0 Then
f.write Chr(13) & Chr(10)
End If
row = cell.row
End If
f.write cell.Value
f.write Chr(9)
Next

'close the file and release objects
f.Close
Set fs = Nothing

End Sub

</pre>
By the way, I did this in Excel XP.

 

[K_Man95]

Thanks Mark, I will give that a try. I have something working right now... but I it has one bug left. I will give yours a try and see if that fixes it.... do you think it will matter if I am using XL 2000?

Also, I have written a segment to check to see if the file already exists and then if it does to AUTOMATICALLY overwrite the existing file ... WITHOUT promting the user..... but for some reason..when I get to that portion of the Module, it seems as if Excel/Microsoft office/Windows (something) performs that check already. Ya know, like trying to overwrite any file in windows, it tells you "File already exists. Are you sure you want to overwrite existing file?" and then you have to choose yes or no. Well... I don't want that... and I don't need that option. I want the file to overwrite automatically as I will be creating the same file with different data for X amount of times.. one at a time. Can this be done? Any suggestions?

Thanks again for the code.... I will give it a try.

 

[Captain Smith]

I used MarkHenri's code above and it works great. Only problem is that the data exports with an extra tab at the end of each records. Is there a way for the data to export without this extra tab? Thx

 

[Captain Smith]

Never mind..I believe I found that the Chr(9) was causing the issue..i removed it and now the code works perfectly.