Importing data from an Excel spreadsheet into a VB 2005 application SQL table

Question

 

I am trying to re-write a VB6 application in VB 2005. One of the problems I have is I need to be able to import a spreadsheet into an SQL table. I have my VB 2005 application interface, where I need to enter the filename to import and then click a button. The old VB6 way was the transferspreadsheet command.  I set my application to use an Access database.

 

What is the best way to achieve this in VB2005?.

Answer 1

Private Sub loadDataFromExcel()

Dim excelFile As String = "c:\someDirectory\someFile.xls" 'replace with valid path - file

Dim excelSheet As String = "[Sheet1$]" 'replace with valid Sheet Name

Dim sql As String = "SELECT * FROM " & excelSheet ' add WHERE and ORDER if required

Dim tableName As String = "theNameYouLike"

Try

Using cn As New OleDb.OleDbConnection

' HDR = YES if first row contain column name else NO

' Excel 8.0 / 9.0 ... depending on your Excel version

cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=" & excelFile & ";" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

 

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, cn)

cn.Open()

da.Fill(yourDataSetName, tableName)

cn.Close()

End Using

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

'TODO proccess data to add / update other tables

End Sub

 

Answer 2

 

Thanks for the reply. I wish this kind of info was in the expensive VB 2005 .net book that I bought. Unfortunately, like most books, only about 10% of it seems to be of any use.

Answer 3

There seems to be a problem with the da.fill(yourdatasename, tablename)

 

I need to import the spreadsheet into a .mdb database. I open the connection and I am able to update/browse the table in other parts of the application.

 

I specify the <excelFil>e using the openfiledialog method and choose a filename from my c: drive. The <tableName> is hard coded into the application.

 

The da.Fill part fails with an error trying to convert a string to an integer. At this point, I have two conenctions open, the one for the .mdb and one for the excel file. The data from the excel spredsheet is not imported into the sql table.

 

I set the <youdatasetname> to the connectionname.datasource and this is not accepted.

Any ideas?

 

Thanks

Answer 4

 

Hello,

 

I assumed that your application has a typed dataset thru which you are connecting to your .mdb file. When you initially added a datasource to your application the wizard had created a typed dataset for you (eg NorthwindDataSet.xsd) which appears to the solution explorer.

 

So suppose the name of dataset is NorthwindDataSet

 

da.fill(NorthwindDataSet, "excelTable")

 

the excelTable does not exists in your dataset it is created when you execute the above statement.

 

Now suppose you want to ADD the data to the existing table Customers

 

for each row as datarow in NorthwindDataSet.tables("excelTable").rows

dim customerRow as NorthwindDataSet.CustomerRow = NorthwindDataSet.Customer.NewCustomerRow

dim i as int32 = 0

with customerRow

.CompanyName = row("CompanyName")

'.more columns ....

' some columns may require changing the Datatype

' at excel columns that contain numbers and blanks are considered STRINGS so

if int32.tryparse(row("SomeNumber"), i) then

.integerColumn = i

else

.integerColumn = 0

end if

'for decimals, dates etc are the coresponding tryparse methods

'also if you must check for DBnull

end with

NorthwindDataSet.Customer.AddCustomerRow(customerRow)

next

 

Answer 5

Thanks for you help. If I use the name of a table which does not exist within the .mdb file it does not add the table name to the .mdb file. Even if I hard code the table name in the fill command.

 

da.Fill(Account_Closure_DatabaseDataSet.Table_Account_Numbers, "Table_Account_Numbers_New") instead of

da.Fill(Account_Closure_DatabaseDataSet.Table_Account_Numbers, TableToImportTo)

 

This is the whole procedure below. It runs through without error, just no data appears in the table. "Table_Account_Numbers" does exist within the .mdb database, so I tried adding "_new" on the end. No success, but no runtime errors either.

 

==============================================================================

The xsd was created when I imported the database into the application.

Dim ExcelFileLocation As String = txtFileName.Text

Dim ExcelSheet As String = "[Table_Account_Numbers$]"

Dim sqlExcelData = "SELECT * FROM" & ExcelSheet

Dim TableToImportTo As String = "Table Account Numbers"

Try

Using ExcelConnection As New OleDb.OleDbConnection

ExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ExcelFileLocation & ";" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlExcelData, ExcelConnection)

ExcelConnection.Open()

da.Fill(Account_Closure_DatabaseDataSet.Table_Account_Numbers, "Table_Account_Numbers_New")

ExcelConnection.Close()

End Using

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

=================================================================================

Answer 6

Hello,

 

Change

da.Fill(Account_Closure_DatabaseDataSet.Table_Account_Numbers, "Table_Account_Numbers_New")

 

to

da.Fill(Account_Closure_DatabaseDataSet, "Table_Account_Numbers_New")

 

Now this table is a table created in the dataset NOT to the database

 

now if both tables contains the columns AccountNumber and AccountName ..........more Columns

 

for each row as datarow in Account_Closure_DatabaseDataSet.Tables("Table_Account_Numbers_New").rows

' create a new row for your table in the database

dim accountRow as Account_Closure_DatabaseDataSet.Table_Account_NumbersRow =

Account_Closure_DatabaseDataSet.Table_Account_Numbers.NewTable_Account_NumbersRow

 

with accountRow

' as i posted before you must check for dbnull values and parse values to correct format if necessary

.AccountNumber = row("AccountNumber")

.AccountName = row("AccountName")

 

' more columns

End with

 

' Add the row to the Table

Account_Closure_DatabaseDataSet.Table_Account_Numbers.AddTable_Account_NumbersRow (accountRow)

 

next

 

now the data from the temporary table are inserted to your table but they are not yet posted to the database

 

Table_Account_NumbersTableAdapter.Update(Account_Closure_DatabaseDataSet.Table_Account_Numbers)

Account_Closure_DatabaseDataSet.AcceptChanges

 

now the data have been posted to your database

 

 

Answer 7

Great, thanks. This works now. Thanks again for your help.

Answer 8

 

Here's my code anyhow for anyone who's interested. I added in a clear statement to the dataset I figured this was needed as I found the rows still exists within the dataset and cannot be re-inserted even if the rows of data are removed from the .mdb. There's probably room for improvement. I select the excel file to be imported from a form and pass this to the code.

 

Thanks again for you help.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Private Sub btnImportSpreadsheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportSpreadsheet.Click

Dim ExcelFileLocation As String = txtFileSelected.Text

Dim ExcelSheet As String = "[Table_Account_Numbers$]"

Dim sqlExcelData = "SELECT * FROM" & ExcelSheet

Dim TableToImportTo As String = "Table_Account_Numbers"

Try

Using ExcelConnection As New OleDb.OleDbConnection

ExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ExcelFileLocation & ";" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Dim AccountNumbersDataSet As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlExcelData, ExcelConnection)

Dim I As Int32

ExcelConnection.Open()

Account_Closure_DatabaseDataSet.Clear()

AccountNumbersDataSet.Fill(Account_Closure_DatabaseDataSet, TableToImportTo)

For Each Row As DataRow In Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows

Dim AccountNumbersTableRow As Account_Closure_DatabaseDataSet.Table_Account_NumbersRow = Account_Closure_DatabaseDataSet.Table_Account_Numbers.NewTable_Account_NumbersRow()

With AccountNumbersTableRow

 

If Not IsDBNull(Row("Account Numbers")) Then

If Int32.TryParse(Row("Account Numbers"), I) Then

.Account_Numbers = I

Else

.Account_Numbers = 0

End If

.Account_Numbers = Row("Account Numbers")

End If

 

If Not IsDBNull(Row("Status")) Then

If Int32.TryParse(Row("Status"), I) Then

.Status = I

Else

.Status = 0

End If

.Status = Row("Status")

End If

End With

 

'Add the next row

Account_Closure_DatabaseDataSet.Table_Account_Numbers.AddTable_Account_NumbersRow

AccountNumbersTableRow)

Next

 

'Apply changes to the physical database

Table_Account_NumbersTableAdapter.Update(Account_Closure_DatabaseDataSet.Table_Account_Numbers)

Account_Closure_DatabaseDataSet.AcceptChanges()

'Close connection

ExcelConnection.Close()

End Using

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

Answer 9

Hello,

 

Could you identify the reason as to why the application is not deleting ALL the rows from the physical database using the table adapters. I can get this to work by accessing the .mdb directly, but wish to use the table adapters instead.

I understand the part about marking each row for deletion. I commented out the tableadapters part where you can see my attemtpts at deleting the rows.

Thanks

 

 

Dim ExcelFileLocation As String = txtFileSelected.Text

Dim ExcelSheet As String = "[Sheet1$]"

Dim sqlExcelData = "SELECT * FROM" & ExcelSheet

Dim TableToImportTo As String = "Table_Account_Numbers"

Dim NumberOfRows, CurrentRowPosition As Integer

 

Const ConnectionString As String = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=""C:\Account Closure vb2008\Database\Account Closure Database.mdb"";" '

'Setup database connection and other environment variables

Dim DatabaseConnection As New OleDb.OleDbConnection(ConnectionString)

'open the database

DatabaseConnection.Open()

Const qryDeleteTableAccountNumbers As String = "DELETE * FROM [Table Account Numbers]"

Dim DeleteCmd1 As New OleDb.OleDbCommand(qryDeleteTableAccountNumbers, DatabaseConnection)

DeleteCmd1.ExecuteNonQuery()

DatabaseConnection.Close()

frmImportMessage.Show()

Try

Using ExcelConnection As New OleDb.OleDbConnection

ExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ExcelFileLocation & ";" & _

"Extended Properties=""Excel 8.0;HDR=NO"""

Dim AccountNumbersDataSet As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlExcelData, ExcelConnection)

Dim I As Int32

ExcelConnection.Open()

AccountNumbersDataSet.Fill(Account_Closure_DatabaseDataSet, TableToImportTo)

'NumberOfRows = Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows.Count

'

' NumberOfRows = NumberOfRows - 1

' For CurrentRowPosition = 0 To NumberOfRows

' Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows(CurrentRowPosition).Delete()

' CurrentRowPosition = CurrentRowPosition + 1

' Next

' Account_Closure_DatabaseDataSet.Table_Account_Numbers.AcceptChanges()

For Each Row As DataRow In Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows

Dim AccountNumbersTableRow As Account_Closure_DatabaseDataSet.Table_Account_NumbersRow = Account_Closure_DatabaseDataSet.Table_Account_Numbers.NewTable_Account_NumbersRow()

With AccountNumbersTableRow

' Some columns may require changing the Datatype

' Excel columns that contain numbers and blanks are considered STRINGS so they

' Need to be converted

If Not IsDBNull(Row(0)) Then

If Int32.TryParse(Row(0), I) Then

.Account_Numbers = I

Else

.Account_Numbers = 0

End If

.Account_Numbers = Row(0)

End If

'If Not IsDBNull(Row(1)) Then

' If Int32.TryParse(Row(1), I) Then

' .Status = I

' Else

' .Status = 0

' End If

' .Status = Row(1)

' End If

End With

'Add the next row

Account_Closure_DatabaseDataSet.Table_Account_Numbers.AddTable_Account_NumbersRow(AccountNumbersTableRow)

Next

'Apply changes to the physical database

Table_Account_NumbersTableAdapter.Update(Account_Closure_DatabaseDataSet.Table_Account_Numbers)

Account_Closure_DatabaseDataSet.AcceptChanges()

'Close connection and clear the dataset to avoid duplicate rows in table adapter

Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows.Clear()

ExcelConnection.Close()

ExcelConnection.Dispose()

End Using

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

frmImportMessage.Close()

Answer 10

Hi,

 

the problem at your code is this line

 

CurrentRowPosition = CurrentRowPosition + 1

 

because the for .. to statement automaticaly increases CurrentRowPosition by 1 and you also increase the counter by so you delete just half of the records. removing the above line your code will work properly

Answer 11

Thanks. I got used to using while loops. The records are still not being deleted though. I have tried the deletion at the dataset level and at the table adapter level then applied the changes. The physical database remains uinchanged and I then get duplicate row error messages.

 

Can you advise on the for loop part where I have the deletion and acceptchanges part?

 

 

Private Sub btnImportSpreadsheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportSpreadsheet.Click

Dim ExcelFileLocation As String = txtFileSelected.Text

Dim ExcelSheet As String = "[Sheet1$]"

Dim sqlExcelData = "SELECT * FROM" & ExcelSheet

Dim TableToImportTo As String = "Table_Account_Numbers"

Dim NumberOfRows, CurrentRowPosition As Integer

'Const ConnectionString As String = _

' "Provider=Microsoft.Jet.OLEDB.4.0;" & _

' "Data Source=""C:\Account Closure vb2008\Database\Account Closure Database.mdb"";" '

'Setup database connection and other environment variables

'Dim DatabaseConnection As New OleDb.OleDbConnection(ConnectionString)

'open the database

'DatabaseConnection.Open()

'Const qryDeleteTableAccountNumbers As String = "DELETE * FROM [Table Account Numbers]"

'Dim DeleteCmd1 As New OleDb.OleDbCommand(qryDeleteTableAccountNumbers, DatabaseConnection)

'DeleteCmd1.ExecuteNonQuery()

'DatabaseConnection.Close()

 

 

'The line below gives the incorrect nunmber of rows.

'NumberOfRows = Account_Closure_DatabaseDataSet.Table_Account_Numbers.Rows.Count

 

'This gives me the correct number of rows

NumberOfRows = Table_Account_NumbersTableAdapter.GetData.Rows.Count

 

For CurrentRowPosition = 0 To NumberOfRows - 1

'Account_Closure_DatabaseDataSet.Table_Account_Numbers.Rows(CurrentRowPosition).Delete()

Table_Account_NumbersTableAdapter.GetData.Rows(CurrentRowPosition).Delete()

Next

Table_Account_NumbersTableAdapter.GetData.AcceptChanges()

'Account_Closure_DatabaseDataSet.Table_Account_Numbers.AcceptChanges()

 

frmImportMessage.Show()

Try

Using ExcelConnection As New OleDb.OleDbConnection

ExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ExcelFileLocation & ";" & _

"Extended Properties=""Excel 8.0;HDR=NO"""

 

Dim AccountNumbersDataSet As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlExcelData, ExcelConnection)

Dim I As Int32

ExcelConnection.Open()

AccountNumbersDataSet.Fill(Account_Closure_DatabaseDataSet, TableToImportTo)

For Each Row As DataRow In Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows

Dim AccountNumbersTableRow As Account_Closure_DatabaseDataSet.Table_Account_NumbersRow = Account_Closure_DatabaseDataSet.Table_Account_Numbers.NewTable_Account_NumbersRow()

With AccountNumbersTableRow

' Some columns may require changing the Datatype

' Excel columns that contain numbers and blanks are considered STRINGS so they

' Need to be converted

If Not IsDBNull(Row(0)) Then

If Int32.TryParse(Row(0), I) Then

  .Account_Numbers = I

 Else

.Account_Numbers = 0

End If

.Account_Numbers = Row(0)

End If

'If Not IsDBNull(Row(1)) Then

' If Int32.TryParse(Row(1), I) Then

' .Status = I

' Else

' .Status = 0

' End If

' .Status = Row(1)

' End If

End With

'Add the next row

Account_Closure_DatabaseDataSet.Table_Account_Numbers.AddTable_Account_NumbersRow(AccountNumbersTableRow)

Next

'Apply changes to the physical database

Table_Account_NumbersTableAdapter.Update Account_Closure_DatabaseDataSet.Table_Account_Numbers)

Account_Closure_DatabaseDataSet.AcceptChanges()

'Close connection and clear the dataset to avoid duplicate rows in table adapter

Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows.Clear()

ExcelConnection.Close()

ExcelConnection.Dispose()

End Using

 

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

frmImportMessage.Close()

End Sub

Answer 12

Thanks. I got used to using while loops. The records are still not being deleted though. I have tried the deletion at the dataset level and at the table adapter level then applied the changes. The physical database remains uinchanged and I then get duplicate row error messages.

 

Can you advise on the for loop part where I have the deletion and acceptchanges part?

 

 

Private Sub btnImportSpreadsheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportSpreadsheet.Click

Dim ExcelFileLocation As String = txtFileSelected.Text

Dim ExcelSheet As String = "[Sheet1$]"

Dim sqlExcelData = "SELECT * FROM" & ExcelSheet

Dim TableToImportTo As String = "Table_Account_Numbers"

Dim NumberOfRows, CurrentRowPosition As Integer

'Const ConnectionString As String = _

' "Provider=Microsoft.Jet.OLEDB.4.0;" & _

' "Data Source=""C:\Account Closure vb2008\Database\Account Closure Database.mdb"";" '

'Setup database connection and other environment variables

'Dim DatabaseConnection As New OleDb.OleDbConnection(ConnectionString)

'open the database

'DatabaseConnection.Open()

'Const qryDeleteTableAccountNumbers As String = "DELETE * FROM [Table Account Numbers]"

'Dim DeleteCmd1 As New OleDb.OleDbCommand(qryDeleteTableAccountNumbers, DatabaseConnection)

'DeleteCmd1.ExecuteNonQuery()

'DatabaseConnection.Close()

 

 

'The line below gives the incorrect nunmber of rows.

'NumberOfRows = Account_Closure_DatabaseDataSet.Table_Account_Numbers.Rows.Count

 

'This gives me the correct number of rows

NumberOfRows = Table_Account_NumbersTableAdapter.GetData.Rows.Count

 

For CurrentRowPosition = 0 To NumberOfRows - 1

'Account_Closure_DatabaseDataSet.Table_Account_Numbers.Rows(CurrentRowPosition).Delete()

Table_Account_NumbersTableAdapter.GetData.Rows(CurrentRowPosition).Delete()

Next

Table_Account_NumbersTableAdapter.GetData.AcceptChanges()

'Account_Closure_DatabaseDataSet.Table_Account_Numbers.AcceptChanges()

 

frmImportMessage.Show()

Try

Using ExcelConnection As New OleDb.OleDbConnection

ExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ExcelFileLocation & ";" & _

"Extended Properties=""Excel 8.0;HDR=NO"""

 

Dim AccountNumbersDataSet As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlExcelData, ExcelConnection)

Dim I As Int32

ExcelConnection.Open()

AccountNumbersDataSet.Fill(Account_Closure_DatabaseDataSet, TableToImportTo)

For Each Row As DataRow In Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows

Dim AccountNumbersTableRow As Account_Closure_DatabaseDataSet.Table_Account_NumbersRow = Account_Closure_DatabaseDataSet.Table_Account_Numbers.NewTable_Account_NumbersRow()

With AccountNumbersTableRow

' Some columns may require changing the Datatype

' Excel columns that contain numbers and blanks are considered STRINGS so they

' Need to be converted

If Not IsDBNull(Row(0)) Then

If Int32.TryParse(Row(0), I) Then

  .Account_Numbers = I

 Else

.Account_Numbers = 0

End If

.Account_Numbers = Row(0)

End If

'If Not IsDBNull(Row(1)) Then

' If Int32.TryParse(Row(1), I) Then

' .Status = I

' Else

' .Status = 0

' End If

' .Status = Row(1)

' End If

End With

'Add the next row

Account_Closure_DatabaseDataSet.Table_Account_Numbers.AddTable_Account_NumbersRow(AccountNumbersTableRow)

Next

'Apply changes to the physical database

Table_Account_NumbersTableAdapter.Update Account_Closure_DatabaseDataSet.Table_Account_Numbers)

Account_Closure_DatabaseDataSet.AcceptChanges()

'Close connection and clear the dataset to avoid duplicate rows in table adapter

Account_Closure_DatabaseDataSet.Tables(TableToImportTo).Rows.Clear()

ExcelConnection.Close()

ExcelConnection.Dispose()

End Using

 

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

frmImportMessage.Close()

End Sub

Answer 13

 

Hi,

 

if NumberOfRows = 0

'The line below gives the incorrect nunmber of rows.

'NumberOfRows = Account_Closure_DatabaseDataSet.Table_Account_Numbers.Rows.Count

 this is because you have not fill the table adapter

 

to delete the records:

 

Table_Account_NumbersTableAdapter.Fill(Account_Closure_DatabaseDataSet.Table_Account_Numbers)

 

For each row as Account_Closure_DatabaseDataSet.Table_Account_NumbersRow in

Account_Closure_DatabaseDataSet.Table_Account_Numbers.Rows

row.delete

next

Table_Account_NumbersTableAdapter.Update(Account_Closure_DatabaseDataSet.Table_Account_Numbers)

Account_Closure_DatabaseDataSet.AcceptChanges

Answer 14

Thanks. I tried this but the number of rows is invalid when I reference using the Account_Closure_DatabaseDataSet.Table_Account_NumbersRow. It loops round 30 times when there are only 5 rows by 3 columns. If I use the Table_Account_NumbersTableAdapter.GetData.Rows.Count I get the correct numbers. The update statement ends up failing with this error :

Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

 

When I look at the data from the dataset, the rows are duplicated. Dont know why.

 

Any ideas?

 

Thanks.

 

Answer 15

Hi,

 

"Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

 

This is caused because the tableadapter is not configured properly for delete/update methods

Open the dataset , click on the table and look at the property window if there are Delete and update commands, if no you must add the Commands either manualy or by using the wizard.

 

Make sure that the table has a PRIMARY KEY else you can only add rows

 

Answer 16

The code works great, however I would like to change

Dim excelSheet As String = "[Sheet1$]" 'replace with valid Sheet Name

so that the sheet name can be determined programatically.  I am importing many different excel files from many different sources so it is hard to ensure that all of the files will contain a 'Sheet1'.  Does anyone have any ideas as to how this can be made more dynamic?

Answer 17

Hello,

 

First Add reference to Microsoft Excel Object Library (it is COM component) then

you can use the following code:

 

Dim xl As New Excel.Application

 

Try

xl.Workbooks.Open("C:\someDirectory\someFile.xls") ' Replace with valid Path and filename

 

For Each sheet As Excel.Worksheet In xl.Worksheets

'add sheet names to a listbox so user can select

ListBox1.Items.Add(sheet.Name)

Next

 

Catch ex As Exception

 

' proccess error

 

Finally

 

' Close Excel proccess and clear resources

xl.Quit()

xl = Nothing

 

End Try

 

Answer 18

Wow, it works GREAT!!  You have no Idea how long I have been scowering the internet for help on that.  Thanks for the help.