0

i am bulk inserting the worksheet to a temp table in SQL, after that i am calling a stored procedure to move data from temp to main table.

for insert i am using the below statement

s = "select * into [" & ThisWorkbook.Sheets("Master Control").Range("F2") & "].[" & Environ("username") & "] FROM [ABC$A13:IU5000] "
cn.Execute s

the problem is that, even after putting the range in the query, it is only picking upto the last column which have data, which is creating problem.

How can i make it to select exactly the range i specified?

Vikky
  • 752
  • 3
  • 15
  • 35

1 Answers1

0

I just posted my code to Transfer an Excel Range INTO a Database

This works for me. 

SELECT * INTO [TagetTable] FROM [Excel 8.0;HDR=YES;DATABASE=C:\stackoverflow\test-stub.xlsx].[ABC$A13:IU5000]

This is your Query string:

select * into [F2].[best buy] FROM [ABC$A13:IU5000]

Is F2 the name of the database your are trying to query? Your missing the filepath in the FROM Clause

Community
  • 1
  • 1
  • my query is running fine, the only problem is that instead of inserting upto column IU, it only insert upto DD. – Vikky Jun 15 '16 at 08:19
  • the number of columns varies user to user, i have decided the max range upto IU, but it didnot work. – Vikky Jun 15 '16 at 08:20
  • I would insert a dummy row of data into Row(14). That may force the query to insert all the rows and columns. You can always delete the dummy record from your database afterwards. –  Jun 15 '16 at 08:36