Reading Excel Fields in Visual Basic 6.0

Posted on 2009-02-23
Last Modified: 2012-05-06
I opened an Excel Spreadsheet through a Data Recordset in Visual Basic 6.0 and I'm able to move down rows.  When I try to read fields half the time I can read the value but have the time I get a Numeric Overflow.


Temporary Variable = data20.recordset.fields(0)

Sometimes the Temporary Variable gets a value but sometimes I get a Numeric Overflow.

Is there a better way to read the value of the Excel Cell?

Thank you,
Question by:AndrewBanfer
    LVL 12

    Expert Comment

    Hello, Andrew,

    If you open the Excel Workbook manually and examine the values of the cells that cause the problem, what values do you see?   How does the magnitude of these values compare with the range that is possible for "Temporary Variable"?  For example, if I remember correctly, an integer in VB6 is only 16 bits.  That is, if "Temporary Variable" is dimensioned as an Integer, then its range will be limited to [-32768,+32767].


    Author Comment


    I put the data20.recordset.fields(0) in my Watch List to remove the Temporary Variable dimension as being an issue.

    The result is that I sometimes get the numeric overflow error with text such as "apb" (Analyst:) or numbers like "88" (TSS Column) or "0.114" (Initial Wt Column).  It works for a Date Field (Date:), Time Field (Time:), and other Text Fields (Sample # Column).

    The "88" (TSS Column) is derived from a calculation but the "0.114" (Initial Wt. Column) is a direct entry.

    I'm attaching the Spreadsheet as an example.

    Thanks very much,


    LVL 12

    Accepted Solution

    Hi, Andrew,

    It doesn't look as if there is anything in the Worksheet that should cause a problem.  It's a bit odd that you would get numeric overflow errors from fields that contain only text.  Were your temporary variable a numeric type, I might expect a conversion error but not an overflow.  (Having seen the spreadsheet, I'm assuming that the temporary variable is Variant type.)

    Perhaps the problem has something to do with the way that Data20 is configured.  Unfortunately, I no longer even have VB6 installed and so cannot test things and I don't have any experience with accessing Excel data via a query.  I've always just used VB to open Excel directly and get the values that I need.  Maybe this relates to your question ("Is there a better way to read the value of the Excel Cell?")

    Is there a basic reason to use the Data Recordset, or could you instead do something like:

        Dim xlApp       As New Excel.Application    ' excel application.
        Dim wbkResults As Excel.Workbook          
        Dim wshResults As Excel.Worksheet          
        Dim celCurrent    As Range
        Dim intRow         As Integer
        Dim intCol            As Integer

        Set wshResults = xlApp.Workbooks.Open("...\1-16-09.xls")
        Set wshResults = wshResults.Worksheets("Sheet1")
        intRow = ...
        intCol = ...
        Set celMark = wshResults.Cells(intRow, intCol)
        Temporary Variable = celMark.Value

    (Of course, that's completely untested.)

    Also, you might get better advice if you try posting your question in a VB6 specific forum.  (This one is oriented to VB.Net, which is a quite different beastie.)



    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now