Copy Selected Range() & Save to .txt file

[jfarc]

I also use VBA in a terminal emulation program that I use the following command to 'screen scrape' the current display memory and quickly Save it to a .txt file:

.SaveDisplayMemory "C:\File1.txt", rcOverwrite

What I want to do in Excel is, Copy the current selected Range() and Save it to a .txt file.

I know how Save the current Sheet to a .txt file, but can't figure out how to Save just a Range() of cells.

 

[wigi]

Try:

Sub f()
    For Each v In Selection.Rows
        c0 = c0 & vbCrLf & Join(Application.Transpose(Application.Transpose(v)), "-")
    Next
    Open "C:\Wigi.txt" For Output As #1
    Print #1, c0
    Close #1
End Sub

 

[jfarc]

wigi - Sorry, still getting the same Error 13 even with new code update.

Norie - I'm just all for efficiencies. The code I came up with does do the job, just thought there should be easier way.

 

[wigi]

Please post the layout of your data here.

 

[GTO]

Hi all,

Just curious, do you have (and/or are selecting) only one column of data?

Mark

 

[jfarc]

This is a list of data starting in cell (Z1):

2
40
8W2010
02-26-10
02/26/10 - WIP BBKS

24700.2700.21506.72231.RTH - WIP
32470.-2700.72231.0.RTH - WIP
24700.2500.21494.74100.RTH - WIP
24700.10000.21494.74100.RTH - WIP
24700.10000.21494.74100.RTH - WIP
24700.8750.21494.74100.RTH - WIP
24700.2500.21494.74100.RTH - WIP
24700.10000.21494.74100.RTH - WIP
24700.6250.21497.74100.RTH - WIP
24700.1250.21498.74100.RTH - WIP
24700.10250.21498.74100.RTH - WIP
24700.7500.21501.74100.RTH - WIP
24700.2250.21501.74100.RTH - WIP
24700.11250.21504.74100.RTH - WIP
24700.750.21510.74100.RTH - WIP
24700.3750.21513.74100.RTH - WIP
24700.750.21516.74100.RTH - WIP
24700.7500.21523.74100.RTH - WIP
24700.3750.21529.74100.RTH - WIP
32470.-99000.74100.0.RTH - WIP
24700.20805.21447.74238.RTH - WIP
24700.6840.21447.74238.RTH - WIP
24700.-11400.21479.74238.RTH - WIP
24700.42750.21479.74238.RTH - WIP
24700.2850.21479.74238.RTH - WIP

I do have the cells 'Z1:Z31' Selected when I run the Macro. I am on Excel2002 running Windows XP on a Dell Latitude D820 Laptop.

When the macro runs and errors out, I choose [Debug] and the 'c0 = c0...' line of code is highlighted and the error msgbox says 'Run-Time error 13 - Data mismatch'.

During the Debug, I can cursor over the (v) in this line of code and it shows the data in Cell Z1 as '2' is in this variable.

 

[wigi]

You did not state that you had only 1 column. Then, it's nearly a oneliner.

Sub f()
    Open "C:\Wigi.txt" For Output As #1
    Print #1, Join(Application.Transpose(Selection), vbCrLf)
    Close #1
End Sub

Wigi

 

[jfarc]

You couldn't get it down to just one line, huh?

Works beautifully. It's exactly what I was looking for.

I do see that the 'Open' command line does create the file and if the file already exists, it deletes it first. This is fine for this project.

But, is there a command line/option I can use that will append to the file if it already exists? I have other projects that need this to happen.

 

[wigi]

Open "C:\Wigi.txt" For Append As #1

 

[jfarc]

You did not state that you had only 1 column. Then, it's nearly a oneliner.

Sub f()
    Open "C:\Wigi.txt" For Output As #1
    Print #1, Join(Application.Transpose(Selection), vbCrLf)
    Close #1
End Sub

Wigi

Hello, I have an additional question on this original piece of code this was supplied by wigi. New thread - http://www.mrexcel.com/forum/excel-...de-when-single-cell-selected.html#post4464901