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).

 

[suzi]

Dear Jerry,

thank you so very much! your solution is indeed a SOLUTION to my problem!! I cannot find a words to thank you enough.


p.s. previously I had made an error. your code is working perfectly.

 

[Jerry Sullivan]

Suzi, I'm happy to hear that worked for you.

I'd suggest saving the workbook before refreshing (at least until you've used the code for a while) just in case something unexpected happens and the notes are cleared but not restored.

I've tried to prevent that scenario in the error handling - but it's best to be safe.

Cheers!

 

[suzi]

i will keep you updated but so far, so good!
i hope that your preventive measurements will be effective

bye, suzi

 

[strive4peace]

impressive code, Jerry. I loved seeing how you work with tables since I am passionate about databases. Excel has come a long way working with structured data. The way you hooked up the code, populate arrays from cells and then reshape the results is particularly interesting to me. I learned some very cool tricks, thank you

is this all you have to do to hook up the code (aside from naming properly) to the table events?

   Private WithEvents qt As QueryTable
   Set qt = Me.ListObjects(sTableName).QueryTable

In case you are interested, John Mishefske has a teaching example for Access here:
Hangman Demo - Subclass Controls and Sink Events Using WithEvent Keyword, Hangman Game by John Mishefske
Downloads - Tigeronomy Software

thank you, Jerry

 

[Jerry Sullivan]

Hi Crystal,

I appreciate your thoughtful feedback.

Yes, those two statements are all that is needed to hook up the events to the object assigned to qt. Because qt is declared with Private scope in a single worksheet, the code could be repurposed in other sheets without a conflict. (just add additional calls to the Workbook_Open event code).

Thanks for providing the link to the Subclass Controls Demo. I'll keep that handy for the next opportunity I can find to apply that to a group of UserForm controls.

 

[strive4peace]

thank you, Jerry, and you're welcome

 

[Fazza]

There have been some similar posts in the past, like http://www.mrexcel.com/forum/excel-questions/381918-aligning-manually-entered-data-external-data-query.html

The suggested approach was similar to Jerry's post but to use queries to do the work. It is a little confusing to explain. This'll probably be no clearer than previously..

Set up another query, that just pulls a unique identifier and the custom entered text - I'll call it Comments. Before refreshing the data from the database, first refresh this new query. Its query would by "SELECT UniqueID, Comments FROM YourExcelTable"

The past solution was to modify the original query so that when it now refreshes, instead of pulling data just from the database, it also grabs the comments at the same time. Its SQL might be like

SELECT A.UniqueID, A.OtherFields, B.Comments
FROM DatabaseTable A, LEFT OUTER JOIN [Excel 8.0;Database=YourDrive:\filename.xls].[YourExcelTable] B
ON A.UniqueID = B.UniqueID

I think that would be neatest.


Another (messier?) way would be to instead add two update queries like this, untested
1. query as above to run before you refresh from the database
2. your current database query unchanged, then after that
3. UPDATE YourResultTable SET Comments Is Null
4. UPDATE YourResultTable A INNER JOIN YourExcelTable B ON A.UniqueID = B.UniqueID SET A.Comments = B.Comments

I've assumed the new table I refer to is in the same Excel file. If you prefer to not query the active file, suggest instead using an mdb file, or separate xls file, or whatever.

hth

 

[Jerry Sullivan]

Hi Fazza,

Thanks for sharing that approach. I'll wait until the weekend to work through that uninterrupted as it looks like a stretch for my fledgling SQL skills.

I appreciate your recently pointing me to some links that show the basics of using SQL queries of Excel sources.
I'm using the technique often for simple queries and starting to get the hang of it.

 

[dhouseknecht]

Hey Suzi,

I'm having the same error that you had...could you please tell me how you fixed the "Subscript out of range" error?

p.s. previously I had made an error. your code is working perfectly.