TheBaroness
asked on
Deleting the entire row when refreshing external data
Is there a way to delete an entire row when external data is refreshed, the same way a entire row is added? I'm playing with the idea of having static data in the column after the data range.
ASKER
What "they" would like me to do is populate the data from the the database, but also include a static, corresponding cell that contains a description of the client. Clients are added and deleted from the database, so the spreadsheet will shrink and grow, and I need the corresponding descriptions to stay on the row with the appropriate client. I can't really massage the data once its in the spreadsheet, because its intended to be functional for any user without having to tweek it. I'm pretty sure this isn't the route to go, but I figured it would at least be good to know if it can be done this way.
Sounds pretty straightforward. So there is one record per client. Are the descriptions supposed to be written back to the database or just maintained in the workbook?
Kevin
Kevin
ASKER
If they could be written back to the database, that would be awesome and might just solve my problem, with added benefits. If I added a field in the database to hold the description data, can it go backward and forward, so to speak?
Yep. That is a much better solution. Then you don't have to "maintain" it in the workbook.
The update command is pretty simple with ADO. Assuming the field is now part of the Access table, all you need to is monitor for an edit (Worksheet_Change) in that column and update the database with some VBA code running in the background.
Kevin
The update command is pretty simple with ADO. Assuming the field is now part of the Access table, all you need to is monitor for an edit (Worksheet_Change) in that column and update the database with some VBA code running in the background.
Kevin
ASKER
I didn't even know this was possible, so can you give me an idea of what the code would look like? Would I be running it from the spreadsheet or the database?
The logic is in the spreadsheet. Assuming the description ends up in column C and the client key is in column A then the following code would update the field "Description" in table "ClientTable" where "ClientID" equals the value in column A.
Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).
Place this code in the worksheet code module in which the data is edited:
Private Sub Worksheet_SelectionChange( ByVal Target As Range)
Dim FocusCells As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [C:C])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
For Each Cell In FocusCells
MyDatabase.Execute "UPDATE ClientTable SET Description = '" & Cell & "' WHERE ClientID = '" & Cell.EntireRow.Columns("A" ) & "'"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
Kevin
Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).
Place this code in the worksheet code module in which the data is edited:
Private Sub Worksheet_SelectionChange(
Dim FocusCells As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [C:C])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
For Each Cell In FocusCells
MyDatabase.Execute "UPDATE ClientTable SET Description = '" & Cell & "' WHERE ClientID = '" & Cell.EntireRow.Columns("A"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
Kevin
ASKER
I can just about see this happening, but there are two issues ... the CELL variable is grabbing the data from the cell below the one I change (so if I change Q15, the data comes from Q16). I'm also getting a type mismatch error. If it helps at all, BlurbID is the key and the field is named BlurbStatic; on the spreadsheet the ID is in column A and the description field is column Q.
Try this:
Private Sub Worksheet_SelectionChange( ByVal Target As Range)
Dim FocusCells As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [Q:Q])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
For Each Cell In FocusCells
MsgBox "Updating client ID " & Cell.EntireRow.Columns("A" ) & "(" & Cell.EntireRow.Columns("A" ).Address( False, False) & " with value in cell " & Cell.Address(False, False)
MyDatabase.Execute "UPDATE ClientTable SET BlurbStatic = '" & Cell & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A" ) & "'"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
I can't see how the rows are getting mangled (15/16).
Kevin
Private Sub Worksheet_SelectionChange(
Dim FocusCells As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [Q:Q])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
For Each Cell In FocusCells
MsgBox "Updating client ID " & Cell.EntireRow.Columns("A"
MyDatabase.Execute "UPDATE ClientTable SET BlurbStatic = '" & Cell & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
I can't see how the rows are getting mangled (15/16).
Kevin
ASKER
Now the expression is pulling the data from the cell as soon as the cursor enters it ... before any changes are made (I love the message box though ... that helps a lot). And I'm still getting a datatype mismatch in criteria expression error. Let me ask you this ... is this something that could be triggered when the spreadsheet is closed, instead of on every change? Even if unchanged cells are "updated" in the database, that would be fine with me.
Oh crap! (Sorry!) I just realized I am using the wrong event handler!!! Try this one. The results will be much better.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FocusCells As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [Q:Q])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
For Each Cell In FocusCells
MsgBox "Updating client ID " & Cell.EntireRow.Columns("A" ) & "(" & Cell.EntireRow.Columns("A" ).Address( False, False) & " with value in cell " & Cell.Address(False, False)
MyDatabase.Execute "UPDATE ClientTable SET BlurbStatic = '" & Cell & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A" ) & "'"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
Postponing the updates and batching them is doable but a little more complex. I don't like the idea of shoving them all in at a later time as it increases the risk of collisions with other users. See how the above works and then we can look at different solutions if necessary.
Kevin
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FocusCells As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [Q:Q])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
For Each Cell In FocusCells
MsgBox "Updating client ID " & Cell.EntireRow.Columns("A"
MyDatabase.Execute "UPDATE ClientTable SET BlurbStatic = '" & Cell & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
Postponing the updates and batching them is doable but a little more complex. I don't like the idea of shoving them all in at a later time as it increases the risk of collisions with other users. See how the above works and then we can look at different solutions if necessary.
Kevin
ASKER
Lol ... doing this kind of thing calls for a lot of "oh craps" :). That code works perfectly for grabbing the correct data, but I'm still getting the data type mismatch. I changed the ID column to number format and the description column to text, but no luck. Any other suggestions?
As far as batching, it was just a thought ... this will work fine, it was just a curiousity question.
As far as batching, it was just a thought ... this will work fine, it was just a curiousity question.
I'm not seeing where a type mismatch can occur yet...try this code and, if the error occurs, click Debug and tell me the line it occurs on.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FocusCells As Range
Dim Cell As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [Q:Q])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
For Each Cell In FocusCells
MsgBox "Updating client ID " & Cell.EntireRow.Columns("A" ) & "(" & Cell.EntireRow.Columns("A" ).Address( False, False) & " with value in cell " & Cell.Address(False, False)
MyDatabase.Execute "UPDATE ClientTable SET BlurbStatic = '" & Cell.Value & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A" ).Value & "'"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
Kevin
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FocusCells As Range
Dim Cell As Range
Dim MyDatabase As ADODB.Connection
Set FocusCells = Intersect(Target, [Q:Q])
If Not FocusCells Is Nothing Then
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
For Each Cell In FocusCells
MsgBox "Updating client ID " & Cell.EntireRow.Columns("A"
MyDatabase.Execute "UPDATE ClientTable SET BlurbStatic = '" & Cell.Value & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A"
Next Cell
MyDatabase.Close
Set MyDatabase = Nothing
End If
End Sub
Kevin
ASKER
Its happening on the line:
MyDatabase.Execute "UPDATE CaseblurbSubtable SET BlurbStatic = '" & Cell.Value & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A" ).Value & "'"
Cell.Value contains the client description, but it does seem that there is any value in Cell.EntireRow.Columns("A" ).Value ... I'm not certain if there should be, but my guess was that should be the ID number.
MyDatabase.Execute "UPDATE CaseblurbSubtable SET BlurbStatic = '" & Cell.Value & "' WHERE BlurbID = '" & Cell.EntireRow.Columns("A"
Cell.Value contains the client description, but it does seem that there is any value in Cell.EntireRow.Columns("A"
That's odd...what is reported in the message box?
Kevin
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THAT'S IT!!! You rock!! This is SO much easier than my first idea ... thank you!
ASKER
Thanks for being so patient with me, and for opening up a new side of Excel. I really appreciate it!
Kevin