Export to Excel From Two different database ?

A

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
 
C

Chris

Agnes said:
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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export data to excel too slow 4
After export to Excel, that excel cannot open 3
Need a little help... 1
CSV File 5
large dataSet (continued) 5
faster export to excel? 7
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Export single rows from a query to excel using a template 1

Top