1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| Sub RequeteClasseurFermeMaj()
Dim Cn As ADODB.Connection
Dim BDD As String
Dim SheetBDD As String, Req_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
BDD = ThisWorkbook.Path & "\BDD TMT.xlsm"
SheetBDD = "Topics"
Set Cn = New ADODB.Connection
'--- Connection ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& BDD & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'-----------------
Dim R As Range, L As Integer, rs As Object, C As Integer, sqlTitre As String, sqlValeur As String, Sql As String
Set R = ThisWorkbook.Sheets("Topics").Range("A1").CurrentRegion
For L = 2 To R.Rows.Count
sqlTitre = "": sqlValeur = ""
Set rs = CreateObject("adodb.recordset")
rs.Open "select [ID] from [Topics$] where [ID]='" & R(L, 1) & "';", Cn
If rs.EOF = False Then Sql = "Update [Topics$] set " Else Sql = "Insert Into [Topics$] "
For C = 1 To R.Columns.Count
If rs.EOF = False Then
If sqlValeur = "" Then sqlValeur = "[" & R(1, C) & "]=" & returValSql(R(L, C)) Else sqlValeur = sqlValeur & ",[" & R(1, C) & "]=" & returValSql(R(L, C))
Else
If sqlTitre = "" Then sqlTitre = "[" & R(1, C) & "]" Else sqlTitre = sqlTitre & "," & "[" & R(1, C) & "]"
If sqlValeur = "" Then sqlValeur = returValSql(R(L, C)) Else sqlValeur = sqlValeur & "," & returValSql(R(L, C))
End If
Next
If rs.EOF = False Then Cn.Execute Sql & sqlValeur & " where [id] =" & returValSql(R(L, 1)) & ";"
Else Cn.Execute Sql & " ( " & sqlTitre & " ) Values ( " & sqlValeur & " );"
rs.Close: Set rs = Nothing
Next
Cn.Close: Set Cn = Nothing
End Sub
Function returValSql(v As Range)
If Trim("" & v.Text) = "" Then returValSql = "Null": Exit Function
If IsDate(v.Text) = True And InStr(v.Text, "/") <> 0 And InStr(v.Text, ":") <> 0 Then returValSql = "#" & Format(v.Text, "yyyy-mm-dd hh:mm:ss") & "#": Exit Function
If IsDate(v) = True And InStr(v.Text, "/") <> 0 And InStr(v.Text, ":") = 0 Then returValSql = "#" & Format(v.Text, "yyyy-mm-dd") & "#": Exit Function
If IsNumeric(Replace(v.Text, ".", ",")) = True And InStr(v.Text, ":") <> 0 Then returValSql = "'" & Format(v.Text, "hh:mm:ss") & "'": Exit Function
returValSql = "'" & Replace(v.Text, "'", "''") & "'"
End Function |
Partager