View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Rob Nicholson Rob Nicholson is offline
external usenet poster
 
Posts: 4
Default ADO inserts text strings not numbers when table is empty

Bit of a stab in the dark this one. We're using standard ADO to access data
stored in an Excel workbook (don't ask why) and have a slight problem.
Consider a simple sheet containing the following heading and data:

Col1,Col2
1,3
2,4

If you execute a command like this:

Dim cmd As New ADODB.Command
cmd.Connection=ActiveConnection
cmd.CommandText="Insert Into [Data$] (Col1,Col2) Values (3,5)"
cmd.Execute

It works fine - the extra row is added contain the two *numbers* 3 & 5.
HOWEVER, if you delete the data rows out of the worksheet/table leaving just
the headings and carry out the same command, two *text* strings are written,
i.e. '3 and '5. I'm assuming that this is because Excel is unable to
determine the data types of the fields when the table is empty and therefore
defaults to text.

This can be confirmed by opening up a recordset on the empty table and
delving around the Fields collection. When the table is empty, Fields(0) is
a text string. However, then when with numbers, Fields(0) is a double.

Is there anyway around this? Some way to tell Excel the data types of the
fields in an empty Excel table?

Cheers, Rob.