+ Reply to Thread
Results 1 to 3 of 3

Import data from closed CSV file using ADO

Hybrid View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Import data from closed CSV file using ADO

    Hello everyone
    I have this code that imports data from CSV file using ADO ..
    'Reference : Microsoft ActiveX Data Objects 6.1 Library
    '------------------------------------------------------
    
    Sub GetCSVDataByADOConnection()
        Dim conn            As ADODB.Connection
        Dim rs              As ADODB.Recordset
        Dim folderPath      As String
        Dim fileName        As String
        Dim strCon          As String
        Dim i               As Long
    
        Set conn = New ADODB.Connection
        Set rs = New ADODB.Recordset
    
        folderPath = ThisWorkbook.Path & "\"
        fileName = "SampleCSV"
    
        conn.Provider = "Microsoft.Jet.OLEDB.4.0"
        conn.ConnectionString = "Data Source=" & folderPath & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
        conn.Open
        strCon = "SELECT * FROM [" & fileName & ".csv]"
        Set rs = conn.Execute(strCon)
    
        If Not rs.EOF Then
            Sheets("Sheet1").Range("A1").CopyFromRecordset rs
            rs.Close
        Else
            MsgBox "Error: No Records Returned.", vbCritical
        End If
    
        If CBool(conn.State And adStateOpen) Then conn.Close
        Set conn = Nothing
        Set rs = Nothing
    End Sub
    The csv file name is "SampleCSV" and this file has no headers .. It contains 6 rows from row 1 to row 6
    When executing this code it works well but it imports five rows only from row 2 to row 6 skipping row 1
    How can I edit the code so as to import row 1 too ... so I need to import the 6 rows (from row 1 to the last row which is row 6)?

    Thanks advanced for help
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Import data from closed CSV file using ADO

    This part of the connection string is telling ADO that the file does have headers, try changing YES to NO.
    HDR=Yes
    If posting code please use code tags, see here.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Import data from closed CSV file using ADO

    Thanks a lot Mr. Norie
    That is great and awesome ..
    You're right HDR stands for Header .. and that worked as expected
    Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] import data from multiple closed files to master file
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2017, 08:59 AM
  2. [SOLVED] VB Code to import from closed excel file
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-18-2015, 06:53 PM
  3. Replies: 0
    Last Post: 03-27-2014, 12:38 PM
  4. Macro to Import Data from Closed Workbook to CSV File
    By groundin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2013, 09:39 PM
  5. File import - select file and import sheet(1) from closed workbook
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2011, 03:15 AM
  6. Import data from different closed workbooks
    By shah0101 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2008, 07:17 AM
  7. How import data from a closed file with an update button
    By efmelp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2007, 10:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1