Deleting the entire row when refreshing external data

TheBaroness
TheBaroness used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
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

Author

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.
Top Expert 2008

Commented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Author

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?
Top Expert 2008

Commented:
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

Author

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?
Top Expert 2008

Commented:
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

Author

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.
Top Expert 2008

Commented:
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

Author

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.
Top Expert 2008

Commented:
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

Author

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.
Top Expert 2008

Commented:
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

Author

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.
Top Expert 2008

Commented:
That's odd...what is reported in the message box?

Kevin
Top Expert 2008
Commented:
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

Author

Commented:
THAT'S IT!!! You rock!! This is SO much easier than my first idea ... thank you!

Author

Commented:
Thanks for being so patient with me, and for opening up a new side of Excel. I really appreciate it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial