Member Avatar for gabanxx

ai guys need a little help here..i create an interface where user can store their information like name, hobbies, DOB and phone number...the info is stored in sql server..now i want to call back all data that have being stored in sql to excel..when i say all i mean all of it... i tried and manage to run the code but the problem is only the data that are disply in the textbox(used by user to enter data and outomatically display the data when the progrm start) that appear in excel... i know this matter is small to u guys out there but its big for me...so why dont give some help here by giving the approriate code..plssssss

this is code made so give comment....

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim MsExcel As Excel.Application
        MsExcel = CreateObject("Excel.Application")

        Dim varConnection As New SqlConnection


        MsExcel.Workbooks.Add()
        MsExcel.Range("A1").Value = "Name_User"
        MsExcel.Range("B1").Value = "Date_of_birth"
        MsExcel.Range("C1").Value = "Hobbies"
        MsExcel.Range("D1").Value = "Phone_Number"
        MsExcel.Range("A2").Value = TextBox1.Text
        MsExcel.Range("B2").Value = TextBox2.Text
        MsExcel.Range("C2").Value = TextBox3.Text
        MsExcel.Range("D2").Value = TextBox4.Text

        MsExcel.Visible = True

End Sub

seriously need help here....:(

  • 3 Contributors
  • 4 Replies
  • 520 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by NanaYee
Member Avatar for gabanxx

i know the code but im using control button and there is certain error that i dont get it and im just a nuub in vb.net...can u give mre detail code.. or can u detect the problm in my code...

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim MsExcel As Excel.Application
        MsExcel = CreateObject("Excel.Application")

        Dim varConnection As New SqlConnection


        MsExcel.Workbooks.Add()
        MsExcel.Range("A1").Value = "Name_User"
        MsExcel.Range("B1").Value = "Date_of_birth"
        MsExcel.Range("C1").Value = "Hobbies"
        MsExcel.Range("D1").Value = "Phone_Number"
        MsExcel.Range("A2").Value = TextBox1.Text
        MsExcel.Range("B2").Value = TextBox2.Text
        MsExcel.Range("C2").Value = TextBox3.Text
        MsExcel.Range("D2").Value = TextBox4.Text

        MsExcel.Visible = True

  




    End Sub

'

***********-----try 1------saje test----------------------------------------********'''''''

    Private Function fncExcelExport(ByVal strSql As String)

      

        Dim dsExcelExport As New DataSet
        Dim daExcelExport As New SqlDataAdapter
        Dim Excel As New Excel.Application
        Dim intColumn, intRow, intColumnValue As Integer
        Dim strExcelFile As String
        Dim strFileName As String
        Dim conCurrent As New SqlConnection

        'conCurrent = New SqlConnection("Server=(local);user id=sa;password=;Initial Catalog=ROP1")
        conCurrent = New SqlConnection("server=;(local) catalog=ROP1;User ID=sa;Password=")
        daExcelExport = New SqlDataAdapter(strSql, conCurrent)
        varCommand = New SqlCommand(strSql, varConnection)
        daExcelExport = New SqlDataAdapter(varCommand)
        daExcelExport.Fill(dsExcelExport)

        Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\") + 1)

        With Excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()
            'For displaying the column name in the the excel file.            
            For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                .Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString
            Next
            'For displaying the column value row-by-row in the the excel file.            
            For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
                For intColumnValue = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                    .Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                Next

            Next
            'strFileName = InputBox("Please enter the file name.", "Swapnil")
            'strExcelFile = strAppPath & "employee"
            .ActiveWorkbook().SaveAs(strExcelFile)
            .ActiveWorkbook.Close()


        End With
        MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
NormalExit:
        Excel.Quit()
        Excel = Nothing
        GC.Collect()
        Exit Function
    End Function


End Class
Member Avatar for sierrainfo

heres the code--replace the Initial Catalog (database) in the Connection string.

Add a Reference to the Excel Library.

The Excel file will be saved to the bin/Debug folder.

Instead of a Function-use a Sub. functions must return a value and your code doesn't.

Also set a Reference and Import System.Data.SqlClient.

Replace the Query in the Button_Click event


Private Sub fncExcelExport(ByVal strSql As String)

Dim dsExcelExport As New DataSet
Dim daExcelExport As New SqlDataAdapter
Dim Excel As New Excel.Application
Dim intColumn, intRow, intColumnValue As Integer
Dim strExcelFile As String
Dim strFileName As String
Dim conCurrent As New SqlConnection
Dim varCommand As New SqlCommand

'conCurrent = New SqlConnection("Server=(local);user id=sa;password=;Initial Catalog=ROP1")
conCurrent = New SqlConnection("server=(local); Initial Catalog=Pubs;User ID=sa;Password=")
daExcelExport = New SqlDataAdapter(strSql, conCurrent)
varCommand = New SqlCommand(strSql, conCurrent)
daExcelExport = New SqlDataAdapter(varCommand)
daExcelExport.Fill(dsExcelExport)

Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\") + 1)

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
'For displaying the column name in the the excel file.
For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
.Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString
Next
'For displaying the column value row-by-row in the the excel file.
For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
For intColumnValue = 0 To dsExcelExport.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
Next

Next
strFileName = InputBox("Please enter the file name.", "Swapnil")
strExcelFile = strAppPath & "employee"
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close()


End With
MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
NormalExit:
Excel.Quit()
Excel = Nothing
GC.Collect()


End Sub

Dim MsExcel As Excel.Application
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

fncExcelExport("Select * from authors")

End Sub

Be a part of the DaniWeb community

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