# Open and write array to Excel?



## bradle95 (Jul 27, 2003)

Could I get some help on how to open (or create) a spreadsheet with VB6 and then write an array to it? Thanks.


----------



## asbo (Sep 21, 2001)

Write your array as a CSV file, then shell() it.


----------



## bradle95 (Jul 27, 2003)

I did find a way to do write an array to an excel file, but out of curiosity, how would I write my array as a CSV file, then shell() it? I have used VB for a grand total of about 2 hours now, so I'm not really sure what that means. Thanks.


----------



## asbo (Sep 21, 2001)

The resulting file is nothing fancy, but here goes:

```
Private Sub writeCSVFile(aryData() as String)
    Dim objFSOHandle, fsoCSVDump, strPath, str
    
    strPath = InputBox("Path?")
    
    For i = LBound(aryData) To UBound(aryData)
        If i < UBound(aryData) Then
            strPath = strPath & aryData(i) & ","
        Else
            strPath = strPath & aryData(i)
        End If
    Next

    Set objFSOHandle = CreateObject("Scripting.FileSystemObject")
    Set fsoCSVDump = objFSOHandle.CreateTextFile(strPath & "arraydump.csv", True)
    
    fsoCSVDump.WriteLine
    
    fsoCSVDump.Close
    
    Select Case Msgbox("The contents of the array were successfully written to a CSV file. Execute Excel?")
        Case vbYes
            Shell "c:\program files\microsoft office\excel.exe " & strPath & "arraydump.csv"
            Shell strPath & "arraydump.csv"
            Msgbox "Done."
    End Select
End Sub
```
I wrote this all without syntax checking, so there may be errors, but this code gives the jist of my method, although this particular example is very dirty; you have to supply a hand-typed path, and it must end with a backslash. You can't customize the filename at runtime, and I'm not sure whether the second Shell line (it's supposed to have the same effect as the first one); ideally it would be because then you wouldn't need the path to Excel.

If my IDE would play nice and install, I could follow up more. Let me know how that works.

I thought I'd add that if you add this code to your project, you call it like so:
writeCSVFile(somearray)
You should replace somearray with the name of the array that contains your data.


----------



## bradle95 (Jul 27, 2003)

Thanks a lot for the help, I really appreciate it. I will give this a shot, and let you know.


----------

