title: ADO Connection and Recordset = ""
body: <p>Do CSV (comma delimited text files) have a sheet name?
  Do I need to specify a range for data to copy?</p>
  
  <p>My SQL skills are weak. </p>
  
  <p>I am trying to get all the text from my export.csv file into a worksheet where the "Price" column is not empty and sort it by "sku".</p>
  
  <p>I can open the file and build the SQL string but there must be an error?</p>
  
  <p>The debugger shows the string as: </p>
  
  <pre><code>"SELECT * FROM [export$] WHERE price IS NOT NULL ORDER BY sku;"
  </code></pre>
  
  <p>When I try and open the Connection and Recordset the debugger shoes they both contain the value of "".</p>
  
  <pre><code>Set rsCon = CreateObject("ADODB.Connection")
  Set rsData = CreateObject("ADODB.Recordset")
  </code></pre>
  
  <p>When I get to using them I go straight to my error trap.</p>
  
  <pre><code>rsCon.Open szConnect
  rsData.Open szSQL, rsCon, 0, 1, 1
  </code></pre>
  
  <p>I am calling the subroutine with the following data:</p>
  
  <p>GetData "export.csv", "export", "A:AH", "BirdFeet", "B1", "sku", True, True</p>
  
  <p>Here is the complete subroutine.</p>
  
  <pre><code>    Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, _
                                                TargetSheet As String, TargetRange As String, _
                                                TargetSortColumn As String, _
                                                HaveHeader As Boolean, UseHeaderRow As Boolean)
  
       Dim lColumn As Long
          Dim lCount As Long
          Dim lRow As Long
  
          Dim rsCon As Object
          Dim rsData As Object
  
          Dim szConnect As String
          Dim szSQL As String
  
          lRow = Range(TargetRange).Row
          lColumn = Range(TargetRange).Column
  
          ' Create the connection string.
          If HaveHeader = False Then                                                          'No there is NOT a header row.
              If Val(Application.Version) &lt; 12 Then
                  szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
                              "Data Source=" &amp; SourceFile &amp; ";" &amp; _
                              "Extended Properties=""Excel 8.0;HDR=No"";"
              Else
                  szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
                              "Data Source=" &amp; SourceFile &amp; ";" &amp; _
                              "Extended Properties=""Excel 12.0;HDR=No"";"
              End If
          Else                                                                                'Yes there is a Header Row
              If Val(Application.Version) &lt; 12 Then
                  szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
                              "Data Source=" &amp; SourceFile &amp; ";" &amp; _
                              "Extended Properties=""Excel 8.0;HDR=Yes"";"
              Else
                      If (Right(SourceFile, 4) = ".csv") Then
                          szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
                                      "Data Source=" &amp; SourceFile &amp; ";" &amp; _
                                      "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""
                      Else
                          szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
                                      "Data Source=" &amp; SourceFile &amp; ";" &amp; _
                                      "Extended Properties=""Excel 12.0;HDR=Yes"";"
                      End If
              End If
          End If
  
          If SourceSheet = "" Then                                                            'Create query strings
  
              szSQL = "SELECT * FROM " &amp; SourceRange$ &amp; " ORDER BY sku;"
  
          ElseIf SourceSheet = "DiamondAvian" Or SourceSheet = "export" Then
              szSQL = "SELECT * FROM [" &amp; SourceSheet$ &amp; "$" &amp; "] WHERE price IS NOT NULL ORDER BY " &amp; TargetSortColumn &amp; ";"   'Drops all rows with no Price
  
          Else
  
              szSQL = "SELECT * FROM [" &amp; SourceSheet$ &amp; "$" &amp; SourceRange$ &amp; "] WHERE sku IS NOT NULL ORDER BY " &amp; TargetSortColumn &amp; ";"   'THIS WORKS FOR DICIONARY
  
          End If
  
          On Error GoTo SomethingWrong
  
          Set rsCon = CreateObject("ADODB.Connection")
          Set rsData = CreateObject("ADODB.Recordset")
  
          rsCon.Open szConnect
          rsData.Open szSQL, rsCon, 0, 1, 1
                                                                                               ' Check to make sure we received data and copy the data
          If Not rsData.EOF Then
  
              If HaveHeader = False Then
                  Cells(1, 1).CopyFromRecordset rsData
              Else
                                                                                               'Add the header cell in each column if the last argument is True
                  If UseHeaderRow Then
                      For lCount = 0 To rsData.Fields.Count - 1                                'Builds the Header row one column at a time.
                          Cells(lRow, lColumn + lCount).value = rsData.Fields(lCount).Name     'lcount determines the Column to paste header info in.
                      Next lCount
                  Cells(lRow + 1, lColumn).CopyFromRecordset rsData                            'This is the step that copies and Pastes the data.
                  Else
                      Cells(lRow + 1, lColumn).CopyFromRecordset rsData
                  End If
              End If
  
          Else
              MsgBox "No records returned from : " &amp; SourceFile, vbCritical
          End If
  
          rsData.Close                                                                        ' Clean up our Recordset object.
          Set rsData = Nothing
          rsCon.Close
          Set rsCon = Nothing
  
          Exit Sub
  
  SomethingWrong:
  
      MsgBox "The file name, Sheet name or Range is invalid of : " &amp; SourceFile, vbExclamation, "Error"
  
  On Error GoTo 0
  
  End Sub
  </code></pre>
  
  <p>Sample data from export.csv</p>
  
  <pre><code>sku    price    post_date        post_date_gmt
  B1     - M      4/19/2015 11:15   4/19/2015 15:15
  B8     8.06     4/19/2015 11:11   4/19/2015 15:11
  B1     10.79    4/19/2015 11:08   4/19/2015 15:08
  B2     11.65    4/19/2015 11:08   4/19/2015 15:08
  B3     11.98    4/19/2015 11:08   4/19/2015 15:08
  B3B    12.74    4/19/2015 11:08   4/19/2015 15:08
  B4     16.24    4/19/2015 11:08   4/19/2015 15:08
  SB     770      4/3/2015 12:37    4/3/2015 16:37
  </code></pre>