By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,579 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,579 IT Pros & Developers. It's quick & easy.

Export to Excel From Two different database ?

P: n/a
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).ColumnN ame.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(int ColumnValue).ToString
Next
Next
''strFileName = InputBox("Please enter the file name.",
"Swapnil")
strExcelFile = _pFilePath
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close()
End With
Feb 19 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Agnes 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).ColumnN ame.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(int ColumnValue).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.
Feb 19 '06 #2

P: n/a
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

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).Column Name.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(in tColumnValue).ToString
Next
Next
''strFileName = InputBox("Please enter the file name.",
"Swapnil")
strExcelFile = _pFilePath
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close()
End With


Feb 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.