Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002

    Unanswered: how read data from excel?

    i have a visual basic program and i need read data from a excel sheet and put them in an array after. i would not like use data control or another data access control.
    any idea?
    Last edited by byte2002; 01-24-03 at 09:29.

  2. #2
    Join Date
    Jul 2001
    Moving data from an excel worksheet into a memory array takes 5 simple steps:

    1 Add the following items to the program References
    Microsoft ActiveX 2.5 for DDL and Security
    Microsoft Excel 8.0 Object Library

    2. Predefine memory variables in the globals.bas file
    Public m_XLSconx As ADODB.Connection
    Public m_XLScmd As ADODB.Command
    Public m_XLSrs As ADODB.Recordset
    Public m_XLSdir As String
    Public m_XLSfile As String
    Public m_strSQL As String

    3 Establish an ADO connection to Excel worksheet in the globals.bas file
    Sub XLSCONX()
    m_xlsdir = "" 'where the XLS file located
    m_xlsfile = "" 'name of XLS file
    Set m_XLScmd = New ADODB.Command
    Set m_XLSconx = New ADODB.Connection
    Set m_XLSrs = New ADODB.Recordset
    With m_XLSconx
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source = " & m_XLSdir & _
    "\" & _m_XLSfile & ";" & _
    "Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    End With
    End Sub

    4. Establish a way to drop Excel-ADO connection in the globals.bas file
    sub XLSDROP()
    Set m_XLScmd = Nothing
    Set m_XLSconx = Nothing
    Set m_XLSrs = Nothing
    end sub

    5. In one of the forms, connect to Excel worksheet, move data into the data array, and drop connection
    dim refCells as string
    dim dataArray() as string
    dim colMax as integer
    dim colCnt as integer
    dim rowMax as integer
    dim rowCnt as integer

    ' make XLS ADO connection

    'estblish data range
    referCells = "<top left cell>:<bottom right cell>"

    'select data from worksheet data range
    m_strSQL = "SELECT * FROM [<worksheet name>$" & referCells & "]"

    'open recordset readonly
    m_XLSrs.Open m_strSQL, m_XLSconx, adOpenStatic, adLockReadOnly, -1

    'got to first record

    'setup 2-dimensional data array to fit rows and columns in data range
    ReDim dataArray(rowMax, colMax)

    'load 2-dimensional array with data
    For rowCnt = 0 To rowMax - 1
    colCnt = 0
    For colCnt = 0 To colMax - 1
    dataArray(rowCnt, colCnt) = "" & m_XLSrs.Fields(colCnt).Value

    'drop XLS ADO connection

    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts