从Excel电子表格中的每一行创建文本文件 [英] Create text Files from every row in an Excel spreadsheet

问题描述

我需要帮助，在名为工作表的Excel表格中的每一行中创建单独的文本文件。我希望文本文件以列A的内容命名，列BG为内容，最好在文本文件中的每列之间双重硬返回，因此每列在它们之间将有一个空行。

I need help creating separate text files from each row in an excel spread sheet called "worksheet". I want the text files to be named with content of Column A, with columns B-G being the content, preferably with a double hard return between each column in the text file, so each column will have a blank line in between them.

这是可能吗？我该怎么做谢谢！

Is this possible? How would I go about it. thanks!

推荐答案

@ nutsch的答案是完美的，应该在99.9％的时间工作。在FSO不可用的罕见情况下，这里是一个没有依赖关系的版本。因此，它要求源工作表在内容部分中没有任何空白行。

@nutsch's answer is perfectly fine and should work 99.9% of the time. In the rare occasion that FSO is not available, here's a version that doesn't have a dependency. As is, it does require that the source worksheet doesn't have any blank rows in the content section.

Sub SaveRowsAsCSV()

Dim wb As Excel.Workbook, wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim r As Long, c As Long

    Set wsSource = ThisWorkbook.Worksheets("worksheet")

    Application.DisplayAlerts = False 'will overwrite existing files without asking

    r = 1
    Do Until Len(Trim(wsSource.Cells(r, 1).Value)) = 0
        ThisWorkbook.Worksheets.Add ThisWorkbook.Worksheets(1)
        Set wsTemp = ThisWorkbook.Worksheets(1)

        For c = 2 To 7
            wsTemp.Cells((c - 1) * 2 - 1, 1).Value = wsSource.Cells(r, c).Value
        Next c

        wsTemp.Move
        Set wbNew = ActiveWorkbook
        Set wsTemp = wbNew.Worksheets(1)
        'wbNew.SaveAs wsSource.Cells(r, 1).Value & ".csv", xlCSV 'old way
        wbNew.SaveAs "textfile" & r & ".csv", xlCSV 'new way
        'you can try other file formats listed at http://msdn.microsoft.com/en-us/library/office/aa194915(v=office.10).aspx
        wbNew.Close
        ThisWorkbook.Activate
        r = r + 1
    Loop

    Application.DisplayAlerts = True

End Sub

这篇关于从Excel电子表格中的每一行创建文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！