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.

 

[sijpie]

Range("A1:20").copy

will copy the rnage A1:A20 of the active sheet to the clipboard.

 

[jfarc]

Thanks for the try sijpie, but my real question was how to "Save this copied Range() to a .txt file".

I was hoping there was some nice 1 line command similar to the VBA example I gave in my first post.

I ended up having to do the following: Copy the range to an already added new Sheet I called 'CopytoFile'; then copy this sheet to a new workbook; then Save the entire new workbook to a .txt file; then close the new workbook. Then to clean up, I position the original workbook back to the main Sheet called 'Master' and Save/Close it. It also loops through creating multiple files is the reason for the For/Loop.

Dim i As Integer
    Sheets("MASTER").Select
    Range("Z1", Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("CopytoFile").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("CopytoFile").Copy
    Application.DisplayAlerts = False
    For i = 1 To 100 Step 1
        If Dir("C:\Post" & i & ".txt") = "" Then
            ActiveWorkbook.SaveAs Filename:="C:\Post" & i & ".txt", FileFormat:=xlText, CreateBackup:=False
            ActiveWindow.Close SaveChanges:=False
        Exit For
        End If
    Next i
    Application.DisplayAlerts = True
    Sheets("MASTER").Select
    ActiveWindow.Close SaveChanges:=True

An awful lot of code to take the place of what should be 1 line. Oh well.

 

[wigi]

Hi

Here is an example with shorter code. It will output the currentregion of cell A1 to a file.

Sub f()
    For Each v In [A1].CurrentRegion.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

Wigi

 

[Norie]

JFarc

There is no SaveDisplayMemory method I can find.

What exactly do you want to do?

As far as I know if you save a worksheet as a text file then it's only going to have the data that is in that worksheet.

 

[wigi]

As far as I know if you save a worksheet as a text file then it's only going to have the data that is in that worksheet.

But this doesn't mean that everything in the sheet should also be part of the text file

 

[Norie]

Wigi

But if all there is in the worksheet is the data then surely that's all that's going to be saved.

 

[wigi]

Wigi

But if all there is in the worksheet is the data then surely that's all that's going to be saved.

True.

But make the trade-off: either having code to copy the sheet to a new file and save as a text file.

Either (almost) directly outputting to a text file.

The second option will be a bit more difficult, but it will be faster in many cases.

 

[jfarc]

wigi,

I'm getting a Run-Time error 13 - Data mismatch on the 'c0 = c0....' line in your code. I removed the ' & vbCrLf', but still same error.

Norie,

I was just trying to find a simple way to save a currently Selected Range() of cells in the current workbook to a text file without going through the rigmarole of 'Copying the Range to a separate sheet - Saving entire sheet to file, etc, etc, etc. There are other things on the original sheet I do not want to save - only the selected cells.

I gave the example of the VBA line of code in the other software I use because it does everything in one line, although it Saves the current display memory of the terminal emulator. I would have expected there to be a similar command line to accomplish what I want in a single line of code.

 

[Norie]

Why would you expect something similar to be available in Excel VBA?

There's a lot of things I would like to see in VBA, my personal favourite being 'BringMeTheMoonOnAStick' - but sadly not available.

Seriously though, I don't see why copying a range to a new worksheet/workbook and then saving the data to a worksheet.

It might not be possible in one line but a few lines of code might do.