Link to home
Start Free TrialLog in
Avatar of TheBaroness
TheBaronessFlag for United States of America

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.
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Can you be more specific? When you have a table range that is populated with data from a live external feed, you really should not be playing with the rows and columns from that feed. What you can do is write a macro that refreshes the feed and then copies the data to a another tab and there you can massage the data any way you want.

Kevin
Avatar of TheBaroness

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
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
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.OLEDB.4.0; Data Source='C:\full\path\to\database.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
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.OLEDB.4.0; Data Source='C:\full\path\to\database.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
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.OLEDB.4.0; Data Source='C:\full\path\to\database.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
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.
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.OLEDB.4.0; Data Source='C:\full\path\to\database.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
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.
That's odd...what is reported in the message box?

Kevin
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
THAT'S IT!!! You rock!! This is SO much easier than my first idea ... thank you!
Thanks for being so patient with me, and for opening up a new side of Excel. I really appreciate it!