Hi,
apologies if this has been asked before.

I'm trying to insert a range of data in Excel to a table in SQL Server. I have some VBA code that loops through each row and executes an Insert statement to the table, and this works. However, I'm trying to find a more flexible solution.

I have a Stored Proc in SQL Server that takes a Table Valued Parameter and inserts all of the records in the parameter to the table. 


declare @spt test_excel_process_type


insert into @spt
values('Colin Pates',5,13.6,getdate(),'1963-05-05','Y')
insert into @spt
values('Doug Rougvie',3,11.6,getdate(),'1965-09-05','Y')


exec proc_test_excel_process @spt
I'd like to pass the range e.g. A2:F4 to the Stored Proc in VBA as a parameter but can't seem to find a way to do it. I'd be grateful for any ideas/code snippets folks might have.

As an alternative I can package the range into an XML string and pass that to the Stored Proc but I'd rather leave it as it is with the TVP.

Thanks,
Sean