如何转置一组列并将输出另存为CSV [英] How do I transpose a set of columns and save the output as a CSV

问题描述

我在列中有很长的信息集，有时是数百行(这是通过VBA生成的).我需要对此进行转置并将其另存为CSV(因为excel会用完列).

I have a long set of information in columns, sometimes hundreds of rows (This is being generated via VBA). I need to transpose this AND save it as a CSV (as excel would run out of columns).

该表格的屏幕快照已附上.

A screenshot of the table is attached.

任何帮助都需要事先感谢.

Any help is appreciated in advance.

已经用两种不同的方式回答了问题.肯定它将对许多其他人有价值.

Question has been answered in two different ways. Am sure it will be valuable to many other people.

推荐答案

您需要一些代码才能导出到CSV，例如从此答案中导出 如何从VBA创建单独的CSV文件? 并只需更改您写入文件的顺序即可.例如.在写行之前先写列

You take some code to export to CSV, such as from this answer How to create a separate CSV file from VBA? and just change the order you write to the file. E.g. write the column before writing the row

Sub WriteFile()

  Dim ColNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim RowNum As Integer
  Dim SheetValues() As Variant

  PathName = Application.ActiveWorkbook.Path
  OutputFileNum = FreeFile

  Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum

  'Print #OutputFileNum, "Field1" & "," & "Field2"

  SheetValues = Sheets("RawData").Range("A1:C249").Value

Dim RowMax
RowMax = UBound(SheetValues)
Dim ColMax
ColMax = 3
ReDim LineValues(1 To RowMax)

  For ColNum = 1 To ColMax
    For RowNum = 1 To RowMax
      LineValues(RowNum) = SheetValues(RowNum, ColNum)
    Next
    Line = Join(LineValues, ",")
    Print #OutputFileNum, Line
  Next

  Close OutputFileNum

End Sub

希望这足以使您前进.

这篇关于如何转置一组列并将输出另存为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！