Exporting Selected Range to TXT file

K_Man95

Board Regular
Joined
Jul 31, 2002
Messages
158
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

MarkHenri

Board Regular
Joined
Aug 2, 2002
Messages
106
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

Board Regular
Joined
Jul 31, 2002
Messages
158
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

Active Member
Joined
Feb 28, 2003
Messages
324
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

Active Member
Joined
Feb 28, 2003
Messages
324
Never mind..I believe I found that the Chr(9) was causing the issue..i removed it and now the code works perfectly.
 

Forum statistics

Threads
1,147,694
Messages
5,742,671
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top