Viggy,
Below You find two sample-procedures. The $ in sheetname indicate that the sheet exist. The 8.0 has nothing to do with which version of XL You are running. "HDR=Yes" tells Excel that the first row include fieldnames.
Don't forget to set a reference to the Microsoft ActiveX Data Object (AD) Library 2.5 or above.
Code:
Option Explicit
Sub Lägg_Till_Post_Arbetsblad()
Dim cnt As ADODB.Connection
Dim cmd As ADODB.Command
Dim stCon As String, stSQL As String
Set cnt = New ADODB.Connection
Set cmd = New ADODB.Command
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & vbCrLf _
& "Data Source=E:\Arbetsmaterial\Datakalla.xls;" & vbCrLf _
& "Extended Properties=""Excel 8.0;HDR=YES"";"
stSQL = "INSERT INTO [Data$] (Avdelning,Resultat,Budget) VALUES ('QQ', '10', '8')"
cnt.ConnectionString = stCon
cnt.Open
cmd.ActiveConnection = cnt
cmd.CommandText = stSQL
cmd.Execute
Set cmd = Nothing
cnt.Close
Set cnt = Nothing
End Sub
Sub Uppdatera_Post_Arbetsblad()
Dim cnt As ADODB.Connection
Dim cmd As ADODB.Command
Dim stCon As String, stSQL As String
Set cnt = New ADODB.Connection
Set cmd = New ADODB.Command
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & vbCrLf _
& "Data Source=E:\Arbetsmaterial\Datakalla.xls;" & vbCrLf _
& "Extended Properties=""Excel 8.0;HDR=YES"";"
stSQL = "UPDATE [Data$] SET Resultat=20 WHERE Avdelning = 'BB'"
cnt.ConnectionString = stCon
cnt.Open
cmd.ActiveConnection = cnt
cmd.CommandText = stSQL
cmd.Execute
Set cmd = Nothing
cnt.Close
Set cnt = Nothing
End Sub
Some of the names and so on are in swedish but I believe You're capable of understand it.
Kind regards,
Dennis
dBforums
>
SQL in VBA/Excel (other than importing)
01-20-04, 15:40
Linear Mode
