Problem to see specific Data in Excel Cell via VB6

From: eranRL (eranRL_at_discussions.microsoft.com)
Date: 10/20/04

  • Next message: Cliff Sutton: "Re: Can anybody show me how to open up a DAO connection to an Acce" 
    Date: Wed, 20 Oct 2004 09:35:04 -0700

    Hello all,

    I have a very wierd problem.

    I have an Excel file, that looks normaly

    I have data in the cells. that I can see while activate the
    Excell Program, can manipulate that data... like Cut paste ac..

    But when I go into my VB project..
    Some of the Cells I see as a NULL value..
    Can somebody help me, do you have an Idea how can that happend????/

    This is the code and attach is the Excell file.
    The file is Excell2000. Maybe I open the File incorrect in VB..?
    Maybe Do you have some triks that I can do?

    When I use the Record Count I see all Records.
    When I go on F3 some of the Data apears as NULL

    Thanks in Adnvace
    Eran.

    Here is the Code:

    Public Function fOpen_ConnDBExcelFrmActive()
    'Connect to the Excel DB
    'Created by Rinat Sade - iDnext Ltd.

    On Error GoTo function_error

    Dim objData As New CDBData
    Dim pszConnection As String
    Dim pszDataBase As String
    Set objCn = New ADODB.Connection
    If objCn.State = adStateOpen Then
    If objRs.status = 1 Then
    objRs.Close
    Set objRs = Nothing
    End If
    objCn.Close
    End If

    Dim glTheError As Long

    'get string connection
    'Paste the correct Excel File here !!
    pszDataBase = frmActiveForm.txtExcelFile.Text
    'glTheError = objData.fGetDBConnection(11, pszDataBase, "")
    pszConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & pszDataBase & _
    "; Extended Properties=""Excel 8.0;HDR=No;"""

    'Open connection
    objCn.Open pszConnection

    Set objData = Nothing

    Dim pszString As String
    Set objRs = New ADODB.Recordset

    If objRs.State = adStateOpen Then
    objRs.Close
    End If

    objRs.CursorType = adOpenStatic
    ' Use client cursor to allow use of
    ' AbsolutePosition property.
    objRs.CursorLocation = adUseClient

    ' Open the recordset
    pszString = "[" & frmActiveForm.txtTable.Text & "]"

    objRs.Open pszString, objCn, adOpenStatic, adLockOptimistic
    Debug.Print "Number of Records : "; objRs.RecordCount
    objRs.MoveFirst
    Do While Not objRs.EOF
      Debug.Print objRs.Fields("f3").Value
      objRs.MoveNext
    Loop

    Exit Function
    function_error:
    Debug.Print err.Description, err.Number
    End Function 

    --
Eran Harel
R & D Manager
iDnext Ltd.
  • Next message: Cliff Sutton: "Re: Can anybody show me how to open up a DAO connection to an Acce"

    Relevant Pages

    • Re: Opening Excel from Access
      ... Db is about 180 k and Excel file about 50k. ... Dim MDBName As String, DefaultDirectory As String, SQLStg As String ... If MsgBox("Do you want to use this database in future?", ...
      (comp.databases.ms-access)
    • Re: How to return a variable to a listbox on the form.
      ... > go out and select an Excel file. ... Function fncSheetNames(strPath As String) As String ... Dim tdf As DAO.TableDef ... Dirk Goldgar, MS Access MVP ...
      (microsoft.public.access.modulesdaovba)
    • Re: Opening Excel from Access
      ... I have a form with a textbox to hold the Excel file name and a command ... Dim MDBName As String, DefaultDirectory As String, SQLStg As String ... If MsgBox("Do you want to use this database in future?", ...
      (comp.databases.ms-access)
    • RE: code to create five different sql queries
      ... Excel file, not five Excel files. ... Private Sub ExportToExcelBtn_Click ... Dim IR_ListAs String ...
      (microsoft.public.access.formscoding)
    • Re: Opening Excel from Access
      ... I have a form with a textbox to hold the Excel file name and a command ... Dim MDBName As String, DefaultDirectory As String, SQLStg As String ... If MsgBox("Do you want to use this database in future?", ...
      (comp.databases.ms-access)