Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing / OLE DB Query
I don't know exactly to what forum I should be posting this question so I thought I'd start here with the gurus. Situation: Background: I work in the bankrupcy department of my company. Each week there i an excel document that is updated with new accounts that have gon bankrupt. (I copy this document to another location so it doesn't ge overwritten.) On my copy of the accounts I have a color coding schem to classify each account. However, each week there are more account added and I am currently having to go threw the entire list of account again, color coding not only the ones I've already looked at, bu analyzing the newly added accounts to the excel document. The new accounts that are added to the [weekly updated] excel documen are organized by their account number, which makes it more difficul because the new accounts are scattered throughout and mixed in with th older accounts. Is there away that I can import only the newly added accounts to m copy of the excel document on my desktop, that way all the account that I have color coded remain color coded and I can easily go dow through the list analyzing only the newly added accounts to the exce document that is updated weekly? Or in other words is there a way to only import the newly adde accounts to my excel document without having to import the entir document thus overwriting everything that I have done? Is there some parameters I need to use in my Query options with th Importing tool? Or will I have to come up with some Visual Basic Macr or something to be able to do this? So many questions.. phew -- nkippe ----------------------------------------------------------------------- nkippen's Profile: http://www.excelforum.com/member.php...fo&userid=1548 View this thread: http://www.excelforum.com/showthread.php?threadid=27061 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing / OLE DB Query
"nkippen" wrote in message ... I don't know exactly to what forum I should be posting this question, so I thought I'd start here with the gurus. A fine choice. excel.querydao would have been okay too. Is there away that I can import only the newly added accounts to my copy of the excel document on my desktop, that way all the accounts that I have color coded remain color coded and I can easily go down through the list analyzing only the newly added accounts to the excel document that is updated weekly? Or in other words is there a way to only import the newly added accounts to my excel document without having to import the entire document thus overwriting everything that I have done? Is there some parameters I need to use in my Query options with the Importing tool? Or will I have to come up with some Visual Basic Macro or something to be able to do this? I don't think so. You can definitely import only new records, but that's all that will be the new records. The old ones will be gone and you don't want that. I would probably make a separate table of previously analyzed records using some kind of unique field to identify them and a code to determine what color they should be. Then I would use VBA to re-color the rows after the query table was refreshed. In as separate file (an excel file or even a text file) you could have information like 1001 Blue 1003 Red 1010 Blue and the macro would read down this file and color the rows the way they were. Another macro could update the file when you're done analyzing the new records to record their colors. You definitely need some way to uniquely identify those records. If this sounds like something you want to try and you need some help implementing it, post back. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing / OLE DB Query
"Dick Kusleika" wrote ...
Is there some parameters I need to use in my Query options I don't think so. You can definitely import only new records, but that's all that will be the new records. The old ones will be gone and you don't want that. Dick, I think the OP wants to create an outer join on the two sheets, looking for a null in the key column in the existing sheet e.g. something like this (untested): INSERT INTO [Excel 8.0;Database=C:\Documents and Settings\nkippen\Desktop\MyExcelDoc.xls;].[Sheet1$] (Account_nbr, this_column, that_column) SELECT T2.Account_nbr, T2.this_column, T2.that_column FROM [Excel 8.0;Database=C:\Documents and Settings\nkippen\Desktop\MyExcelDoc.xls;].[Sheet1$] T1 RIGHT JOIN [Excel 8.0;Database=C:\Temp\WeeklyUpdated.xls;].[Sheet1$] T1 ON T1.Account_nbr = T2.Account_nbr WHERE T1.Account_nbr IS NULL; Or maybe not (I don't use the GUI tools too often). Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing / OLE DB Query
Jamie
Dick, I think the OP wants to create an outer join on the two sheets, looking for a null in the key column in the existing sheet e.g. something like this (untested): INSERT INTO [Excel 8.0;Database=C:\Documents and Settings\nkippen\Desktop\MyExcelDoc.xls;].[Sheet1$] (Account_nbr, this_column, that_column) SELECT T2.Account_nbr, T2.this_column, T2.that_column FROM [Excel 8.0;Database=C:\Documents and Settings\nkippen\Desktop\MyExcelDoc.xls;].[Sheet1$] T1 RIGHT JOIN [Excel 8.0;Database=C:\Temp\WeeklyUpdated.xls;].[Sheet1$] T1 ON T1.Account_nbr = T2.Account_nbr WHERE T1.Account_nbr IS NULL; You may be right. I got the impression that the external data was coming from some other software, not Excel (although it doesn't say that explicitly.) If that's the case, can you still do an outer join? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing / OLE DB Query
"Dick Kusleika" wrote ...
I think the OP wants to create an outer join on the two sheets, looking for a null in the key column in the existing sheet You may be right. I got the impression that the external data was coming from some other software, not Excel (although it doesn't say that explicitly.) Dick, I may be wrong but I read it as two Excel workbooks being compared i.e. Each week there is an excel document that is updated with new accounts Is there away that I can import only the newly added accounts to my copy of the excel document Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Union Query | Excel Discussion (Misc queries) | |||
Importing access query | Excel Discussion (Misc queries) | |||
Importing Access Query | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Problem importing using MS Query | Excel Programming |