# VBForums CodeBank > CodeBank - Visual Basic .NET >  VB.NET - Create Excel Spreadsheet From Array

## ProphetBeal

This is a short method that accepts a 2 dimensional string array and turns it into an excel spreadsheet.  You can also pass a file name and it will save the spreadsheet.   FYI...As noted in the code I'm using late binding to avoid version issues with Excel.

*This is the Original code. See Below (post 4) for the Updated Code*

VB Code:
Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
        'To avoid conflicts with different versions of Excel...We are using late binding.
        Dim objxlOutApp As Object 'Excel.Application
        Dim objxlOutWBook As Object 'Excel.Workbook
        Dim objxlOutSheet As Object 'Excel.Worksheet
        Dim objxlRange As Object 'Excel.Range
         Try
            'Try to Open Excel, Add a workbook and worksheet
            objxlOutApp = CreateObject("Excel.Application") 'New Excel.Application
            objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets
            objxlOutSheet = objxlOutWBook.Sheets.Item(1)
        Catch ex As Exception
            MessageBox.Show("While trying to Open Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Try
                If Not IsNothing(objxlOutWBook) Then
                    objxlOutWBook.Close()  'If an error occured we want to close the workbook
                End If
                If Not IsNothing(objxlOutApp) Then
                    objxlOutApp.Quit() 'If an error occured we want to close Excel
                End If
            Catch
            End Try
            objxlOutSheet = Nothing
            objxlOutWBook = Nothing
            If Not IsNothing(objxlOutApp) Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp)  'This will release the object reference
            End If
            objxlOutApp = Nothing
            Exit Sub 'An error occured so we don't want to continue
        End Try
         Try
            objxlOutApp.DisplayAlerts = False    'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?")
            objxlOutApp.Visible = False    'We don't want the app visible while we are populating it.
             'This is the easiest way I have found to populate a spreadsheet
            'First we get the range based on the size of our array
            objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1))
            'Next we set the value of that range to our array
            objxlRange.Value = strOutputArray
            'This final part is optional, but we Auto Fit the columns of the spreadsheet.
            objxlRange.Columns.AutoFit()
             If strExcelFileOutPath.Length > 0 Then 'If a file name is passed
                Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
                 If Not objFileInfo.Directory.Exists Then 'Check if folder exists
                    objFileInfo.Directory.Create() 'If not we create it
                End If
                objFileInfo = Nothing
                 objxlOutWBook.SaveAs(strExcelFileOutPath)  'Then we save our file.
            End If
            objxlOutApp.Visible = True 'Make excel visible
        Catch ex As Exception
            MessageBox.Show("While trying to Export to Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Try
                objxlOutWBook.Close()  'If an error occured we want to close the workbook
                objxlOutApp.Quit() 'If an error occured we want to close Excel
            Catch
            End Try
        Finally
            objxlOutSheet = Nothing
            objxlOutWBook = Nothing
            If Not IsNothing(objxlOutApp) Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
            End If
            objxlOutApp = Nothing
        End Try
    End Sub

----------


## JuggaloBrotha

you might want to mention that the code requires the use of the Office Interop Assemblies

----------


## RobDog888

Also, the use of ReleaseComObject should not be needed ever if the object variables are handled correctly.

Lastly, you shouold have Option Strict On which will generate several errors as the code doesnt have any casting.

----------


## ProphetBeal

Thanks for the feedback guys.  I have updated the code with Option Strict On.  In order to use this code you will need to add a reference to the Microsoft Excel Object Library.

VB Code:
Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
        Dim objxlOutApp As Excel.Application
        Dim objxlOutWBook As Excel.Workbook
        Dim objxlOutSheet As Excel.Worksheet
        Dim objxlRange As Excel.Range
         Try
            'Try to Open Excel, Add a workbook and worksheet
            objxlOutApp = New Excel.Application
            objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets
            objxlOutSheet = DirectCast(objxlOutWBook.Sheets.Item(1), Excel.Worksheet)
        Catch ex As Exception
            MessageBox.Show("While trying to Open Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Try
                If Not IsNothing(objxlOutWBook) Then
                    objxlOutWBook.Close()  'If an error occured we want to close the workbook
                End If
                If Not IsNothing(objxlOutApp) Then
                    objxlOutApp.Quit() 'If an error occured we want to close Excel
                End If
            Catch
            End Try
            objxlOutSheet = Nothing
            objxlOutWBook = Nothing
            'If Not IsNothing(objxlOutApp) Then
            'System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp)  'This will release the object reference
            'End If
            objxlOutApp = Nothing
            Exit Sub 'An error occured so we don't want to continue
        End Try
         Try
            objxlOutApp.DisplayAlerts = False    'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?")
            objxlOutApp.Visible = False    'We don't want the app visible while we are populating it.
             'This is the easiest way I have found to populate a spreadsheet
            'First we get the range based on the size of our array
            objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1))
            'Next we set the value of that range to our array
            objxlRange.Value = strOutputArray
            'This final part is optional, but we Auto Fit the columns of the spreadsheet.
            objxlRange.Columns.AutoFit()
             If strExcelFileOutPath.Length > 0 Then 'If a file name is passed
                Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
                 If Not objFileInfo.Directory.Exists Then 'Check if folder exists
                    objFileInfo.Directory.Create() 'If not we create it
                End If
                objFileInfo = Nothing
                 objxlOutWBook.SaveAs(strExcelFileOutPath)  'Then we save our file.
            End If
            objxlOutApp.Visible = True 'Make excel visible
        Catch ex As Exception
            MessageBox.Show("While trying to Export to Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Try
                objxlOutWBook.Close()  'If an error occured we want to close the workbook
                objxlOutApp.Quit() 'If an error occured we want to close Excel
            Catch
            End Try
        Finally
            objxlOutSheet = Nothing
            objxlOutWBook = Nothing
            'If Not IsNothing(objxlOutApp) Then
            '    System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
            'End If
            objxlOutApp = Nothing
        End Try
    End Sub

----------


## RobDog888

Yes, with Option Strict On you will have to either add thereference and switch to Early Binding or use Reflection and have it as Late Binding with Option Strict On. Its allot of work to use Late Binding with Reflection so unless there is a good reason for late binding I would just early bind.

----------


## tkent

thanks for the post - it acutally works - would change the last part since the spreadsheet stays locked by the system and you can't delete it - i am creating this in a web app to download - will probably bring the ire of those who will say this automation was not meant to be used on a server. sorry bout that.

revised code is 
    Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
        Dim objxlOutApp As Microsoft.Office.Interop.Excel.Application

.
.
.
            If strExcelFileOutPath.Length > 0 Then
                'If a file name is passed                
                Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
                If Not objFileInfo.Directory.Exists Then 'Check if folder exists                    
                    objFileInfo.Directory.Create() 'If not we create it                
                End If
                objFileInfo = Nothing
                objxlOutWBook.SaveAs(strExcelFileOutPath)  'Then we save our file.  

            End If


        Catch ex As Exception


        End Try

        Try
            objxlOutWBook.Close()                'want to close the workbook                
            objxlOutApp.Quit()                'want to close Excel            


        Finally
            objxlOutSheet = Nothing
            objxlOutWBook = Nothing
            objxlOutApp = Nothing
        End Try

    End Sub

----------


## dellis

This works well up to 26 columns in Excel 2003. However, it breaks when computing the upper bound going from Z to AA. 

If you replace line 38 with the following code in the version posted by ProphetBeal on Mar 2nd, 2007, 01:03 PM, all columns up to  IV are accepted.

            'Let's determine the column name
            Dim sUpperBoundLetter As String
            Select Case strOutputArray.GetUpperBound(1)
                Case Is > 234
                    sUpperBoundLetter = "I" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26 - 26 - 26 - 26)
                Case Is > 208
                    sUpperBoundLetter = "H" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26 - 26 - 26)
                Case Is > 182
                    sUpperBoundLetter = "G" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26 - 26)
                Case Is > 156
                    sUpperBoundLetter = "F" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26 - 26)
                Case Is > 130
                    sUpperBoundLetter = "E" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26 - 26)
                Case Is > 104
                    sUpperBoundLetter = "D" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26 - 26)
                Case Is > 78
                    sUpperBoundLetter = "C" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26 - 26)
                Case Is > 52
                    sUpperBoundLetter = "B" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26 - 26)
                Case Is > 26
                    sUpperBoundLetter = "A" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64 - 26)
                Case Else
                    sUpperBoundLetter = Chr(strOutputArray.GetUpperBound(1) + 1 + 64)
            End Select

            objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & sUpperBoundLetter & (strOutputArray.GetUpperBound(0) + 1))

----------


## zhshqzyc

How to call this method if I pass one dimension string array?

----------


## ProphetBeal

> How to call this method if I pass one dimension string array?


Well this was created to handle 2 dimensional arrays.  1 dimension for the rows and a 2nd for the columns.  If you only have a 1 dimension array where would the data be populated?  A single row or a single column?

----------


## zhshqzyc

Thanks.
I resloved it.


But I deploy the executable to my flash drive(run .exe file), I get an "Unhandled exception error" stating "Could not find file or assembly "Interop.Excel."

Do I need to add more ferences or modify the codes?
Or shall I copy something to the flash drive?

I am using your the earliest code.

----------


## zhshqzyc

The code is good except one thing.
If there is an excel file opened. It can't been export it to the excel file.
Any advice?

----------


## stlaural

Nice Thread, helped a lot, thanks.

----------

