06. november 2010 - 21:07
Der er
4 kommentarer og
1 løsning
Opdater MS-SQL tabel fra Excel
Har søgt og søgt uden at finde noget der fungere for følgende problem:
Opdater (INSERT INTO) en tabel i MS-SQL med værdier fra et Excel ark. Og det hele skal udføre fra Excel.
Har med succes fået automatiseret excel til at læse en tabel og beregne data, men kan ikke helt få styr på hvorledes jeg opdateret en tabel (NelsonSiegel) på serveren.
Har kigget forskellige steder:
Forbindelses string:
http://www.connectionstrings.com/sql-server-2008#p3http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServerViaSQLXMLOLEDBSkriv (ADO)
www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22034603.html+Write+to+SQL+Server+Table+from+Excel&cd=9&hl=da&ct=clnk&gl=dk" target="_blank">http://webcache.googleusercontent.com/search?q=cache:tioDfw4TMgEJ:
www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22034603.html+Write+to+SQL+Server+Table+from+Excel&cd=9&hl=da&ct=clnk&gl=dkhttp://support.microsoft.com/kb/321686#appliestohttp://social.msdn.microsoft.com/Forums/en/vbinterop/thread/572226df-e59b-4c80-9978-494892699b19 Kan man bruge "windows autification" eller ligende ved login?
Selve information der skal importeres ser således ud, og er orgaiseret ligeså i DB.
Fitting parametre: Værdi Valuta Time
Phi0 3,574043398 Dkk 2010-11-06 20:52
Phi1 -2,728787369 Dkk 2010-11-06 20:52
Phi2 -0,765405829 Dkk 2010-11-06 20:52
Phi3 -6,604198618 Dkk 2010-11-06 20:52
Phi4 0,943258676 Dkk 2010-11-06 20:52
Object funktion 0,003570572 Dkk 2010-11-06 20:52
Phi0 3,546411218 Eur 2010-11-06 20:52
Phi1 -2,862945513 Eur 2010-11-06 20:52
Phi2 -1,020329776 Eur 2010-11-06 20:52
Phi3 -7,613463949 Eur 2010-11-06 20:52
Phi4 1,019912799 Eur 2010-11-06 20:52
Object funktion 0,006036181 Eur 2010-11-06 20:52
Håber der er nogen der kan give mig den rette vejledning til at komme videre. Og prøver at arbejde videre med "10.0 Write to SQL Server Table from Excel
06. november 2010 - 22:25
#1
Det lykkedes at få noget til at virke. Men er nu ikke helt tilfreds med koden.
Dim MyDatabase As ADODB.Connection
Dim MyCommand As New ADODB.Command
Dim MyRecordset As New ADODB.Recordset
Dim Column As Long
Sheets("Coefficients").Select
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Thorvall;Data Source=Testing;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A2").Value & "','" & Range("C2").Value & "','" & Range("D2").Value & "','" & Range("B2").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A3").Value & "','" & Range("C3").Value & "','" & Range("D3").Value & "','" & Range("B3").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A4").Value & "','" & Range("C4").Value & "','" & Range("D4").Value & "','" & Range("B4").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A5").Value & "','" & Range("C5").Value & "','" & Range("D5").Value & "','" & Range("B5").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A6").Value & "','" & Range("C6").Value & "','" & Range("D6").Value & "','" & Range("B6").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A7").Value & "','" & Range("C7").Value & "','" & Range("D7").Value & "','" & Range("B7").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A8").Value & "','" & Range("C8").Value & "','" & Range("D8").Value & "','" & Range("B8").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A9").Value & "','" & Range("C9").Value & "','" & Range("D9").Value & "','" & Range("B9").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A10").Value & "','" & Range("C10").Value & "','" & Range("D10").Value & "','" & Range("B10").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A11").Value & "','" & Range("C11").Value & "','" & Range("D11").Value & "','" & Range("B11").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A12").Value & "','" & Range("C12").Value & "','" & Range("D12").Value & "','" & Range("B12").Value & "')"
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & Range("A13").Value & "','" & Range("C13").Value & "','" & Range("D13").Value & "','" & Range("B13").Value & "')"
' Close database
MyDatabase.Close
End Sub
07. november 2010 - 10:32
#2
kan du bedre lide noget alla dette her...
Dim MyDatabase As ADODB.Connection
Dim MyCommand As New ADODB.Command
Dim MyRecordset As New ADODB.Recordset
Dim lRow As Long
Sheets("Coefficients").Select
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" & _
"Initial Catalog=Thorvall;Data Source=Testing;Use Procedure for Prepare=1;Auto Translate=True;" & _
"Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"
For lRow = 2 To 13
MyDatabase.Execute "INSERT INTO NelsonSiegel (Parameter, Currency, Time, Value) VALUES ('" & _
Cells(lRow, 1).Value & "','" & Cells(lRow, 3).Value & "','" & _
Cells(lRow, 4).Value & "','" & Cells(lRow, 2).Value & "')"
Next lRow
' Close database
MyDatabase.Close
End Sub
