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

Column Type

P: n/a
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
Share this Question
Share on Google+
14 Replies


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

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

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

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

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

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

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

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

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

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

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

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

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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.