• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

Reading Excel Fields in Visual Basic 6.0

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.

Example:

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,
0
AndrewBanfer
Asked:
AndrewBanfer
  • 2
1 Solution
 
omegaomegaDeveloperCommented:
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].

Cheers,
Randy
0
 
AndrewBanferAuthor Commented:
Randy,

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,

Andrew

1-16-09.xls
0
 
omegaomegaDeveloperCommented:
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.)

Cheers,
Randy



0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now