borderAndreaVB free resources for Visual Basic developersborder
AndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2017 Andrea Tincani
:: Read Excel and Text Files Using ADO

AndreaVB Monthly Tip Award

Author  

Mike g

Language  

VB5, VB6

Operating Systems  

Windows 95, 98, NT, Me and 2k
Module
'Read Excel File Using ADO
Public Function Read_Excel _
         (ByVal sFile _
          As String) As ADODB.Recordset

      On Error GoTo fix_err
      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      Dim sconn As String

      rs.CursorLocation = adUseClient
      rs.CursorType = adOpenKeyset
      rs.LockType = adLockBatchOptimistic

      sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile
      rs.Open "SELECT * FROM [sheet1$]", sconn
      Set Read_Excel = rs
      Set rs = Nothing
      Exit Function
fix_err:
      Debug.Print Err.Description + " " + _
                  Err.Source, vbCritical, "Import"
      Err.Clear
End Function

'*********************************************************************
'Read Text files
'You can use Extended Properties='text;FMT=Delimited'"
'By adding a third argument we can tell ADO that the file doesn't contain headers.
'The argument named HDR takes YES or NO .
'connOpen "Provider=Microsoft.Jet" _
'         & ".OLEDB.4.0;Data Source=" & App.Path _
'         & ";Extended Properties='text;HDR=NO;" _
'         & "FMT=Delimited'"
'You can use Microsoft Text Driver or Microsoft.Jet
'*********************************************************************

Public Function Read_Text_File() As ADODB.Recordset

      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      Dim conn As ADODB.Connection
      Set conn = New ADODB.Connection
      conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
                  "DBQ=" & App.Path & ";", "", ""

      rs.Open "select * from [test#txt]", conn, adOpenStatic, _
                  adLockReadOnly, adCmdText
      Set Read_Text_File = rs
      Set rs = Nothing
      Set conn = Nothing
End Function
:: Navigation

Home

Database Management

Previous Tip

Next Tip

:: Search this site
Google
:: Related Topics
icon 22-06-2013 Automatic Image ID creator by seejr
icon 22-06-2013 Automatic Image ID creator by seejr
icon 15-02-2011 How to Add a Formula for Excel by Daisy09
icon 18-06-2009 Lotus - Productivity Features by Boesch25
icon 15-05-2008 Re: Error code structure by stickleprojects
borderAndreaVB free resources for Visual Basic developersborder
borderAndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2017 Andrea Tincaniborder