Update SQL serve DB from EXCEL spread sheet

[nnadimi19]

I am trying to update a SQL serve DB with data that is in a spread sheet. Any help with script or any other method of conversion will be very much appreciated.

Thanks in advance

 

[XL-Dennis]

Hi,

If someone can give me a SQl Server I would be very happy :wink:

Following procedure will hopefully get You started - Kindly disregard the swedish comments:

<PRE>
<FONT color=blue>Sub </FONT>Exportera_Accessdata()

<FONT color=#ff0000>'© 2001 Alla rättigheter XL-Dennis
</FONT>
<FONT color=blue>Dim </FONT>cnt<FONT color=blue> As</FONT><FONT color=blue> New </FONT>ADODB.Connection

<FONT color=blue>Dim </FONT>rst<FONT color=blue> As</FONT><FONT color=blue> New </FONT>ADODB.Recordset

<FONT color=blue>Dim </FONT>stDB<FONT color=blue> As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>rnExportOmrade<FONT color=blue> As</FONT> Range, rnCell<FONT color=blue> As</FONT> Range



stDB = ThisWorkbook.Path & "" & "XLData.mdb"

<FONT color=blue>Set </FONT>rnExportOmrade = ActiveSheet.Range("D12:D16")



<FONT color=#ff0000>'Här skapas databasanslutningen
</FONT>
cnt.<FONT color=blue>Open </FONT>"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & stDB & ";"



<FONT color=#ff0000>'Här öppnas tabellen tblNamn
</FONT>
rst.<FONT color=blue>Open </FONT>"tblNamn", cnt, adOpenKeyset, _

adLockOptimistic, adCmdTableDirect



<FONT color=#ff0000>'Här läses data in i tabellen
</FONT>
<FONT color=blue>For </FONT>Each rnCell In rnExportOmrade

<FONT color=blue>With </FONT>rst

.AddNew

.Fields("Namn") = rnCell.Value

.Update

<FONT color=blue>End With</FONT>

<FONT color=blue>Next </FONT>rnCell



<FONT color=#ff0000>'Tar bort objekten från arbetsminnet och samtidigt kopplas
</FONT>
<FONT color=#ff0000>'anslutningen ned.
</FONT>
<FONT color=blue>Set </FONT>rst =<FONT color=blue> Nothing</FONT>

<FONT color=blue>Set </FONT>cnt =<FONT color=blue> Nothing</FONT>



<FONT color=#ff0000>'Tömmer inmatningsområdet i arbetsbladet
</FONT>
rnExportOmrade.ClearContents

<FONT color=blue>End Sub</FONT>




</PRE>


To connect to a SQL server DB see:
http://www.able-consulting.com/tech.htm

HTH,
Dennis