irisbu
06-21-2010, 01:47 PM
Hi everybody,
I have an excel worksheet with ActiveWorkbook.CustomDocumentProperties. When I close the workbook, I want to update an access table with all the ActiveWorkbook.CustomDocumentProperties.
This is the code I wrote - but it does not update the database.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
On Error GoTo UserForm_Initialize_Err
Dim dbDatabase As ADODB.Connection
Dim rs As ADODB.Recordset
Dim a As Integer
Dim b As Integer
If ActiveWorkbook.Path = "" Then
Exit Sub
Else
dbDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\masterfood.mdb"
rs.Open "categories;", dbDatabase, adOpenStatic
rs.FindFirst "mispar=" & "'" & ActiveWorkbook.CustomDocumentProperties("mispar") & "'"
If rs.NoMatch Then
rs.AddNew
rs!irgun = ActiveWorkbook.CustomDocumentProperties("irgun")
rs!l_name = ActiveWorkbook.CustomDocumentProperties("l_name")
rs!f_name = ActiveWorkbook.CustomDocumentProperties("f_name")
rs!tafkid = ActiveWorkbook.CustomDocumentProperties("tafkid")
rs!Subject = ActiveWorkbook.CustomDocumentProperties("Subject")
rs!delivery_date = ActiveWorkbook.CustomDocumentProperties("delivery_date")
rs!sender_f_name = ActiveWorkbook.CustomDocumentProperties("sender_f_name")
rs!sender_l_name = ActiveWorkbook.CustomDocumentProperties("sender_l_name")
rs!sender_tafkid = ActiveWorkbook.CustomDocumentProperties("sender_tafkid")
rs!remarks = ActiveWorkbook.CustomDocumentProperties("remarks")
rs!mispar = ActiveWorkbook.CustomDocumentProperties("mispar")
rs!Path = ActiveWorkbook.Path
rs!software = ActiveWorkbook.CustomDocumentProperties("software")
rs.Update
Else
rs.Edit
rs!irgun = ActiveWorkbook.CustomDocumentProperties("irgun")
rs!l_name = ActiveWorkbook.CustomDocumentProperties("l_name")
rs!f_name = ActiveWorkbook.CustomDocumentProperties("f_name")
rs!tafkid = ActiveWorkbook.CustomDocumentProperties("tafkid")
rs!Subject = ActiveWorkbook.CustomDocumentProperties("Subject")
rs!delivery_date = ActiveWorkbook.CustomDocumentProperties("delivery_date")
rs!sender_f_name = ActiveWorkbook.CustomDocumentProperties("sender_f_name")
rs!sender_l_name = ActiveWorkbook.CustomDocumentProperties("sender_l_name")
rs!sender_tafkid = ActiveWorkbook.CustomDocumentProperties("sender_tafkid")
rs!remarks = ActiveWorkbook.CustomDocumentProperties("remarks")
rs!mispar = ActiveWorkbook.CustomDocumentProperties("mispar")
rs!Path = ActiveWorkbook.Path
rs!software = ActiveWorkbook.CustomDocumentProperties("software")
rs.Update
End If
End If
rs.Close
db1.Close
Set rs = Nothing
Set db1 = Nothing
Unload UserForm1
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
can someone please help me?
I have an excel worksheet with ActiveWorkbook.CustomDocumentProperties. When I close the workbook, I want to update an access table with all the ActiveWorkbook.CustomDocumentProperties.
This is the code I wrote - but it does not update the database.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
On Error GoTo UserForm_Initialize_Err
Dim dbDatabase As ADODB.Connection
Dim rs As ADODB.Recordset
Dim a As Integer
Dim b As Integer
If ActiveWorkbook.Path = "" Then
Exit Sub
Else
dbDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\masterfood.mdb"
rs.Open "categories;", dbDatabase, adOpenStatic
rs.FindFirst "mispar=" & "'" & ActiveWorkbook.CustomDocumentProperties("mispar") & "'"
If rs.NoMatch Then
rs.AddNew
rs!irgun = ActiveWorkbook.CustomDocumentProperties("irgun")
rs!l_name = ActiveWorkbook.CustomDocumentProperties("l_name")
rs!f_name = ActiveWorkbook.CustomDocumentProperties("f_name")
rs!tafkid = ActiveWorkbook.CustomDocumentProperties("tafkid")
rs!Subject = ActiveWorkbook.CustomDocumentProperties("Subject")
rs!delivery_date = ActiveWorkbook.CustomDocumentProperties("delivery_date")
rs!sender_f_name = ActiveWorkbook.CustomDocumentProperties("sender_f_name")
rs!sender_l_name = ActiveWorkbook.CustomDocumentProperties("sender_l_name")
rs!sender_tafkid = ActiveWorkbook.CustomDocumentProperties("sender_tafkid")
rs!remarks = ActiveWorkbook.CustomDocumentProperties("remarks")
rs!mispar = ActiveWorkbook.CustomDocumentProperties("mispar")
rs!Path = ActiveWorkbook.Path
rs!software = ActiveWorkbook.CustomDocumentProperties("software")
rs.Update
Else
rs.Edit
rs!irgun = ActiveWorkbook.CustomDocumentProperties("irgun")
rs!l_name = ActiveWorkbook.CustomDocumentProperties("l_name")
rs!f_name = ActiveWorkbook.CustomDocumentProperties("f_name")
rs!tafkid = ActiveWorkbook.CustomDocumentProperties("tafkid")
rs!Subject = ActiveWorkbook.CustomDocumentProperties("Subject")
rs!delivery_date = ActiveWorkbook.CustomDocumentProperties("delivery_date")
rs!sender_f_name = ActiveWorkbook.CustomDocumentProperties("sender_f_name")
rs!sender_l_name = ActiveWorkbook.CustomDocumentProperties("sender_l_name")
rs!sender_tafkid = ActiveWorkbook.CustomDocumentProperties("sender_tafkid")
rs!remarks = ActiveWorkbook.CustomDocumentProperties("remarks")
rs!mispar = ActiveWorkbook.CustomDocumentProperties("mispar")
rs!Path = ActiveWorkbook.Path
rs!software = ActiveWorkbook.CustomDocumentProperties("software")
rs.Update
End If
End If
rs.Close
db1.Close
Set rs = Nothing
Set db1 = Nothing
Unload UserForm1
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
can someone please help me?