Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data into VB 6.0
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Write Macro to Fix Data Alignment (Data dump from Crystal to Excel | Excel Discussion (Misc queries) | |||
Email (LDAP) data download into a single Excel cell - data separat | Excel Worksheet Functions | |||
excel 2007, how to select a data point and cycle through data points | Charts and Charting in Excel | |||
Eliminating rows of data in excel spreadsheet that have blank cell in row A and data in row B - E | Excel Programming |