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