query in excel - updating and keeping the new data

[suzi]

hi everyone!
i am hoping that you could help me. even though i am terrible with explaining.

i have created a query from access and in excel made a table with it. i need few more columns on the right that corresponds with data in database connected part of table.
these new columns are not formulas that can be linked with some reference to the linked part, but it is custom (text, note etc).

the problem is that when i refresh workbook, if in source some data was deleted (which is OK), the same data (row) disappear from linked part of table. - the same data naturally disappears from excel table. but then i hit the problem - the "custom" new columns (text, notes) remains, and as such does not correspond with the data on the left (that is being shifted down as some rows are deleted).

 

[strive4peace]

you will need to repeat whatever column is unique in your disconnected part so that after records are refreshed, you can use code to go remove the custom information that should be deleted too

 

[Jerry Sullivan]

Hi suzi and Welcome to the Board,

If I'm understanding you correctly, you are adding notes and comments in added columns to the right of the table that you generate and refresh with a query.

Are the rows of data that result from the query doing aggregate functions (like summing or averaging) of the source data?

Is there a unique identifier (one field or the combination of more than one fields) that could be used to map comments before the refresh and then restore the comments after the refresh?

This thread shows an example of using VBA to map comments like this that are associated with rows of a PivotTable report.

http://www.mrexcel.com/forum/excel-questions/608648-how-annotate-pivot-tables.html

Just ask if you're interested in pursuing an approach like this for your scenario.

 

[suzi]

hi,

The data from query does not have calculated fields. it is made that generate from database all the information needed except the one the database does not contain. that is the various steps of status. (not approved, approved, in progress, loaded, delivered, finished, some of these are dates). this is all in rows, and corresponding with the query generated data.
the status fields would be typed manually.

Could this be done?

p.s.
I am not familiar with VBA.

 

[Jerry Sullivan]

I believe VBA could be used to do what you describe.

Am I correct in understanding that you can't (or prefer not to) add this field to your Access database?
If you could do that, you might use VBA to take the changes made in Excel and feed them back to your database which may be better for the consistency of your system.

If that isn't an option, please provide a few specifics...
1. Sheet name, table name
2. Field header of a field with a unique identifier

 

[suzi]

thank you for your help!

adding it in access is not an option.

sheet name: LOG
unique identifier field is: FAZ_ID

 

[Jerry Sullivan]

One more detail- what is the header on your "Status" data column in Excel and is it in the Table, or adjacent to it in a standard range?

I'll take a pass at some VBA code- probably won't be able to get to that until tonight.

 

[suzi]

thank you so much for your help!!

the new columns in excel are in Table.
these are the headers:[TABLE="width: 836"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
namireno (it is a date)
utovareno (it is a date)
vozac (text)
br.putnog naloga (text)
vozilo (text)
preuzima (text)
isporuceno dana (date)


[TABLE="width: 836"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Suzi,

Below is some VBA code you can try. Test this on a copy of your workbook.
The code assumes that you have one Table that has both the Query fields and added columns in which you have placed your notes.
The added fields for notes must be a contiguous range with no Query fields in between

Steps to setup.
1. If your workbook is not already saved as a macro-enabled workbook, use SaveAs to save as .xlsm format.
2. From your open workbook type Alt-F11 to open the VB Editor and Ctrl-R to view the Project Explorer (usually a pane on the Left side of the window).
3. In the project explorer, find your workbook's file name and double-click on the ThisWorkbook icon below it.
4. Paste the code below into the ThisWorkbook Code module (the text area on the Right side of the VB Editor window).

Private Sub Workbook_Open()
    '--Start monitoring for connection refresh events
    Call Sheets("[COLOR="#0000FF"][B]LOG[/B][/COLOR]").MonitorQuery
End Sub

5. In the project explorer, double-click on the "LOG" worksheet icon to open that Sheet Code Module.
6. Paste this code into the Sheet Code Module and edit "MyQTable" in the code to match your Query Table's name.

Option Explicit

Private WithEvents qt As QueryTable
Private vStoredNotes As Variant, vKeysBefore As Variant
Private sTableName As String, sKeyField As String
Private sFirstNoteField As String, sLastNoteField As String

Public Sub MonitorQuery()
    '--initialize module scope variables
    sTableName = "[B][COLOR="#0000FF"]MyQTable[/COLOR][/B]"
    sKeyField = "[B][COLOR="#0000FF"]FAZ_ID[/COLOR][/B]" 
    sFirstNoteField = "[B][COLOR="#0000FF"]namireno[/COLOR][/B]"
    sLastNoteField = "[B][COLOR="#0000FF"]isporuceno dana[/COLOR][/B]"
    
    On Error GoTo ErrorHandler
    Set qt = Me.ListObjects(sTableName).QueryTable
    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err & ": " & Error(Err.Number)
End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    On Error GoTo ErrorHandler
    '--store the unique IDs from the QueryTable
    vKeysBefore = Application.Transpose(Me.Range(sTableName & "[" & sKeyField & "]"))
    '--store the Notes data
    With Me.Range(sTableName & "[[" & sFirstNoteField & "]:[" & sLastNoteField & "]]")
        vStoredNotes = Application.Transpose(.Cells)
        .Cells.ClearContents
    End With
    Exit Sub
ErrorHandler:
    vStoredNotes = Empty
    MsgBox "Error " & Err & ": " & Error(Err.Number)
End Sub

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    Dim vKeysAfter As Variant, vRemapped As Variant
        
    If IsEmpty(vStoredNotes) Then Exit Sub
    
    On Error GoTo ErrorHandler
    If Success Then
        '--Get updated unique IDs from the QueryTable
        vKeysAfter = Application.Transpose(Me.Range(sTableName & "[" & sKeyField & "]"))
        '--Transfer stored data into new array matching new order of Unique IDs
        vRemapped = Remap_Notes(vStoredNotes, vKeysBefore, vKeysAfter)
    Else
        vRemapped = vStoredNotes
    End If
    '--Write remapped data
    Me.Range(sTableName & "[[" & sFirstNoteField & "]:[" & sLastNoteField & "]]") _
        .Resize(UBound(vStoredNotes, 2), UBound(vStoredNotes)) _
            = Application.Transpose(vRemapped)

    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err & ": " & Error(Err.Number)
End Sub

Private Function Remap_Notes(vStored, vKeysBefore, vKeysAfter) As Variant
    Dim vRemapped As Variant, vIdx As Variant
    Dim iRow As Long, iField As Long, iNoteFieldCount As Long
    
    On Error GoTo ErrorHandler
    
    '--resize array
    iNoteFieldCount = UBound(vStored, 1)
    ReDim vRemapped(1 To iNoteFieldCount, 1 To UBound(vKeysAfter))
    
    For iRow = 1 To UBound(vKeysAfter)
        vIdx = Application.Match(vKeysAfter(iRow), vKeysBefore, 0)
        '--if match, transfer row of stored data
        If Not IsError(vIdx) Then
            For iField = 1 To iNoteFieldCount
                vRemapped(iField, iRow) = vStored(iField, vIdx)
            Next iField
        End If
    Next iRow
    Remap_Notes = vRemapped
    Exit Function
ErrorHandler:
    MsgBox "Error " & Err & ": " & Error(Err.Number) & vbCr _
        & "Notes will be restored to previous range"
    Remap_Notes = vStored
End Function

7. Save your workbook and close it.
8. When you reopen your workbook and enable macros, the Notes fields should now update to stay aligned with the Unique ID values whenever the QueryTable is refreshed.

Thanks to MVP Rory for showing how to trigger code when a Query Table is refreshed in this thread.
http://www.mrexcel.com/forum/excel-...check-if-excel-querytable-refreshing-not.html

Please let me know if you have any difficulties getting this set up and working.

 

[suzi]

hi
i have done as you instructed me, and after i save it all and reopen excel, it tells me "error 9: subscript out of range".

in the step 5. when Sheet Code Module opens - do i leave as it was: the "General" or choose "workbook" option on upper left corner, and declaration on right side?