Create CSV from array in VBA

Question

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.

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.

I think I can do all of that except write the array contents to 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

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 Have also seen How do I save a 2-dimensional array as a csv file? but can't understand it.

Answer 1

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
' [email protected]


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

Answer 2

I don't work with VB so I'm not sure about the open file for output #7 but if that works (test it with some random output), then your code snippet is right. The only thing is that you should have another Print call where you add a ",".

Just make sure that on your last iteration you don't add the ",". You can do this by adding an If statement that will check if n = UBound(MyArray) - 1 (Which is the index of the last element.)

Hope it helps,

PM