Member Avatar

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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.