Newsgroup: it.comp.lang.visual-basic
Titolo: Using Jet data control to write numbers to an excel spreadsheet?

Hi,
I'm currently using the vb data controls to write numbers to excel 
spreadsheets using the Jet driver.
Unfortunately, even though the data being sent to the spreadsheet is 
numbers, excel has a nasty habit of showing them as text, requiring them 
to be selected and choosing "Convert text to number".
Is there a way to force the numbers to be stored as numbers in the excel 
spreadsheet? I've tried searching the internet and haven't been able to 
find an answer.

 wrote:
¤ Hi,

Can you format the column in the Worksheet for numeric values? Chances are the default is text.
Paul
~~~~
Microsoft MVP (Visual Basic)


No, that doesn't work. If you format the column for numbers and then 
have the data written, then you can right click on a cell and select 
format, and it will say number.
However, you still get the little triangles that when selected, say that 
it is a number stored as text. Similarly, if you do a simple 
mathematical operation such as summing the cells, the answer is 0 even 
though the cells obviously contain numbers greater than 0.

 wrote:
¤ > 
¤ > Can you format the column in the Worksheet for numeric values? Chances are the default is text.
¤ > 
¤ > 
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤ 
¤ No, that doesn't work. If you format the column for numbers and then 
¤ have the data written, then you can right click on a cell and select 
¤ format, and it will say number.
¤ 
¤ However, you still get the little triangles that when selected, say that 
¤ it is a number stored as text. Similarly, if you do a simple 
¤ mathematical operation such as summing the cells, the answer is 0 even 
¤ though the cells obviously contain numbers greater than 0.
Do you see the same behavior when you using ADO or DAO code instead of the Data Control?



I've only tried using the data control, which indicates it is using jet 
drivers.
Are there other methods that allow similar levels of access to spreadsheets?

 wrote:
: I've only tried using the data control, which indicates it is using
: jet drivers.
:
: Are there other methods that allow similar levels of access to
: spreadsheets?
You can encode things inside the spreadsheet and set up DAO
or ADO inside the Excel VBA Editor. Inside Excel, click on the
Tools menu, then click on Macro, then on the Visual Basic Editor
menu item.
Something to try inside the Visual Basic though, as opposed to
the Visual Basic Editor inside of Excel, would be to type out
Dim db As DAO.Database
If the VB IDE presents a drop down list the moment you type
in the period after the word DAO, then you know you're using
DAO. If not, the Data control is something else. I'm thinking that
you dragged one of those Data1 controls onto a form and that's
what you're talking about, as opposed to a third party control.
I just tried it out myself and noticed it set a reference to DAO 3.6.
I verified this by clicking the Project menu, then References. It
doesn't stop one from deselecting DAO 3.6 and configuring
something else, but I think those Data1 controls only work via DAO.
From the Excel Help...
Worksheets("Sheet1").Range("A17").NumberFormat = "General"
Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
Worksheets("Sheet1").Columns("C"). _
    NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
The NumberFormat property is the same as right-clicking inside an
Excel Sheet and then clicking on Format Cells. The first tab you
get is the Number tab for formatting numbers. It all ends up as
text, so think of that as just a way to format the text, because even
though you give it an Accounting format, that doesn't prevent any-
one from entering text there, and numbers are entered as text any-
ways.
Everything available inside Excel is pretty much available once
you create the Excel object with a VB app. And it's all just
presentation of text (until you get into calculations).
The following should work from VB as well, but it's quicker to
first test such things in Excel. In a new Excel worksheet I clicked
on View, then Toolbars, then Control Toolbox. I then dragged a
button to the form and then double-clicked on the button I just
put there. It opened the VBA Editor automatically exposing the
CommandButton1_Click() event.
Private Sub CommandButton1_Click()
  Sheet1.Cells.Range("A1").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
  Sheet1.Cells.Range("A1").Value = Sheet1.Cells.Range("C1").Text
End Sub
The .Text property returns the contents of the cell, and then you
use the .Value property to set the data in the cell. The .NumberFormat
property formats the string that gets put into the cell.
Hope this helps.
-- 
Jim Carlock
Post replies to the group.

 wrote:

Yes, you can use ADO:
ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks
http://support.microsoft.com/kb/278973/EN-US/


 posted:
: I'm currently using the vb data controls to write numbers to excel
: spreadsheets using the Jet driver.
:
: Unfortunately, even though the data being sent to the spreadsheet
: is numbers, excel has a nasty habit of showing them as text,
: requiring them to be selected and choosing "Convert text to number".
:
: Is there a way to force the numbers to be stored as numbers in the
: excel spreadsheet? I've tried searching the internet and haven't
: been able to find an answer.
The following code demonstrates how to set items in an Excel
spreadsheet. I left error trapping out so you could play with
putting different things in cell "C1" to see what happens. I only
checked for empty strings ("").
'frmTest.frm
'Place two Command1 buttons on the form, the first should get
'Index 0 and get labeled as "Start Excel", the second gets Index 1
'and label it "Numbers". This demonstrates how to put a number
'in excel.
'
'In Project, References, make sure "Microsoft Excel n.nn Object ..."
'is selected.
Option Explicit
Private oExcel As Excel.Application
Private oWB As Excel.Workbook
Private oWS As Excel.Worksheet
Private Sub Command1_Click(Index As Integer)
  Static iCount As Long
  If Index = 0 Then
    Set oExcel = New Excel.Application
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Add
    Set oWS = oExcel.Worksheets(1)
    oWS.Cells.Range("A1").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    Command1(0).Enabled = False
    Command1(1).Enabled = True
  Else
    'Put a number into cell "C1" before you click on this button.
    iCount = iCount + 33
    'NOTE: When testing, put a number into "C1" after Excel starts
    'or you get a "Type mismatch", because VB treats the cell as
    'a blank string and VB expects a number to add to the other
    'numbers.
    '
    'DAO and ADO may return NULL from empty recordset
    'objects so keep your eyes open for that.
    '
    If oWS.Cells.Range("C1").Text = "" Then
      oWS.Cells.Range("A1").Value = CDbl(iCount + 0.368)
    Else
      oWS.Cells.Range("A1").Value = CLng(oWS.Cells.Range("C1").Text) + iCount
    End If
  End If
End Sub
This should help out as you can play with what's put into "C1"
and watch what happens. You can also change the CLng to
CDbl as follows...
oWS.Cells.Range("A1").Value = CDbl(oWS.Cells.Range("C1").Text) + iCount
For the most part, though, think of it all as text. Excel transparently
handles the text and converts to numbers when doing calculations.
There's a few different ways to handle feeding data to Excel. Most
people jump in and use the Automation built into Excel and think,
Cool! And it is cool. But when it comes to data controls, consider
getting rid of the Data1 control and reading directly from the Access
file...
'If you get rid of the global Excel objects, you could pass in only
'the required Excel.Worksheet which gets updated...
'Private Sub FillExcelSheet(oWS As Excel.WorkSheet)
'The example here is based upon the code above and uses the global
'oWS.
'
Private Sub FillExcelSheet()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iRecord As Long, iCount As Long, oField As DAO.Field
Dim iField As Long
Set db = DAO.OpenRecordset("Filename.mdb")
Set rs = db.OpenRecordset("tTableName")
Call rs.MoveLast: Call rs.MoveFirst
iCount = rs.RecordCount - 1
For iRecord = 0 to iCount
  'reset field
  iField = 0
  For Each oField In rs.Fields
    oWS.Cells.Range(("A" & (iField + 1))).Value = ReadDAOString(rs.Fields(iField))
    iField = iField + 1
  Next oField
Next iRecord
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
It should be fairly close to what you need.
I didn't test the FillExcelSheet() subroutine out, so it might need an
adjustment.



Thanks for everyone's help so far. I have to present at an academic 
conference next week, and am flat out preparing my posters. However, 
I'll try all of the suggestions as soon as I have time.