0

I need to be able to insert a row in an Excel file at a specific row number. So far, I have the query:

insert into OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Test.xls;','SELECT * FROM [Feuil1$]')
SELECT * FROM OPENQUERY(ExcelServer2, 'SELECT * FROM [Feuil1$]') WHERE [BIP] = '967046'

This query insert a row at the end of the Excel spreadsheet. Now, I need to be able to insert a row anywhere I want in the Excel. This file is a master Excel file, used by several users. I can't import the whole file in a table, insert the row where I need and export the table back to the Excel.

So, is there a way I can do this?

thanks a lot

Dominic
  • 159
  • 1
  • 3
  • 13
  • Why are you using an excel file as sql table replacement? The problem is with the process, not how to insert data. Remember that in relational theory tables have no inherent order so there is no concept of inserting into a specific "location". – Sean Lange May 12 '15 at 15:47
  • client needs to use its existing Excel file. We need to update/insert data in this Excel from a PocketPC. It works fine throught linked server, but I need to be able to insert rows in the Excel at the location I want, not only from the bottom of the file. – Dominic May 12 '15 at 19:31
  • You have to realize that when you connect to Excel with OPENQUERY it is just a data source. As such there is no concept of order in the table. In order to do what you want you will need to use VBA to add a new row where you need it and then populate that row with values. If it were my project I would work long and hard at finding a way to stop using Excel as the datasource for any application. – Sean Lange May 12 '15 at 19:43
  • yeah, I tried to convince them to ditch Excel and use MSSQL, but they have multiple reports and cross-spreadsheets queries and they wanted to keep it that way. Thanks for the answer though. It clarify things up for me. – Dominic May 12 '15 at 20:16
  • Holy Cowabunga!!! cross spreadsheet queries? Wow that data must be a real "joy" to work with. Best of luck, that sounds like a nightmare project to say the least. – Sean Lange May 12 '15 at 21:14
  • that would actually be an easy project, if it wasn't of the purpose of my question. Now, it look like a showstopper for us! Thanks for your help! :-) – Dominic May 13 '15 at 12:30

0 Answers0