Am having a little problem with importing data from MS SQL to and Excel sheet. I followed previous answers on daniweb, but they couldn't help me out.

Here my code.

Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click

        Dim appXL As New Microsoft.Office.Interop.Excel.Application
        Dim shXL As New Microsoft.Office.Interop.Excel.Worksheet
        Dim wbXl As New Microsoft.Office.Interop.Excel.Workbook

        wbXl = appXL.Workbooks.Add()
        shXL = wbXl.Sheets("sheet1")

        shXL.Cells(1, 1).Value = "First Name"
        shXL.Cells(1, 2).Value = "Last Name"

        Dim adapter As New SqlDataAdapter("select * from data", con)
        Dim ds As New DataSet()

        adapter.Fill(ds, "data")

        Dim colIndex = 0
        Dim rowIndex = 1

        Dim d As String

        For Each row As DataRow In ds.Tables(0).Rows
            For Each col As DataColumn In ds.Tables(0).Columns
                d = row(col.ColumnName).ToString()
                shXL.Cells(rowIndex, colIndex) = d


    End Sub
End Class

Following is the error report which happens to be for the line Dim shXL As New Microsoft.Office.Interop.Excel.Worksheet

Suggestions on code efficiency and other ways are also welcomed.

Edited by Rahul47: added snap

3 Years
Discussion Span
Last Post by rubberman

One suggestion would be to use the SQL command line and do a query that outputs the data in CSV format. Then you can easily load that into Excel.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.