>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
Agnes,
20mins for 5K rows is simply unacceptable. As I can see you have taken the worst path that you could in Excel automation. You're using SQLClient, querying SQL server, sending results to Excel cell-by-cell!!! Instead directly query SQL server from excel (and I'm talking about subseconds vs 20 mins).
Cetin