Code VBA pour Excel lent, aide svp

Question

la procédure UpdateSQL est sérieusement trop lente. Pouvez-vous m'aider a régler ce probleme? merci. Public Sub UpdateSQL() Dim Page As String Dim Cellule As String Dim Selection As String Dim Exclusion As String Dim Annee As String Annee = Sheets("Paramêtres").Range("B3") Dim x As Integer With Sheets("Paramêtres") For x = 10 To 108 Page = .Range("B" & x) Cellule = .Range("C" & x) Selection = .Range("D" & x) Exclusion = .Range("E" & x) Call Afficher(Page, Cellule, SelectionSQL(Selection, Exclusion), Annee) Next x End With End Sub Private Sub Afficher(Page As String, Cellule As String, SQL As String, Annee As String) On Error Resume Next Dim rs As ADODB.Recordset Dim sConnect As String sConnect = "driver={SQL Server};server=911.911.9.9;uid=user;pwd=password;database=pasdetesaffaire" Sheets(Page).Select Set rs = New ADODB.Recordset SQL = SQL & Annee & "'" rs.Open SQL, sConnect, asOpenForwardOnly, adLockReadOnly Range(Cellule).Select 'Call InputBox("e", "e", SQL) 'MsgBox (CDec(rs.Fields(0).Value)) If rs.Fields(0).Value <> 0 Then Range(Cellule) = CDec(rs.Fields(0).Value) Else Range(Cellule) = 0 End If If CBool(rs.State And adStateOpen) Then rs.Close Set rs = Nothing End Sub Private Function SelectionSQL(Selection As String, Exclusion As String) As String Dim SQL As String Dim Caractere As String Dim chaine As String Dim Increment As Integer Increment = 1 SQL = "SELECT SUM(SLDE) FROM dbo.FIN_GLG WHERE (NO_CMPT like '" 'Calcul de la selection Do While Increment <> Len(Selection) + 1 Caractere = Mid(Selection, Increment, 1) If Caractere = "*" Then chaine = chaine & "_" ElseIf Caractere = "/" Then SQL = SQL & chaine & "' OR NO_CMPT like " chaine = "'" Else chaine = chaine & Caractere End If Increment = Increment + 1 Loop SQL = SQL & chaine & "')" 'Calcul de l'exclusion If Exclusion <> "" Then chaine = "" Caractere = "" Increment = 1 SQL = SQL & " AND NOT NO_CMPT like '" Do While Increment <> Len(Exclusion) + 1 Caractere = Mid(Exclusion, Increment, 1) If Caractere = "*" Then chaine = chaine & "_" ElseIf Caractere = "/" Then SQL = SQL & chaine & "' AND NOT NO_CMPT like " chaine = "'" Else chaine = chaine & Caractere End If Increment = Increment + 1 Loop SQL = SQL & chaine & "'" End If SQL SQL & " AND EXER_FIN '" SelectionSQL = SQL End Function

cs_jperre · Answer

Voir ma réponse sur l'utilisation de la méthode CopyFromRecordset dans Excel.

Code VBA pour Excel lent, aide svp

1 réponse

Discussions similaires