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
| Sub GetDataFromADO()
Cells.Select
Range("b2:Z65536").Select
Selection.ClearContents
'Declare variables'
Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyRecordset As ADODB.Recordset
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
'Open Connection'
' objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=
'...
'...
' objMyCmd.CommandType = adCmdText'
'Open Recordset'
' Set objMyRecordset.Source = objMyCmd'
' objMyRecordset.Open
Range("B2").Select
ActiveSheet.Range("B2").CopyFromRecordset objMyRecordset
' ************** Le code qui suit ne s'exécute pas **************
DoEvents
While IsEmpty(Cells(2, 2).Value) Or attend > 10000
Application.Wait Now + TimeValue("0:00:01")
' on met une limite
attend = attend + 1
Wend
Range("D65536").Select
Range(Selection, Selection.End(xlUp)).Select
nbl = ActiveCell.Row
' Range("L2").Select
Cells(2, 12).FormulaR1C1 = _
"=(MID(RC[-1],14,3))*1&RIGHT(RC[-1],1)&""-""&(MID(RC[-7],14,3))*1&""-""&(CODE(RIGHT(RC[-7],1))-64)"
Range(Cells(2, 12), Cells(nbl, 12)).Select
Selection.FillDown
End Sub |
Partager