If you are running an application server-side, this can be a good approach for taking the bulk of processing the data away from your clients. There are also methods that you can use to transfer data to Excel that do not necessarily require Automation.

Transfer data to the clipboard and then paste the clipboard contents into an Excel worksheet.

Create a QueryTable on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source.Transfer data in an ADO recordset to a range of cells using the CopyFromRecordset method.Transfer data in an array to a range of cells.With Automation, you can use several approaches for transferring your data: Automation gives you the greatest flexibility for specifying the location of your data in the workbook as well as the ability to format the workbook and make various settings at run time. The approach most commonly used to transfer data to an Excel workbook is Automation. This article also presents the advantages and the disadvantages for each method so that you can choose the solution that works best for you. Using con As New OleDbConnection(conStr)Ĭmd.CommandText = (Convert.This article discusses numerous methods for transferring data to Microsoft Excel from your Microsoft Visual Basic application. SheetName = dtExcelSchema.Rows( 0)( ' TABLE_NAME').ToString()Įnd Using End Using ' Read Data from the First Sheet. Using con As New OleDbConnection(conStr)ĭim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Object, ByVal e As ) Handles OpenFileDialog1.FileOkĭim filePath As String = OpenFileDialog1.FileNameĭim extension As String = Path.GetExtension(filePath)ĭim header As String = If(RadioButton1.Checked, ' YES', ' NO')ĭim conStr As String, sheetName As StringĬonStr = String.Format(Excel03ConString, filePath, header)ĬonStr = String.Format(Excel07ConString, filePath, header)Įxit Select End Select ' Get the name of the First Sheet. Object, ByVal e As System.EventArgs) Handles Button1.ClickĮnd Sub Private Sub OpenFileDialog1_FileOk( ByVal sender As System. RadioButton1.Checked = True End Sub Private Sub Button1_Click( ByVal sender As System. Object, ByVal e As System.EventArgs) Handles MyBase.Load Private Excel03ConString As String = ' Provider=.4.0 Data Source='' Private Sub Form1_Load( ByVal sender As System.

How to convert excel to access table