title: excel vba sql server data import "type mismatch error"
body: <p>I am attempting to create a macro that will pull in some data into Excel 2003 from SQL Server 2005.</p>
  
  <p>The SQL code is in this macro, and part of the code for this comes from values in a cell in Excel. This works, to a degree, but when the data in the Excel cell that i'm using for the SQL code exceeds a line in the formula preview (or approx 170 characters), i get the "Type mismatch" error. Otherwise it works correctly.</p>
  
  <pre><code>With ActiveSheet.QueryTables.Add(Connection:=Array( _
  "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data       Source=anglobisql;Use Procedure for Prepare=1;Auto " _
      , _
      "Translate=True;Packet Size=4096;Workstation ID=MyIDHere;Use Encryption for Data=False;Tag with column collation when possible=F" _
      , "alse;Initial Catalog=DATABASENAME"), Destination:=Range("A1"))
      .CommandType = xlCmdSql
      .CommandText = Array("SELECT COLUMN1, COLUMN2 FROM TABLENAME (nolock) WHERE COLUMN1 IN (" &amp; ActiveWorkbook.Sheets("Sheet1").Range("e607").Value &amp; ")")
      .Name = "DATABASE TABLENAME"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .SourceConnectionFile = _
      "H:\My Data Sources\DATABASE TABLENAME.odc"
      .Refresh BackgroundQuery:=False
  </code></pre>
  
  <p>Obviously all databasename and tablename are filled in correctly. </p>
  
  <p>Any idea why this is falling down (whether there's a character limit, or whether it fails because the cell value continues onto the next line in the formula preview bar in Excel? How could this be fixed?</p>
  
  <p>Much appreciated,</p>
  
  <p>Alex</p>