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.
TheBaronessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
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
TheBaronessAuthor Commented:
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.
zorvek (Kevin Jones)ConsultantCommented:
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

TheBaronessAuthor Commented:
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?
zorvek (Kevin Jones)ConsultantCommented:
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
TheBaronessAuthor Commented:
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?
zorvek (Kevin Jones)ConsultantCommented:
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
TheBaronessAuthor Commented:
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.
zorvek (Kevin Jones)ConsultantCommented:
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
TheBaronessAuthor Commented:
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.
zorvek (Kevin Jones)ConsultantCommented:
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
TheBaronessAuthor Commented:
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.
zorvek (Kevin Jones)ConsultantCommented:
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
TheBaronessAuthor Commented:
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.
zorvek (Kevin Jones)ConsultantCommented:
That's odd...what is reported in the message box?

Kevin
zorvek (Kevin Jones)ConsultantCommented:
Unless Access is expecting a numeric value for the ID...try this version:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TheBaronessAuthor Commented:
THAT'S IT!!! You rock!! This is SO much easier than my first idea ... thank you!
TheBaronessAuthor Commented:
Thanks for being so patient with me, and for opening up a new side of Excel. I really appreciate it!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.