Really need help with external excel files

[zu3st3r]

Hi there people,



Well firstly I'd just like to thank most of you guys for this great forum, have been helping me so much the last couple of years. Never really registered but think it is time now since I can help out, but need some help urgently.

Like the title says, I have one very big database file in excel format. In it is about 300 000 odd details of people, with their ID numbers, contact details etc.

At every month end I get a list, similar to the one I have, just smaller, that needs to be "compared" to the big database file. I need to be able to cross check list A (big file) with list B (small file that changes every month) to see what people we have on the list, pick up the duplicates, and then once it finds it, I need to be able to add the ones not in the list to the big file, or simply just see who we keep getting as duplicates.

Big thing is, we need to be able to print out a report every month end. And I need to make something that my boss can understand . So the front end must really be simple.

Will it be possible to do something like this in access? Or am I barking up the wrong tree here.

What I could do, was get the entire access file going with the two imported tables, and pick up the files missing from A or even B, but then it is fixed to that one month file, I cannot think of a way to get the imported monthly file inside without letting the branches shake inside of my bosses head.

I would really appreciate it if someone could help me out here, or just lead me to the right direction.

The monthly print report I can do once I get the entire thing working.

Thanks in advance guys,

Take care,,,

[alansidman]

There are two wizard queries in Access that would help you.

Duplicates Query and Unmatched Query

This should help you get started

http://office.microsoft.com/en-us/ac...001034558.aspx

[zu3st3r]

Hi there,

That is exactly what I did, that part I'm sorted out with. The problem is when I'm not @ work, I need to make it as simple as possible for the boss to use, I can't tell him to go to import, select excel, then run the steps to convert sheet to table..... The oke will fire me .

The main thing I was thinking of, was creating a front end of the entire thing, kinda like a browse button where he points to the new excel file, and as soon as he selects it, all the stuff I did happens in the background, kinda like a script running.

I have seen something similar few years back so I know it can be done..... Question always is -> How?

[alansidman]

Here is an example of a similar situation that I did for some attorney's that knew nothing about Access. You will have to modify this example for your specifics, but it is all VBA driven. The users only had to select three different command buttons to get the desired results. This should be a good jumping off point to get you started.

[zu3st3r]

That is exactly it! Thank you so much!

[bansalpankaj88]

Hi alansidman,
i went through your code. it works fine when i use the database provided by you.
but when i created a database of my own and used used your VBA code to import excel sheet, it shows error "Compile error: user-defined type not defined".
Used-defined type for which error occurs is "Excel.application".
please suggest what can be possibly wrong? do i need to add some reference library.

[alansidman]

Have you checked to see if you are missing any references?