在 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，当触发时将检查该文件夹的内容，然后对于每个邮件项目，将消息正文文本作为字符串获取，创建一个数组，然后将数组内容写入 .csv 文件.然后，我将让另一个应用程序(用于 Firefox 的 iMacros)读取 csv 并与内部企业网络应用程序交互以完成剩下的工作.

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 也看过 How我如何将二维数组保存为 csv 文件?但无法理解.

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

推荐答案

以下代码适用于在 CSV 中转换二维数组.我还没有写代码，但已经测试过了，它可以工作！

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屋！