title: SQL Server 2005 OPENROWSET insert into Excel - text to number formatting
body: <p>So it's not a huge deal, but with Google not returning anything more recent (mostly 2007 or so) about it possibly being an Excel driver issue...I came here to ask the question as what I found as an "answer" was not acceptable without further research.</p>
  
  <p>My problem:</p>
  
  <p>Writing a dynamic OPENROWSET query clear and insert new data to send off a report to specific users - the base excel report contains some formulas in which it calculates off and sums based off the inserted data and the multiplication formulas seem to work fine, but unless the totals are formatted properly in Excel as "numbers" instead of text the sum will not function...</p>
  
  <p>Solution must be free-form based (if there is a solution at all), I don't want to transfer all my code to SSIS...</p>
  
  <p>Update/Insert code:</p>
  
  <pre><code>    SELECT @sqlu = 'UPDATE OPENROWSET(' +
      '''Microsoft.ACE.OLEDB.12.0'', ' +
      '''Excel 12.0;Database=' + @File + ';HDR=YES'', ' +
      '''SELECT * FROM [Base$B1:G601]'')
      SET 
      col1 = '''', col2 = '''', col3 = '''', col4 = '''', col5 = '''', col6 = '''' '
  
  
      EXEC (@sqlu)
  
  
      SELECT @sqli = 'INSERT INTO OPENROWSET(' +
      '''Microsoft.ACE.OLEDB.12.0'', ' +
      '''Excel 12.0;Database=' + @File + ';HDR=NO'', ' +
      '''SELECT * FROM [Base$B1:G2]'')
  
      SELECT col1, col2, col3, col4, col5, col6
      FROM [DATABASE].[dbo].tbl_CIM
      WHERE col1 IN(''' + @area + ''')'
  
  
      EXEC (@sqli)
  </code></pre>
  
  <p>The second problem and it may not be a problem for now but later - after the insert, it seems to create borders around the results. Is there also a potential way to remove those during the result transfer?</p>
  
  <p>Any help or sources to research if it is a driver issue is appreciated as I could find none.</p>