从VBA中的数组创建CSV [英] Create CSV from array in VBA

问题描述

我是一名新手程序员，试图自动化一些重复的工作场所任务，这些任务应该由聪明的脚本而不是人类来完成.我已经做了一些VBA和Java，但是非常基础的东西.

I'm a novice programmer trying to automate some repetitive workplace tasks that should be done by a clever script instead of humans. I've done some VBA and Java, but very basic stuff.

我们有一些通过验证的在线表单生成的数据，这些数据通过电子邮件发送到邮箱，然后邮件客户端(Outlook 2010)中的筛选器将其放入特定的文件夹中.我的意图是编写一些VBA，该VBA在被触发时将检查该文件夹的内容，然后针对每个邮件项目，以字符串形式获取消息正文文本，创建一个数组，然后将数组内容写入.csv文件.然后，我将有另一个应用程序(适用于Firefox的iMacros)读取csv并与内部公司的Web应用程序进行交互以完成其余的工作.

We have some data generated by a validated online form that gets emailed to a mailbox, and then a filter in the mail client (Outlook 2010) puts it into a particular folder. My intention is to write some VBA that when triggered will check the contents of that folder, and then for each mail item, get the message body text as a string, create an array, and then write the array contents into a .csv file. I'll then have another app (iMacros for Firefox) read the csv and interact with an in-house corporate webapp to do the rest.

除了将数组内容写入csv之外，我想我可以做所有的事情.

I think I can do all of that except write the array contents to csv.

通过查看代码示例，我可以最好地工作(我确实尝试理解MS对象模型文档)，但是将VBA数组写入CSV的最佳方式是:

I work best from looking at code examples (I do try to understand the MS Object Model documentation), but the best I can find for writing a VBA array to CSV is something like:

  '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

我认为这是VB，而不是VBA，但是这里的总体思路是否有效?或者，如果其他人有代码示例来帮助我入门或其他任何指针，我将不胜感激.

I think this is VB and not VBA, but is the general idea here valid? Or if anyone else has a code sample to get me started or any other pointers I'd be very grateful.

PS还看到了如何能否将二维数组另存为csv文件?但无法理解.

PS Have also seen How do I save a 2-dimensional array as a csv file? but can't understand it.

推荐答案

以下代码适用于以CSV格式转换2D数组.我尚未编写代码，但已经对其进行了测试，并且可以正常工作！

The code below works for converting 2D arrays in CSV. I have not written the code, but have tested it and it works!

' 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
' [e-mail]


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"
Else
  While (Len(sFilename) - InStr(sFilename, ".csv")) > 3
    sFilename = Left(sFilename, Len(sFilename) - 1)
  Wend
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

这篇关于从VBA中的数组创建CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！