Update SQL serve DB from EXCEL spread sheet

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 

Forum statistics

Threads
1,143,614
Messages
5,719,719
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top