Export to Excel using VB
In this article, I am going to explain how to export data to a spreadsheet from the application. This code needs the excel application to be installed in the machine.Make sure Excel is referenced in the project.
For a silent operations (no Windows) objXLApp.Visible as false and for formatting we need to format range object of worksheet.
Define WorkBook
For accessing a work sheet we must initialize Application and work book.
Dim objXLApp As New Excel.Application
Dim objExBook As Excel.Workbook
Access the Work Book and WorkSheets
Workbooks are the equivalent of a spread sheet file in the disk. Hence, to create to a new file we need to create a new workbook as follows.
Set objExBook = objXLApp.Workbooks.Add
Every workbook will load with three work sheets so probably we may be interested in the first sheet to start working on.
Set objWorkSheet = objExBook.Worksheets(1)
Start writing into cells
After the work sheet is selected, we need to loop through the cells to stuff the data
For Each strItm In colInput
intRowIndex = intRowIndex + 1
objWorkSheet.Cells(intRowIndex, 1) = strItm
Next strItm
Once the data have been stuffed this is the time to save the file. So provide the full path along with the file name
objExBook.SaveAs strPath
Once saved the next step is to close the work book
objExBook.Close
After the workbook is saved. We can safely quit the application. if the application is not quit. The next time the function is called it will create a separate instance(application). So it is always safer to quit the application once done.
objXLApp.Quit
Source Code
Private Sub Command1_Click()
Dim colItems As New Collection
colItems.Add "one"
colItems.Add "two"
colItems.Add "three"
colItems.Add "four"
colItems.Add "five"
colItems.Add "six"
colItems.Add "seven"
Export2XL colItems, "c:\test.xls"
End Sub
'**************************************************************
'* Purpose : To export from collection
'*
'* Inputs : strPath(String) Path to create the file
'* colInput(collection) Collection to save
'*
'* Returns : NA
'**************************************************************
Public Sub Export2XL (colInput As Collection, strPath As String)
Dim objXLApp As New Excel.Application
Dim objExBook As Excel.Workbook
Dim objWorkSheet As Excel.Worksheet
Dim intRowIndex As Integer
Dim strItm
'On Error GoTo ErrTrap
'Initialising application's properties
objXLApp.Visible = True
objXLApp.WindowState = xlNormal
objXLApp.Width = 300
objXLApp.Height = 200
' initialize work book
Set objExBook = objXLApp.Workbooks.Add
' initialize work sheet
Set objWorkSheet = objExBook.Worksheets(1)
For Each strItm In colInput
intRowIndex = intRowIndex + 1
objWorkSheet.Cells(intRowIndex, 1) = strItm
Next strItm
objExBook.SaveAs strPath
objExBook.Close
objXLApp.Quit
ErrTrap:
If Err Then Err.Raise Err.Number, , "Error form Functions. Export2XL " & Err.Description
End Function