In order to interface with excel you have to make the reference to it, go to Project -> Add Reference -> under the COM tab select Microsoft Excel 11.0 Object Library (could be a different number based on your version).
After that you can use functions that are reserved for Excel this next part can get pretty tedious. It is probably best to create a loop that keeps calling information from your DB dynamically. This is an example of some code I used to export:
Sub exportToExcel() 'Declare Variables to create the excel application, workbook, sheet and chart Dim xlApp As Excel.Application Dim xlWkb As Excel.Workbook 'Create the excel application xlApp = CreateObject("Excel.Application") 'Add the workbook to the excel application xlWkb = xlApp.Workbooks.Add 'Set up connection for database Dim con As New OleDb.OleDbConnection Dim ds As New DataSet Dim da As OleDb.OleDbDataAdapter Dim sql As String con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = CONNECTION STRING" 'Open the Connection to the database con.Open() 'Pull information from the database sql = "SELECT * FROM table_name" da = New OleDb.OleDbDataAdapter(sql, con) da.Fill(ds, "Survey Results") Dim cb As New OleDb.OleDbCommandBuilder(da) Dim numRows As Integer = ds.Tables("Survey Results").Rows.Count - 1 xlSessionCommentsExport(numRows, ds, xlWkb) xlBestExport(numRows, ds, xlWkb) xlTopicsExport(numRows, ds, xlWkb) xlNumericalExport(numRows, ds, xlWkb) xlSummaryExport(numRows, ds, xlWkb, con) With xlWkb .Sheets("Sheet1").Select() .ActiveSheet.Delete() .Sheets("Sheet2").Select() .ActiveSheet.Delete() .Sheets("Sheet3").Select() .ActiveSheet.Delete() End With xlApp.Visible = True con.Close() End Sub
Because it can get pretty long I used functions to actually insert the data, the xlSessionCommentsExport is probably the shortest so i will give that to you
Private Sub xlSessionCommentsExport(ByVal numRows, ByVal ds, ByVal xlWkb) Dim i As Integer Dim dsRow As DataRow 'Add the worksheet to the workbook Dim xlSessionComments As Excel.Worksheet xlSessionComments = xlWkb.Worksheets.Add xlSessionComments.Name = "Session Comments" With xlSessionComments .Range("A1:B1").Merge() .Range("A1:B1").FormulaR1C1 = "Session Comments" For i = 0 To numRows 'get the current row dsRow = ds.Tables("Survey Results").Rows(i) 'get the values required for this worksheet out of the current 'row and assign them to variables sessionComments = CheckDBNullStr(dsRow.Item("sessionComments")) .Range("B" & i + 2).FormulaR1C1 = sessionComments Next .Columns("B").WrapText = True .Columns("B:B").ColumnWidth = 110 .Rows("1:" & numRows + 2).autofit() End With End Sub
In order to get what would actually be used to put the information in Excel go into Excel and create a Macro that does what you want to do and then there is a way to look at the VB code that does that macro. You will have to change a little bit, but the majority should work just fine.
Other than that I have never imported anything, but it should be the same except backwards. You will have to open the actual file from the program and use these functions to read it in - google a little bit that should be pretty easy. If I wasn't clear on anything don't hesitate to ask.
i am trying to create a library management system which is supposed to be used to store/keep infp for the library and the library members( book title, author, name and picture for borrower and other stuffs). The app has 6 buttons, delete, save, add,previsous,next, and upload( for upload ...
Im trying to bind my custom vertical scrollbar to datagridview is this possible.. Please help Thnak you..
I have two tables, I will call them first-half and second-half (of the year)
Table: first id jan feb mar apr ----------------------- 2 80 90 70 60 3 50 40 60 30 Table: second id jan feb mar apr ----------------------- 2 50 70 60 90 3 80 60 50 ...