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.

hardikudeshi · Accepted Answer · 2012-04-25 10:21:49Z

2

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

answered Apr 25, 2012 at 10:21

hardikudeshi

1,4915 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Keith Swerling Over a year ago

Works great, just have to add this in case there are commas in the field record. codeDim RecordAt As String and For m = LBound(MyArray(), 2) To UBound(MyArray(), 2) RecordAt = Format(MyArray(n, m)) If InStr(RecordAt, ",") > 0 Then RecordAt = Chr(34) & RecordAt & Chr(34) sCSV = sCSV & RecordAt & sDelimiter Next m code

user472875 · Accepted Answer · 2010-11-16 06:04:44Z

1

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

answered Nov 16, 2010 at 6:04

user472875

3,1854 gold badges43 silver badges70 bronze badges

Comments

Manuel · Accepted Answer · 2022-01-04 21:48:46Z

This is an excel based solution. If you control outlook from excel this could be a viable solution. Additionally if you are working on an excel project this could be of use.

Private Sub ArrayToCsvWorkborkMethod(MyArray As Variant, FileName As String)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim WB As Workbook
Set WB = Application.Workbooks.Add
Dim WS As Worksheet
Set WS = WB.Sheets(1)

Dim Target As Range
Set Target = Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2))
Target.Value = MyArray
WB.SaveAs FileName, 23
WB.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I solved it this way instead. This is slower but eliminates any weird formatting the previous solution might do. This creates a new workbook, Writes your array to the worksheet, saves that new workbook as a CSV file and than closes the new workbook.

At least for me this works much more reliably and offloads any formatting concerns to excel.

Collectives™ on Stack Overflow

Create CSV from array in VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related