I can export the data to an excel(quit slow , for 5k records, It need 20mins)
Now, my problem is how can I join another table from another database ?
"select I.invno,I.company,C.telno,C.faxno from invoice I,company C where I.company = C.company"
Thanks a lot
Dim Excel As New Excel.Application
Dim intColumn, intRow, intColumnValue As Integer
Dim strExcelFile As String
Dim strFileName As String
daExcelExport.SelectCommand = New SqlCommand
daExcelExport.SelectCommand.Connection = _con
daExcelExport.TableMappings.Add("Table", "invoice")
daExcelExport.SelectCommand.CommandText = "select invno,company from invoice "
daExcelExport = New System.Data.SqlClient.SqlDataAdapter(strSql, _con)
daExcelExport.Fill(dsExcelExport)

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 + 2, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
Next
Next
''strFileName = InputBox("Please enter the file name.", "Swapnil")
strExcelFile = _pFilePath
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close()
End With