dcsimg
Results 1 to 12 of 12

Threaded View

  1. #1

    Thread Starter
    Hyperactive Member ProphetBeal's Avatar
    Join Date
    Aug 2006
    Location
    New York
    Posts
    424

    VB.NET - Create Excel Spreadsheet From Array


    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:
    1. Public Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "")
    2.         'To avoid conflicts with different versions of Excel...We are using late binding.
    3.         Dim objxlOutApp As Object 'Excel.Application
    4.         Dim objxlOutWBook As Object 'Excel.Workbook
    5.         Dim objxlOutSheet As Object 'Excel.Worksheet
    6.         Dim objxlRange As Object 'Excel.Range
    7.  
    8.         Try
    9.             'Try to Open Excel, Add a workbook and worksheet
    10.             objxlOutApp = CreateObject("Excel.Application") 'New Excel.Application
    11.             objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets
    12.             objxlOutSheet = objxlOutWBook.Sheets.Item(1)
    13.         Catch ex As Exception
    14.             MessageBox.Show("While trying to Open Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    15.             Try
    16.                 If Not IsNothing(objxlOutWBook) Then
    17.                     objxlOutWBook.Close()  'If an error occured we want to close the workbook
    18.                 End If
    19.                 If Not IsNothing(objxlOutApp) Then
    20.                     objxlOutApp.Quit() 'If an error occured we want to close Excel
    21.                 End If
    22.             Catch
    23.             End Try
    24.             objxlOutSheet = Nothing
    25.             objxlOutWBook = Nothing
    26.             If Not IsNothing(objxlOutApp) Then
    27.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp)  'This will release the object reference
    28.             End If
    29.             objxlOutApp = Nothing
    30.             Exit Sub 'An error occured so we don't want to continue
    31.         End Try
    32.  
    33.         Try
    34.             objxlOutApp.DisplayAlerts = False    'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?")
    35.             objxlOutApp.Visible = False    'We don't want the app visible while we are populating it.
    36.  
    37.             'This is the easiest way I have found to populate a spreadsheet
    38.             'First we get the range based on the size of our array
    39.             objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1))
    40.             'Next we set the value of that range to our array
    41.             objxlRange.Value = strOutputArray
    42.             'This final part is optional, but we Auto Fit the columns of the spreadsheet.
    43.             objxlRange.Columns.AutoFit()
    44.  
    45.             If strExcelFileOutPath.Length > 0 Then 'If a file name is passed
    46.                 Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath)
    47.  
    48.                 If Not objFileInfo.Directory.Exists Then 'Check if folder exists
    49.                     objFileInfo.Directory.Create() 'If not we create it
    50.                 End If
    51.                 objFileInfo = Nothing
    52.  
    53.                 objxlOutWBook.SaveAs(strExcelFileOutPath)  'Then we save our file.
    54.             End If
    55.             objxlOutApp.Visible = True 'Make excel visible
    56.         Catch ex As Exception
    57.             MessageBox.Show("While trying to Export to Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    58.             Try
    59.                 objxlOutWBook.Close()  'If an error occured we want to close the workbook
    60.                 objxlOutApp.Quit() 'If an error occured we want to close Excel
    61.             Catch
    62.             End Try
    63.         Finally
    64.             objxlOutSheet = Nothing
    65.             objxlOutWBook = Nothing
    66.             If Not IsNothing(objxlOutApp) Then
    67.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference
    68.             End If
    69.             objxlOutApp = Nothing
    70.         End Try
    71.     End Sub
    Last edited by ProphetBeal; Mar 2nd, 2007 at 01:09 PM. Reason: Added note for new code

    Helpful Links:
    VB 6 - How to get the "Key" Value in a collection
    VB.NET - File Search Utility || VB.NET - How to compare 2 directories || VB.NET - How to trust a network share
    VB.NET - Create Excel Spreadsheet From Array || VB.NET - Example Code & Hints you may not know
    VB.NET - Save JPEG with a certain quality (image compression) || VB.NET - DragDrop Files, Emails, and Email Attachments

    Please post some of the code you need help with (it makes it easier to help you)
    If your problem has been solved then please mark the thread [RESOLVED].
    Don't forget to Rate this post

    "Pinky, you give a whole new meaning to the phrase, 'counter-intelligence'."-The Brain-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.