Importing / OLE DB Query

[nkippen]

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

 

[Dick Kusleika]

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 there: 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.

 

[Jamie Collins]

I don't think so. You can definitely import only new records, but that's
all that will be there: 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.

--

 

[Dick Kusleika]

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?

 

[Jamie Collins]

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.

Jamie.

--