问 您能直接将excel VBA中的数组内容写入CSV文件吗？

EN

我有一些代码将表加载到数组中，进行一些计算，创建一个新表，将更改后的数组写入新工作表，并将新表保存为CSV。

我需要一个CSV，因为这是唯一的格式，目的系统，这是接受。

我想知道是否可以直接将数组写入CSV，跳过两个步骤。

通过我的研究，我从 THIS POST 中找到了下面的代码，但是我无法让它工作。

' SaveAsCSV saves an array as csv file. Choosing a delimiter different as a comma, is optional.
' Syntax:
' SaveAsCSV dMyArray, sMyFileName, [sMyDelimiter]
' Examples:
' SaveAsCSV dChrom, app.path & "\Demo.csv"       --> comma as delimiter
' SaveAsCSV dChrom, app.path & "\Demo.csv", ";"  --> semicolon as delimiter
' Rev. 1.00 [8 jan 2003]
' written by P. Wester
' wester@kpd.nl
Public Sub SaveAsCSV(MyArray() As Variant, sFilename As String, Optional sDelimiter As String = ",")
Dim n As Long 'counter
Dim M As Long 'counter
Dim sCSV As String 'csv string to print
On Error GoTo ErrHandler_SaveAsCSV
'check extension and correct if needed
If InStr(sFilename, ".csv") = 0 Then
  sFilename = sFilename & ".csv"
  While (Len(sFilename) - InStr(sFilename, ".csv")) > 3
    sFilename = Left(sFilename, Len(sFilename) - 1)
End If
'If MultiDimensional(MyArray()) = False Then '1 dimension
  'save the file
'  Open sFileName For Output As #7
'  For n = 0 To UBound(MyArray(), 1)
'    Print #7, Format(MyArray(n, 0), "0.000000E+00")
'  Next n
'  Close #7
'Else 'more dimensional
  'save the file
  Open sFilename For Output As #7
  For n = 1 To UBound(MyArray(), 1)
    sCSV = ""
    For M = 1 To UBound(MyArray(), 2)
      sCSV = sCSV & Format(MyArray(n, M)) & sDelimiter
    Next M
    sCSV = Left(sCSV, Len(sCSV) - 1) 'remove last Delimiter
    Print #7, sCSV
  Next n
  Close #7
'End If
Exit Sub
ErrHandler_SaveAsCSV:
  Close #7

使用此语法

    SaveAsCSV arOut, Application.ActiveWorkbook.Path & "\Demo.csv"

我在那条线上发现了这个错误。类型不匹配:期望数组或用户定义的类型。我可以通过编辑这一行和删除括号来弥补这一点。

Public Sub SaveAsCSV(MyArray() As Variant, sFilename As String, Optional sDelimiter As String = ",")

变到

Public Sub SaveAsCSV(MyArray As Variant, sFilename As String, Optional sDelimiter As String = ",")

但是，这只会创建一个空白文件，因为这里现在出现了一个错误。这将触发错误处理。

​

我无法理解的是，当我在手表窗口中看到MyArray时，它的填充方式应该是这样的，所以我无法发现这个错误。任何帮助都是非常感谢的。