473,320 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Column Type

Hi,

I have a Acces Db with one Table. The Table has 4 rows (row 1 & 3 are
integer and 2 & 4 are text).
Now i want to export the Table to an Excel workbook.
the next code i used is working (almost) fine.

///Code
'Create a Recordset from all the records in the Orders table.
Dim sNWind As String
Dim conn As New ADODB.Connection()
Dim rs As ADODB.Recordset
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNorthwind & ";")
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

'Transfer the field names to Row 1 of the worksheet:
'Note: CopyFromRecordset copies only the data and not the field
' names, so you can transfer the fieldnames by traversing the
' fields collection.
Dim n As Int32
For n = 1 To rs.Fields.Count
oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
Next

'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(rs)

'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book3.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

'Close the connection
rs.Close()
conn.Close()
///Code

There is one thing that the code does not. And that is, copying the
columnproperties(integer, text) to Excel.

Does anyone know how i can acomplish this.

Thanks in advance
Nov 21 '05 #1
14 1748
Niels,

Why are you using Adodb wherefore you use interop while there is ADONET.

Here is in my idea a complete sample how to do what you want using ADONET.

http://support.microsoft.com/default...b;EN-US;316934

I hope this helps?

Cor

"Niels" <ni****@hollandridderkerk.nl>
Hi,

I have a Acces Db with one Table. The Table has 4 rows (row 1 & 3 are
integer and 2 & 4 are text).
Now i want to export the Table to an Excel workbook.
the next code i used is working (almost) fine.

///Code
'Create a Recordset from all the records in the Orders table.
Dim sNWind As String
Dim conn As New ADODB.Connection()
Dim rs As ADODB.Recordset
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNorthwind & ";")
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

'Transfer the field names to Row 1 of the worksheet:
'Note: CopyFromRecordset copies only the data and not the field
' names, so you can transfer the fieldnames by traversing the
' fields collection.
Dim n As Int32
For n = 1 To rs.Fields.Count
oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
Next

'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(rs)

'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book3.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

'Close the connection
rs.Close()
conn.Close()
///Code

There is one thing that the code does not. And that is, copying the
columnproperties(integer, text) to Excel.

Does anyone know how i can acomplish this.

Thanks in advance

Nov 21 '05 #2
Niels,

Why are you using Adodb wherefore you use interop while there is ADONET.

Here is in my idea a complete sample how to do what you want using ADONET.

http://support.microsoft.com/default...b;EN-US;316934

I hope this helps?

Cor

"Niels" <ni****@hollandridderkerk.nl>
Hi,

I have a Acces Db with one Table. The Table has 4 rows (row 1 & 3 are
integer and 2 & 4 are text).
Now i want to export the Table to an Excel workbook.
the next code i used is working (almost) fine.

///Code
'Create a Recordset from all the records in the Orders table.
Dim sNWind As String
Dim conn As New ADODB.Connection()
Dim rs As ADODB.Recordset
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNorthwind & ";")
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

'Transfer the field names to Row 1 of the worksheet:
'Note: CopyFromRecordset copies only the data and not the field
' names, so you can transfer the fieldnames by traversing the
' fields collection.
Dim n As Int32
For n = 1 To rs.Fields.Count
oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
Next

'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(rs)

'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book3.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

'Close the connection
rs.Close()
conn.Close()
///Code

There is one thing that the code does not. And that is, copying the
columnproperties(integer, text) to Excel.

Does anyone know how i can acomplish this.

Thanks in advance

Nov 21 '05 #3
On Thu, 11 Nov 2004 10:23:32 +0100, "Cor Ligthert" <no************@planet.nl> wrote:

¤ Niels,
¤
¤ Why are you using Adodb wherefore you use interop while there is ADONET.
¤

CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't support ADO.NET.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #4
On Thu, 11 Nov 2004 10:23:32 +0100, "Cor Ligthert" <no************@planet.nl> wrote:

¤ Niels,
¤
¤ Why are you using Adodb wherefore you use interop while there is ADONET.
¤

CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't support ADO.NET.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #5
>
CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't
support ADO.NET.

I know that, did I write that somewhere Paul?

Cor
Nov 21 '05 #6
>
CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't
support ADO.NET.

I know that, did I write that somewhere Paul?

Cor
Nov 21 '05 #7
On 10 Nov 2004 23:23:55 -0800, ni****@hollandridderkerk.nl (Niels) wrote:

¤ Hi,
¤
¤ I have a Acces Db with one Table. The Table has 4 rows (row 1 & 3 are
¤ integer and 2 & 4 are text).
¤ Now i want to export the Table to an Excel workbook.
¤ the next code i used is working (almost) fine.
¤
¤ ///Code
¤ 'Create a Recordset from all the records in the Orders table.
¤ Dim sNWind As String
¤ Dim conn As New ADODB.Connection()
¤ Dim rs As ADODB.Recordset
¤ conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
¤ sNorthwind & ";")
¤ conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
¤ rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)
¤
¤ 'Create a new workbook in Excel.
¤ Dim oExcel As Object
¤ Dim oBook As Object
¤ Dim oSheet As Object
¤ oExcel = CreateObject("Excel.Application")
¤ oBook = oExcel.Workbooks.Add
¤ oSheet = oBook.Worksheets(1)
¤
¤ 'Transfer the field names to Row 1 of the worksheet:
¤ 'Note: CopyFromRecordset copies only the data and not the field
¤ ' names, so you can transfer the fieldnames by traversing the
¤ ' fields collection.
¤ Dim n As Int32
¤ For n = 1 To rs.Fields.Count
¤ oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
¤ Next
¤
¤ 'Transfer the data to Excel.
¤ oSheet.Range("A2").CopyFromRecordset(rs)
¤
¤ 'Save the workbook and quit Excel.
¤ oBook.SaveAs(sSampleFolder & "Book3.xls")
¤ oSheet = Nothing
¤ oBook = Nothing
¤ oExcel.Quit()
¤ oExcel = Nothing
¤ GC.Collect()
¤
¤ 'Close the connection
¤ rs.Close()
¤ conn.Close()
¤ ///Code
¤
¤ There is one thing that the code does not. And that is, copying the
¤ columnproperties(integer, text) to Excel.
¤
¤ Does anyone know how i can acomplish this.
¤

Excel determines the data types based upon the pattern of the incoming data. It does not consider
the data type of each column in the source.

I don't know how you are checking the column properties but each column should have a data type that
was determined at the time of import.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #8
On 10 Nov 2004 23:23:55 -0800, ni****@hollandridderkerk.nl (Niels) wrote:

¤ Hi,
¤
¤ I have a Acces Db with one Table. The Table has 4 rows (row 1 & 3 are
¤ integer and 2 & 4 are text).
¤ Now i want to export the Table to an Excel workbook.
¤ the next code i used is working (almost) fine.
¤
¤ ///Code
¤ 'Create a Recordset from all the records in the Orders table.
¤ Dim sNWind As String
¤ Dim conn As New ADODB.Connection()
¤ Dim rs As ADODB.Recordset
¤ conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
¤ sNorthwind & ";")
¤ conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
¤ rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)
¤
¤ 'Create a new workbook in Excel.
¤ Dim oExcel As Object
¤ Dim oBook As Object
¤ Dim oSheet As Object
¤ oExcel = CreateObject("Excel.Application")
¤ oBook = oExcel.Workbooks.Add
¤ oSheet = oBook.Worksheets(1)
¤
¤ 'Transfer the field names to Row 1 of the worksheet:
¤ 'Note: CopyFromRecordset copies only the data and not the field
¤ ' names, so you can transfer the fieldnames by traversing the
¤ ' fields collection.
¤ Dim n As Int32
¤ For n = 1 To rs.Fields.Count
¤ oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
¤ Next
¤
¤ 'Transfer the data to Excel.
¤ oSheet.Range("A2").CopyFromRecordset(rs)
¤
¤ 'Save the workbook and quit Excel.
¤ oBook.SaveAs(sSampleFolder & "Book3.xls")
¤ oSheet = Nothing
¤ oBook = Nothing
¤ oExcel.Quit()
¤ oExcel = Nothing
¤ GC.Collect()
¤
¤ 'Close the connection
¤ rs.Close()
¤ conn.Close()
¤ ///Code
¤
¤ There is one thing that the code does not. And that is, copying the
¤ columnproperties(integer, text) to Excel.
¤
¤ Does anyone know how i can acomplish this.
¤

Excel determines the data types based upon the pattern of the incoming data. It does not consider
the data type of each column in the source.

I don't know how you are checking the column properties but each column should have a data type that
was determined at the time of import.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #9
On Thu, 11 Nov 2004 17:08:08 +0100, "Cor Ligthert" <no************@planet.nl> wrote:

¤ >
¤ > CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't
¤ > support ADO.NET.
¤ >
¤ I know that, did I write that somewhere Paul?
¤
¤ Cor
¤

You asked him why he was using ADODB when he could be using ADO.NET.

If you're suggesting he abandon both ADO *and* Excel automation then you are correct in that he can
use ADO.NET instead. Otherwise, if he needs to use Excel automation then he has no choice other than
to use ADO.

Does that make sense?
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #10
On Thu, 11 Nov 2004 17:08:08 +0100, "Cor Ligthert" <no************@planet.nl> wrote:

¤ >
¤ > CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't
¤ > support ADO.NET.
¤ >
¤ I know that, did I write that somewhere Paul?
¤
¤ Cor
¤

You asked him why he was using ADODB when he could be using ADO.NET.

If you're suggesting he abandon both ADO *and* Excel automation then you are correct in that he can
use ADO.NET instead. Otherwise, if he needs to use Excel automation then he has no choice other than
to use ADO.

Does that make sense?
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #11
Thanks you all for your answers.

I have a AccesDB as input and it must be output to Excel.

I going to give the sample that Cor Ligthert gave a try.

Yesterday i tried to run my program on a machine that has no Excel
installed, and of course the program crashed.
Is it possible to make a Excel workbook without Excel being installed???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #12
Thanks you all for your answers.

I have a AccesDB as input and it must be output to Excel.

I going to give the sample that Cor Ligthert gave a try.

Yesterday i tried to run my program on a machine that has no Excel
installed, and of course the program crashed.
Is it possible to make a Excel workbook without Excel being installed???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #13
On Thu, 11 Nov 2004 21:40:38 -0800, Niels <Ni***@devdex.com> wrote:

¤ Thanks you all for your answers.
¤
¤ I have a AccesDB as input and it must be output to Excel.
¤
¤ I going to give the sample that Cor Ligthert gave a try.
¤
¤ Yesterday i tried to run my program on a machine that has no Excel
¤ installed, and of course the program crashed.
¤ Is it possible to make a Excel workbook without Excel being installed???

I didn't see in your code where you were creating an Excel Workbook file but there are only two
options. You either need to use Excel and automation or ADOX (Microsoft ADO Ext 2.x for DDL and
Security).

One other method would be to use a blank Excel Workbook template (file).
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #14
On Thu, 11 Nov 2004 21:40:38 -0800, Niels <Ni***@devdex.com> wrote:

¤ Thanks you all for your answers.
¤
¤ I have a AccesDB as input and it must be output to Excel.
¤
¤ I going to give the sample that Cor Ligthert gave a try.
¤
¤ Yesterday i tried to run my program on a machine that has no Excel
¤ installed, and of course the program crashed.
¤ Is it possible to make a Excel workbook without Excel being installed???

I didn't see in your code where you were creating an Excel Workbook file but there are only two
options. You either need to use Excel and automation or ADOX (Microsoft ADO Ext 2.x for DDL and
Security).

One other method would be to use a blank Excel Workbook template (file).
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: aspnetpal | last post by:
Hi Every1, I'm getting this error. 'Cannot specify a column width on data type smalldatetime.' I'm using SQL Server & I've check the data type in the db & it's same as smalldatetime. Any...
4
by: Brian Brane | last post by:
I have properties that wrap DataRow columns as in: public int aNumber { get{ return m_DataRow; } set{ m_DataRow = value; } } If the column happens to contain DBNull, I get a cast exception...
6
by: Robert Schuldenfrei | last post by:
Dear NG, After being away from C# programming for a spell, I am trying my hand at what should be a simple task. I have been hitting my head against the wall this morning. I have a simple order...
3
by: Bob Day | last post by:
VS 2003, sql How do you determine the data type of a column if its value is DBNull? 1)Table: Column1 STRING non-nullable 2) Fill to a DataSet via DataAdapter 3) dim Data_Type_Is...
4
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which -...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.