vba 将使用的范围复制到文本文件

Copy used range to text file

提问by Vihar

I want to:

我想要：

• Copy the used range of a sheet called "Kommentar"
• Create a ".txt" file ("Kommentar.txt") in the same directory as ThisWorkbook
• Paste the previously copied used range
• Save the ".txt" file

• 复制名为“Kommentar”的工作表的使用范围
• 在与 ThisWorkbook 相同的目录中创建一个“.txt”文件（“Kommentar.txt”）
• 粘贴之前复制的使用范围
• 保存“.txt”文件

I have:

我有：

Sub CreateAfile()

Dim pth As String
pth = ThisWorkbook.path
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dim a As Object
Set a = fs.CreateTextFile(pth & "\Kommentar.txt", True)
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Kommentar")

Dim rng As Range
Set rng = sh.UsedRange
a.WriteLine (rng)
a.Close

End Sub

I get

我得到

run-time error '13' Mismatch

运行时错误“13”不匹配

In line a.WriteLine (rng)the function doesn't accept range to be written.

在行a.WriteLine (rng)不接受范围的功能被写入。

回答by Olle Sj?gren

Since your range is probably made up of several cells, you have to loop through them to get all the text into a string variable. If you use a Variant variable you can copy the values and automatically get an array with the correct dimensions of all the data in the cells, then loop it and copy the text:

由于您的范围可能由多个单元格组成，您必须遍历它们才能将所有文本放入字符串变量中。如果您使用 Variant 变量，您可以复制值并自动获取具有单元格中所有数据的正确维度的数组，然后循环它并复制文本：

Function GetTextFromRangeText(ByVal poRange As Range) As String
    Dim vRange As Variant
    Dim sRet As String
    Dim i As Integer
    Dim j As Integer

    If Not poRange Is Nothing Then

        vRange = poRange

        For i = LBound(vRange) To UBound(vRange)
            For j = LBound(vRange, 2) To UBound(vRange, 2)
                sRet = sRet & vRange(i, j)
            Next j
            sRet = sRet & vbCrLf
        Next i
    End If

    GetTextFromRangeText = sRet
End Function

Call the function in your code by replacing the a.WriteLine (rng)line with the following:

通过将a.WriteLine (rng)行替换为以下内容来调用代码中的函数：

Dim sRange As String
sRange = GetTextFromRangeText(rng)
Call a.WriteLine(sRange)

回答by Eddie

Not sure you can do that. I believe you would have to write it out line by line.

不确定你能做到这一点。我相信你必须一行一行地写出来。

Here is an alternative option.
Rather than use the FSO, you could just try saving the sheet as a .txt file. Here's some sample code. Credit should goto http://goo.gl/mEHVx

这是一个替代选项。
您可以尝试将工作表另存为 .txt 文件，而不是使用 FSO。这是一些示例代码。信用应该转到http://goo.gl/mEHVx

Option Explicit

'Copy the contents of a worksheet, and save it as a new workbook as a .txt file
Sub Kommentar_Tab()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim fName As String

'References
Set wbSource = ActiveWorkbook
Set wsSource = ThisWorkbook.Sheets("Kommentar")
Set wbDest = Workbooks.Add

'Copy range on original sheet
'Using usedrange can be risky and may return the wrong result.
wsSource.UsedRange.Copy

'Save in new workbook
wbDest.Worksheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

'Get file name and location
fName = ThisWorkbook.Path & "\Kommentar.txt"

'Save new tab delimited file
wbDest.SaveAs fName, xlText

wbDest.Close SaveChanges:=True

End Sub

回答by Trí X

Let's say yourRangeis the range you want to copy as string.

假设yourRange是您要复制为字符串的范围。

1. Use yourRange. Copy to copy it.
2. After you copied it, Excel saves the text value to the clipboard. Cells in a row separated by tabs, and every row ends with an enter. You can use DataObject's GetFromClipboardand GetTextmethod to save it to a string variable.
3. Use CreateTextFileto save it to a file.

1. 使用yourRange. 复制以复制它。
2. 复制后，Excel 会将文本值保存到剪贴板。一行中的单元格由制表符分隔，每一行都以回车结束。您可以使用 DataObject 的GetFromClipboardandGetText方法将其保存到字符串变量中。
3. 使用CreateTextFile将其保存到一个文件中。