title: Import csv with quoted newline using QueryTables in Excel
body: <p>I have written a visual basic macro to load a csv file into Excel that I use quite frequently.</p>
  
  <p>Unfortunately, if the csv file contains quoted newlines, the result is different from what you would get if you opened the csv file directly with excel.  Unlike the usual import facility, <code>QueryTables.add()</code> assumes any newline it runs into, whether quoted or not, is the end of the row.</p>
  
  <p>Is there a way around this?  I'd prefer a solution that did not involve pre-modifying the incoming csv files to remove the newlines, but I'm open to suggestions on that front as well.  I do want to have newlines in the resulting excel file cells, though.</p>
  
  <p>The relevant part of my macro:</p>
  
  <pre><code>Sub LoadMyFile()
      ' Query the table of interest
      With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
        &amp; ThisWorkbook.Path &amp; "\" &amp; Range("A1").Value &amp; ".csv", _
        Destination:=Range("$A$2"))
          .Name = ActiveSheet.Name
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .RefreshStyle = xlOverwriteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .TextFilePromptOnRefresh = False
          .TextFilePlatform = 437
          .TextFileStartRow = 1
          .TextFileParseType = xlDelimited
          .TextFileTextQualifier = xlTextQualifierDoubleQuote
          .TextFileConsecutiveDelimiter = False
          .TextFileTabDelimiter = False
          .TextFileSemicolonDelimiter = False
          .TextFileCommaDelimiter = True
          .TextFileSpaceDelimiter = False
          .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
          .TextFileTrailingMinusNumbers = True
          .Refresh BackgroundQuery:=False
      End With
  End Sub
  </code></pre>
  
  <p>Here's an example csv file with quoted newlines</p>
  
  <pre><code>"firstCol","secondCol"
  "name1","data
  one"
  "name 
  2","data two"
  </code></pre>
  
  <p>The macro reads the file name (minus the .csv extension) from cell A1 and assumes the csv file is in the same directory as the excel file containing the macro.</p>
  
  <p>I'm using 32 bit Office Professional 2010 on a windows 7 machine.</p>