strSQL = "" strSQL = strSQL & "select cote.indice indiceZ, " strSQL = strSQL & " cote.cote_actuelle coteZ" strSQL = strSQL & " from t_detail_vin pr, " strSQL = strSQL & " type_vin vin, " strSQL = strSQL & " (select ind.id_tvin, " strSQL = strSQL & " ind.milesime millesime, " strSQL = strSQL & " c.cote cote_actuelle, " strSQL = strSQL & " ind.id_indice indice " strSQL = strSQL & " from t_indices ind, " strSQL = strSQL & " cote_annuelle c " strSQL strSQL & " where ind.id_indice c.id_indice" strSQL = strSQL & " and ind.format in('Bouteille') " strSQL = strSQL & " and c.annee='2010' " strSQL = strSQL & " and ind.id_indice not in ('1','2','3') " strSQL = strSQL & " and ind.id_indice < '100') cote " strSQL strSQL & " where vin.id_tvin pr.id_tvin " strSQL strSQL & " and pr.milesime cote.millesime " strSQL = strSQL & " and vin.id_tvin=cote.id_tvin " strSQL = strSQL & " and vin.proprietaire not in ('Indifferent') " strSQL = strSQL & " and vin.proprietaire is not null " strSQL = strSQL & " order by cote.indice" rs.Open strSQL, cN, adOpenForwardOnly, adLockOptimistic If rs.RecordCount > 0 Then j = 1 i = 0 Do While Not rs.EOF And Not rs.BOF i = i + 1 Feuil1.Cells(i, j) = rs("coteZ") Feuil1.Cells(i, j + 1) = rs("coteZ") rs.MoveNext
strSQL = "" strSQL = strSQL & "select cote.indice, " strSQL = strSQL & " cote.cote_actuelle " strSQL = strSQL & " from t_detail_vin pr, " strSQL = strSQL & " type_vin vin, " strSQL = strSQL & " (select ind.id_tvin, " strSQL = strSQL & " ind.milesime millesime, " strSQL = strSQL & " c.cote cote_actuelle, " strSQL = strSQL & " ind.id_indice indice " strSQL = strSQL & " from t_indices ind, " strSQL = strSQL & " cote_annuelle c " strSQL strSQL & " where ind.id_indice c.id_indice" strSQL = strSQL & " and ind.format in('Bouteille') " strSQL = strSQL & " and c.annee='2010' " strSQL = strSQL & " and ind.id_indice not in ('1','2','3') " strSQL = strSQL & " and ind.id_indice < '100') cote" strSQL strSQL & " where vin.id_tvin pr.id_tvin " strSQL strSQL & " and pr.milesime cote.millesime " strSQL = strSQL & " and vin.id_tvin=cote.id_tvin " strSQL = strSQL & " and vin.proprietaire not in ('Indifferent') " strSQL = strSQL & " and vin.proprietaire is not null " strSQL = strSQL & " order by cote.indice"
MsgBox Err.Number & vbCr & Err.Description
Option Explicit Public cN As ADODB.Connection Public rs As ADODB.Recordset Public Function conNect() As Boolean On Error GoTo conNect_Err Set cN = New ADODB.Connection cN.ConnectionString = "Provider=msdaora;Data Source=OIIUI;User Id=xxxxxxx;Password=xxxxxxxxxx;" cN.Open conNect = True Exit Function conNect_Err: MsgBox Err.Number & vbCr & Err.Description conNect = True End Function Public Function DeconNect() As Boolean On Error Resume Next cN.Close Set cN = Nothing End Function Private Sub Workbook_Open() Dim strSQL As String Dim i As Integer Dim j As Integer On Error GoTo Workbook_Open_Err If conNect() = True Then Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient strSQL = "" strSQL = strSQL & "select cote.indice, " strSQL = strSQL & " cote.cote_actuelle " strSQL = strSQL & " from t_detail_vin pr, " strSQL = strSQL & " type_vin vin, " strSQL = strSQL & " (select ind.id_tvin, " strSQL = strSQL & " ind.milesime millesime, " strSQL = strSQL & " c.cote cote_actuelle, " strSQL = strSQL & " ind.id_indice indice " strSQL = strSQL & " from t_indices ind, " strSQL = strSQL & " cote_annuelle c " strSQL strSQL & " where ind.id_indice c.id_indice" strSQL = strSQL & " and ind.format in('Bouteille') " strSQL = strSQL & " and c.annee='2010' " strSQL = strSQL & " and ind.id_indice not in ('1','2','3') " strSQL = strSQL & " and ind.id_indice < '100') cote " strSQL strSQL & " where vin.id_tvin pr.id_tvin " strSQL strSQL & " and pr.milesime cote.millesime " strSQL = strSQL & " and vin.id_tvin=cote.id_tvin " strSQL = strSQL & " and vin.proprietaire not in ('Indifferent') " strSQL = strSQL & " and vin.proprietaire is not null " strSQL = strSQL & " order by cote.indice" rs.Open strSQL, cN, adOpenForwardOnly, adLockOptimistic If rs.RecordCount > 0 Then j = 1 i = 0 Do While Not rs.EOF And Not rs.BOF i = i + 1 Feuil1.Cells(i, j) = rs("cote.indice") Feuil1.Cells(i, j + 1) = rs("cote.cote_actuelle") rs.MoveNext Loop End If DeconNect Else 'blablabla End If Exit Sub Workbook_Open_Err: MsgBox Err.Number & vbCr & Err.Description End Sub
strSQL = strSQL & " from t_detail_vin pr, " strSQL = strSQL & " type_vin vin, " strSQL = strSQL & " (select ind.id_tvin, " strSQL = strSQL & " ind.milesime millesime, " strSQL = strSQL & " c.cote cote_actuelle, " strSQL = strSQL & " ind.id_indice indice " strSQL = strSQL & " from t_indices ind, " strSQL = strSQL & " cote_annuelle c " strSQL strSQL & " where ind.id_indice c.id_indice" strSQL = strSQL & " and ind.format in('Bouteille') " strSQL = strSQL & " and c.annee='2010' " strSQL = strSQL & " and ind.id_indice not in ('1','2','3') " strSQL = strSQL & " and ind.id_indice < '100') cote
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionFeuil1.Cells(i, j) = rs("indice") Feuil1.Cells(i, j + 1) = rs("cote_actuelle")
ind.id_indice < '100''100' est inférieur à '90' parce que le 1 est avant le 9 dans la table ASCII.