Importing Excel data with macros to SQL server

[ramszone]

Hi All,

I need to import an excel sheet data to sql server.
I have imported each sheet in excel sheet into a table in sql server and I
have made a proper relation between tables.
I found a function written in vb, that updates the cells when I click on a
button on the sheet.
I need to convert this code into a stored procedure or function in sql
server.
Can anyone please help me how to achieve this?

Thanks in advance,
Rams.

 

[Patrick Molloy]

to me there are two issues.

Are you asking what the code is to oush the data to SQl and also how to
convert that code to an sp?

the sql would be quite simplistic

INSERT INTO {tablename} ({field1},{field2} ..) VALUES ( value1. value2...)

read sql help on savign this as an sp.

now in the excel code editor, set a reference the Microsoft active data
objects 2.7 Library

create a new connetcion, then with a command object, execute the sql


Dim con As New ADODB.Connection
sSQL="INSERT INTO {tablename} ({field1},{field2} ..) VALUES ( value1.
value2...)"


With con
.ConnectionString ="{your connection string}"
.Open
End With
With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = sSQL
.Execute
Debug.Print .Properties.Count
End With
con.Close


oce you have the SP, then yuor sql would haev to be changed to somethign
along these lines
sp_myInsert a,b,c