Eric,

This is due to the way the query 'assumes' the data type I believe. So, your
data is such that it is pre-dominantly text, so it is all assumed as text.

But why use ADO, why not just open the Excel workbook and write it as a text
file?

--

HTH

Bob Phillips

"Eric" wrote in message
...
I'm trying to read in Excel data and write it out to a text file using the
code listed below. It mostly works, except it doesn't recognize numbers.
If a cell in the Excel document has - 7 - VB says the cell is empty. If
it
is explicitly a string - '7 - it pulls in fine. If it contains any
characters other than a number - 7.0 - it pulls in fine. Why does it
ignore
just plain numbers? Is there a command I'm missing? I would like to be
able to pull in numbers without explicitly specifying each call to start
with a single quote.




sSourceData = Infile

'Open the ADO connection to the Excel workbook
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sSourceData & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

'Assign worksheet name
Dim sTableName As String

sTableName = "[sheet1$]"

'Get the recordset
Dim oRS As ADODB.Recordset, nCols As Integer

Set oRS = New ADODB.Recordset
oRS.Open sTableName, oConn, adOpenStatic, adLockOptimistic
nCols = oRS.Fields.Count

'Display the field names
Dim I As Integer, sFields As String, sData As String
For I = 0 To nCols - 1
sFields = sFields & oRS.Fields(I).Name & vbTab
Next
Write #1, sFields

'Display the records
Do While Not oRS.EOF
sData = ""
For I = 0 To nCols - 1
sData = sData & oRS.Fields(I).Value & vbTab
Next
Write #1, sData
oRS.MoveNext
Loop

'Close the recordset and the connection
oRS.Close
oConn.Close

Open as in Open with the Excel Application? No, I need to read it in using
VB. I don't know any way to read the data into VB other than those ADO
commands. Does Excel have a spreadsheet to text conversion from a DOS
prompt? The VB program is to be a process which can convert the
spreadsheets to text files; to be called from another application, which can
then read in the text file. The other application cannot read in Excel
spreadsheets. If there's a DOS command that can convert Excel to text, that
might work, though the VB would still be nice as it can manipulate the text
as it writes it out if necessary.

I tried using a different connection string (below) to avoid using ADO, but
that got the same results. I would need to know different commands to pull
in the data it seems.

oConn.Open "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Extended Properties=DBQ=" & filename & ";" & _
"DefaultDir=" & pathname & ";" & _
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;" &
_

"MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=
0;" & _
"Threads=3;UID=admin;UserCommitSync=Yes;"

"Bob Phillips" wrote in message
...
Eric,

This is due to the way the query 'assumes' the data type I believe. So,
your
data is such that it is pre-dominantly text, so it is all assumed as text.

But why use ADO, why not just open the Excel workbook and write it as a
text
file?

--

HTH

Bob Phillips

I think Bob meant something like the follwing - done in VB6. The VB6 project
would have to reference Excel - unless you did this with late binding (not
shown).

Sub ReadExcel(sFile as string)

Dim xl as new Excel.Application
Dim xwb as Excel.Workbook
Dim iRow as integer
Dim iCol as integer
Dim vTmp as variant
Dim sLine as string
CONST DELIMITER=","

set xwb xl.workbooks.open(sFile)
for iRow = 1 to 10
for iCol = 1 to 5
vTmp = xwb.cells(iRow,iCol)
sLine = sLine & DELIMITER & vTmp
next iCol
' write sLine to the file here...maybe trim the leading delimiter
sLine = ""
next iRow

end sub

The syntax may not be exact, I'm just showing the idea...

Steve



"Eric" wrote in message
...
Open as in Open with the Excel Application? No, I need to read it in
using
VB. I don't know any way to read the data into VB other than those ADO
commands. Does Excel have a spreadsheet to text conversion from a DOS
prompt? The VB program is to be a process which can convert the
spreadsheets to text files; to be called from another application, which
can
then read in the text file. The other application cannot read in Excel
spreadsheets. If there's a DOS command that can convert Excel to text,
that
might work, though the VB would still be nice as it can manipulate the
text
as it writes it out if necessary.

I tried using a different connection string (below) to avoid using ADO,
but
that got the same results. I would need to know different commands to
pull
in the data it seems.

oConn.Open "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Extended Properties=DBQ=" & filename & ";" & _
"DefaultDir=" & pathname & ";" & _
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;"
&
_


"MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=
0;" & _
"Threads=3;UID=admin;UserCommitSync=Yes;"

"Bob Phillips" wrote in message
...
Eric,

This is due to the way the query 'assumes' the data type I believe. So,
your
data is such that it is pre-dominantly text, so it is all assumed as
text.

But why use ADO, why not just open the Excel workbook and write it as a
text
file?

--

HTH

Bob Phillips

After determining that ADO does not work, since it cannot read an Excel Cell
with just a number in it:
I started down the path you're talking about here. I managed to open the
excel file and could see it in the debug watch, but couldn't figure out how
to read the cells. I tried this code you put in here, and got runtime error
438 "Object does not support this property of method" on the line: vTmp =
xwb.Cells(iRow.iCol)
Is this not the right way to read in cells? Am I missing one of those
Project - References things? I have it referencing:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
Microsoft Data Environment Instance 1.0 (SP4)
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Excel 9.0 Object Library
(removed Microsoft ADO reference which should no longer be needed)

"S. Daum" wrote in message
...
I think Bob meant something like the follwing - done in VB6. The VB6
project
would have to reference Excel - unless you did this with late binding (not
shown).

Sub ReadExcel(sFile as string)

Dim xl as new Excel.Application
Dim xwb as Excel.Workbook
Dim iRow as integer
Dim iCol as integer
Dim vTmp as variant
Dim sLine as string
CONST DELIMITER=","

set xwb xl.workbooks.open(sFile)
for iRow = 1 to 10
for iCol = 1 to 5
vTmp = xwb.cells(iRow,iCol)
sLine = sLine & DELIMITER & vTmp
next iCol
' write sLine to the file here...maybe trim the leading delimiter
sLine = ""
next iRow

end sub

The syntax may not be exact, I'm just showing the idea...

Steve

I fixed that error.. it was looking for xwb.Activesheet.Cells(iRow,iCol)
Now all I need is to figure out which portion of which of these variables
contains the numbers for the actual last column and row that has a value.
Thanks Steve

....and thanks for trying Tom, but unless I'm really missing something, ADO
doesn't work. I have text in the first few rows in column A, and further
down column A has numbers. ADO does not read in the numbers. It sounds
like the Excel object thing is what I was looking for.

"Eric" wrote in message
...
I started down the path you're talking about here. I managed to open the
excel file and could see it in the debug watch, but couldn't figure out
how
to read the cells. I tried this code you put in here, and got runtime
error
438 "Object does not support this property of method" on the line: vTmp =
xwb.Cells(iRow.iCol)
....

xwb.Cells(iRow.iCol)

iRow should be followed by a comma not a period...



"Eric" wrote in message
...
After determining that ADO does not work, since it cannot read an Excel
Cell
with just a number in it:
I started down the path you're talking about here. I managed to open the
excel file and could see it in the debug watch, but couldn't figure out
how
to read the cells. I tried this code you put in here, and got runtime
error
438 "Object does not support this property of method" on the line: vTmp =
xwb.Cells(iRow.iCol)
Is this not the right way to read in cells? Am I missing one of those
Project - References things? I have it referencing:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
Microsoft Data Environment Instance 1.0 (SP4)
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Excel 9.0 Object Library
(removed Microsoft ADO reference which should no longer be needed)

"S. Daum" wrote in message
...
I think Bob meant something like the follwing - done in VB6. The VB6
project
would have to reference Excel - unless you did this with late binding
(not
shown).

Sub ReadExcel(sFile as string)

Dim xl as new Excel.Application
Dim xwb as Excel.Workbook
Dim iRow as integer
Dim iCol as integer
Dim vTmp as variant
Dim sLine as string
CONST DELIMITER=","

set xwb xl.workbooks.open(sFile)
for iRow = 1 to 10
for iCol = 1 to 5
vTmp = xwb.cells(iRow,iCol)
sLine = sLine & DELIMITER & vTmp
next iCol
' write sLine to the file here...maybe trim the leading
delimiter
sLine = ""
next iRow

end sub

The syntax may not be exact, I'm just showing the idea...

Steve

http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

It discusses this down around the middle of the article. talks about an
extended property of IMEX=1

To work around this problem for read-only data, enable Import Mode by using
the setting "IMEX=1" in the Extended Properties section of the connection
string. This enforces the ImportMixedTypes=Text registry setting. However,
note that updates may give unexpected results in this mode. For additional
information about this setting, click the article number below to view the
article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset



--

Regards,

Tom Ogilvy




"Tom Ogilvy" wrote in message
...
Maybe some useful information he

http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

--
Regards,
Tom Ogilvy


"Eric" wrote in message
...
Open as in Open with the Excel Application? No, I need to read it in
using
VB. I don't know any way to read the data into VB other than those ADO
commands. Does Excel have a spreadsheet to text conversion from a DOS
prompt? The VB program is to be a process which can convert the
spreadsheets to text files; to be called from another application, which
can
then read in the text file. The other application cannot read in Excel
spreadsheets. If there's a DOS command that can convert Excel to text,
that
might work, though the VB would still be nice as it can manipulate the
text
as it writes it out if necessary.

I tried using a different connection string (below) to avoid using ADO,
but
that got the same results. I would need to know different commands to
pull
in the data it seems.

oConn.Open "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Extended Properties=DBQ=" & filename & ";" & _
"DefaultDir=" & pathname & ";" & _
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel
8.0;"
&
_



"MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=
0;" & _
"Threads=3;UID=admin;UserCommitSync=Yes;"

"Bob Phillips" wrote in message
...
Eric,

This is due to the way the query 'assumes' the data type I believe.
So,
your
data is such that it is pre-dominantly text, so it is all assumed as
text.

But why use ADO, why not just open the Excel workbook and write it as
a
text
file?

--

HTH

Bob Phillips

Ok, I added IMEX=1 and it worked. No, we won't be writing to Excel so that
should be fine. The other way should work too once I figure out if there's
a way to tell it the last row and column that contains data, or if that will
work with a while loop that gets next instead of the for-next.
Is there an advantage to using ADO or the Excel.Application,
Excel.Workbook...?


"Tom Ogilvy" wrote in message
...
http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

It discusses this down around the middle of the article. talks about an
extended property of IMEX=1

To work around this problem for read-only data, enable Import Mode by
using
the setting "IMEX=1" in the Extended Properties section of the connection
string. This enforces the ImportMixedTypes=Text registry setting. However,
note that updates may give unexpected results in this mode. For additional
information about this setting, click the article number below to view the
article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset



--

Regards,

Tom Ogilvy

Here is a guy posting a couple of screens above this thread that seems to
have had a similar problem as you - then he says:

-------------------------
From: Goh Siang Hwee
References:
Subject: ado problem in excel
Message-ID:
Newsgroups: microsoft.public.excel.programming
Date: Wed, 27 Aug 2003 20:35:46 -0700

Hi, thanks Rob. I have solved my problem by using other way. I set the
connection string "Extended Properties" to IMEX=1 and all the problem
solved.

Thanks anyway.
Really appreciate for your help.

Regards,
Goh Siang Hwee


-------------------------

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

It discusses this down around the middle of the article. talks about an
extended property of IMEX=1

To work around this problem for read-only data, enable Import Mode by
using
the setting "IMEX=1" in the Extended Properties section of the connection
string. This enforces the ImportMixedTypes=Text registry setting. However,
note that updates may give unexpected results in this mode. For additional
information about this setting, click the article number below to view the
article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset



--

Regards,

Tom Ogilvy




"Tom Ogilvy" wrote in message
...
Maybe some useful information he

http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

--
Regards,
Tom Ogilvy


"Eric" wrote in message
...
Open as in Open with the Excel Application? No, I need to read it in
using
VB. I don't know any way to read the data into VB other than those
ADO
commands. Does Excel have a spreadsheet to text conversion from a DOS
prompt? The VB program is to be a process which can convert the
spreadsheets to text files; to be called from another application,
which
can
then read in the text file. The other application cannot read in
Excel
spreadsheets. If there's a DOS command that can convert Excel to
text,
that
might work, though the VB would still be nice as it can manipulate the
text
as it writes it out if necessary.

I tried using a different connection string (below) to avoid using
ADO,
but
that got the same results. I would need to know different commands to
pull
in the data it seems.

oConn.Open "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Extended Properties=DBQ=" & filename & ";" & _
"DefaultDir=" & pathname & ";" & _
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel
8.0;"
&
_




"MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=
0;" & _
"Threads=3;UID=admin;UserCommitSync=Yes;"

"Bob Phillips" wrote in message
...
Eric,

This is due to the way the query 'assumes' the data type I believe.
So,
your
data is such that it is pre-dominantly text, so it is all assumed as
text.

But why use ADO, why not just open the Excel workbook and write it
as
a
text
file?

--

HTH

Bob Phillips

.... but note that ADO is many times faster than using automation.

"Tom Ogilvy" wrote in message ...
I don't think you need to have Excel installed to use ADO.

Obviously managing Excel through automation offers much, much more
capability.

--
Regards,
Tom Ogilvy

Eric wrote in message
...
Ok, I added IMEX=1 and it worked. No, we won't be writing to Excel so
that
should be fine. The other way should work too once I figure out if
there's
a way to tell it the last row and column that contains data, or if that
will
work with a while loop that gets next instead of the for-next.
Is there an advantage to using ADO or the Excel.Application,
Excel.Workbook...?


"Tom Ogilvy" wrote in message
...
http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

It discusses this down around the middle of the article. talks about an
extended property of IMEX=1

To work around this problem for read-only data, enable Import Mode by
using
the setting "IMEX=1" in the Extended Properties section of the
connection
string. This enforces the ImportMixedTypes=Text registry setting.
However,
note that updates may give unexpected results in this mode. For
additional
information about this setting, click the article number below to view
the
article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset



--

Regards,

Tom Ogilvy