Column Type

[Niels]

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

 

[Cor Ligthert]

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.aspx?scid=kb;EN-US;316934

I hope this helps?

Cor

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Cor Ligthert]

CopyFromRecordset requires an ADO or DAO Recordset object. It doesn't
support ADO.NET.

I know that, did I write that somewhere Paul?

Cor

 

[Paul Clement]

On 10 Nov 2004 23:23:55 -0800, (e-mail address removed) (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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Paul Clement]

¤ >
¤ > 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Niels]

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

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)