S
sal21
.... i have this sheet RATE with a event change code.
Is possible to intercept the index in AC when i fill a cell in column M
and put the related value into rs of mdb...?
here the macro, natuyrally is arranged from old code and naturally not
work!!!!:
sorry for diemension of file here:
http://www7.rapidupload.com/d.php?file=dl&filepath=4874
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Intersect(Range("M3:M65536"), Target) Is Nothing Then Exit Sub
For Each oCell In Intersect(Range("M3:M65536"), Target)
If oCell = "" Then
oCell.Offset(0, -1) = ""
Cells(oCell.Row, 27) = ""
Cells(oCell.Row, 35) = ""
Else
oCell.Offset(0, -1) = Format(Now, "dd/mm/yyyy")
Cells(oCell.Row, 27) = [A1]
Cells(oCell.Row, 35) = "M"
Call INPS_TOTALE_MASTER
End If
Next oCell
End Sub
Global Const gPROVADatabasePath2 = "Data
Source=\\GCD01F4500\DATI\PUBBLICA\INPS\STORICO_INPS.MDB;"
Sub INPS_TOTALE_MASTER()
Sheets("RATE").Select
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, N As
Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
Set rs = New ADODB.Recordset
rs.Open "INPS_02", cn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rs.INDEX = "PROVA29"
r = 3
Do While Len(Range("A" & r).FORMULA) > 0
'If Not Trim(Range("L" & r) & Range("M" & r) & Range("AA" & r) &
Range("AI" & r)) = "" Then
With rs
If Not rs.BOF Then
rs.MoveFirst
End If
rs.Seek Array(Range("AC" & r)), adSeekFirstEQ
If rs.EOF = True Then
..AddNew
..Fields("PROVA12") = Range("L" & r).Value
..Fields("PROVA13") = Range("M" & r).Value
..Fields("PROVA27") = Range("AA" & r).Value
..Fields("PROVA31") = Range("AI" & r).Value
..Update
End If
End With
'End If
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
updating the refred record (match index in Excel range AC and match rs
PROVA29) in MDB...
..Fields("PROVA12") = Range("L" & r).Value
..Fields("PROVA13") = Range("M" & r).Value
..Fields("PROVA27") = Range("AA" & r).Value
..Fields("PROVA31") = Range("AI" & r).Value
... in effect the code into change event of sheet RATE write value into
column L,M,AA, and AI if i select from a list into column M, after this
operation update the related recordset into mdb...
Is possible to intercept the index in AC when i fill a cell in column M
and put the related value into rs of mdb...?
here the macro, natuyrally is arranged from old code and naturally not
work!!!!:
sorry for diemension of file here:
http://www7.rapidupload.com/d.php?file=dl&filepath=4874
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Intersect(Range("M3:M65536"), Target) Is Nothing Then Exit Sub
For Each oCell In Intersect(Range("M3:M65536"), Target)
If oCell = "" Then
oCell.Offset(0, -1) = ""
Cells(oCell.Row, 27) = ""
Cells(oCell.Row, 35) = ""
Else
oCell.Offset(0, -1) = Format(Now, "dd/mm/yyyy")
Cells(oCell.Row, 27) = [A1]
Cells(oCell.Row, 35) = "M"
Call INPS_TOTALE_MASTER
End If
Next oCell
End Sub
Global Const gPROVADatabasePath2 = "Data
Source=\\GCD01F4500\DATI\PUBBLICA\INPS\STORICO_INPS.MDB;"
Sub INPS_TOTALE_MASTER()
Sheets("RATE").Select
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, N As
Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
Set rs = New ADODB.Recordset
rs.Open "INPS_02", cn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rs.INDEX = "PROVA29"
r = 3
Do While Len(Range("A" & r).FORMULA) > 0
'If Not Trim(Range("L" & r) & Range("M" & r) & Range("AA" & r) &
Range("AI" & r)) = "" Then
With rs
If Not rs.BOF Then
rs.MoveFirst
End If
rs.Seek Array(Range("AC" & r)), adSeekFirstEQ
If rs.EOF = True Then
..AddNew
..Fields("PROVA12") = Range("L" & r).Value
..Fields("PROVA13") = Range("M" & r).Value
..Fields("PROVA27") = Range("AA" & r).Value
..Fields("PROVA31") = Range("AI" & r).Value
..Update
End If
End With
'End If
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
updating the refred record (match index in Excel range AC and match rs
PROVA29) in MDB...
..Fields("PROVA12") = Range("L" & r).Value
..Fields("PROVA13") = Range("M" & r).Value
..Fields("PROVA27") = Range("AA" & r).Value
..Fields("PROVA31") = Range("AI" & r).Value
... in effect the code into change event of sheet RATE write value into
column L,M,AA, and AI if i select from a list into column M, after this
operation update the related recordset into mdb...