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
