Export to Excel From Two different database ?

19 Feb 2006 2:16 PM

Agnes

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

19 Feb 2006 4:59 PM

Chris

Agnes wrote:
Show quoteHide quote

> 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
>
>

If you don't need to format the cells, it would be faster to save a file
as a csv file and tell excel to open it. Just a thought for you.

20 Feb 2006 5:21 AM

JL

Check out this link...it is excellent. Not sure about using the second
database. I would probably create a datatable and add records to it
from both databases.

http://www.carlosag.net/default.aspx

John

Show quoteHide quote

On Sun, 19 Feb 2006 22:16:34 +0800, "Agnes" <ag***@dynamictech.com.hk>
wrote:

>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
>