hey all,
I'm rappidly running out of ideas as to help with this one,
Basically I have 2 excel documents that contain data that needs to be shipped off external sources. What I'm doing at the moment is using PDF wizzard to generate PDF's of the specific parts of the excel documents I want to send and then emailing them out.

What I would like to do is develop something, either in access, filemaker or whatever that I can click and import the excel document into have it split up the relevant data and then email the result out to the people who need specific data.

Anyone have any ideas?

Cheers

Mathew

You would have to:

1. Design a set of database tables based on the excel spreadsheets called the operational data store
2. Import the excel spreadsheets into two large tables in Access called the source data
3. Extract the source data using queries into the operational data store
4. Query the operational data store to generate the reports you need to send out as PDFs.

ok,
based on that I dont think Access is going to be powerful enought,

Im trying to make the system idiot and stupid proof and it seems that what you have described would complicate someone.

I'm looking into something like Orcale at the moment, so that could be interesting.

Mat

I would just write an Excel macro, but I'm lazy.

-PatP

Oracle would probably be a little heavy handed

I would say Certus's method is pretty well a standard answer to this sort of thing (plus the odd fancy phrase like operational data store - was that some added value?). I can't see how doing this is oracle rather than access will make this simple and idiot proof unless you are much more familair with Oracle.

In any event, the problem is the same irrespective of the tool (unless you are Pat).

pootle flump ur codings are working excelent.

Actually I gave up with both Access and Oracle.

I decided to write the whole thing in VB inside excel.

I am however a bit stuck on the logic now.

I have the program open the selected excel document, filter down to the required data parts but this is where I get stuck.

I need the program to use a filter to filter and select data concerning a company, copy the data to a new sheet, save the sheet then go back at repeat all that again for the next company.

The bit I cant get to do is the selection of a company, (I can get it to select the 1st company and copy etc), but how do I then go back and select the next company and the next and the next and so forth?

Any ideas?

Cheers

Mat.

Have you tried recording what you need with the macro recorder.
If you know how to do things manually then the recorder is a good start to getting the correct piece of code you need. Just remember to tidy up the code that comes out of it as its usually a bit of a mess although it will be correct.

Mat,

One way is to use an advanced filter on the company field, selecting unique entries only, and copy that to a new location (maybe a spare or temporary worksheet).

Then (for speed) save that list to an array and loop through the array doing what you need to do for each company name; or, leave the newly filtered list of unique names in a worksheet range and loop through those cells.

OK?
Fazza

That sounds a bit too complex for what I want,

Is their not a simple command that I can write into a loop that selects a specific data set from a field (a company name for example), then when the loop is completeted it goes back and selects the next company from the list and starts again?

Cheers

Mat.

Hi Mat,

What about something like
Or in an array, loop through from the LBound to the UBound.

The hurdle is setting up the list of unique names. Excel has built-in functionality do that - the advanced filter.

Another way is a data table query on the "Company" field. Such as to a separate worksheet and give the result a defined name. Each time the query is refreshed the named range will automatically adjust. Just be aware that the header will be included in the named range, so, exclude it if looping like posted above. The query would have SQL of the form "SELECT DISTINCT Company FROM table_whatever" and could be set up once outside VBA and then VBA would simply "wks_Whatever.QueryTables(1).Refresh BackgroundQuery:=False", or, otherwise create the query on the fly each time. I've found if doing this second way, better to create a new worksheet each time and delete it after use.

Thinking further, if you don't want to create a unique list, build one as you go. Such as start with the company name in the first record, store it in an array "UsedNames", do the processing, LOOP STARTS, go to the company name in the next record, check if it is in array "UsedNames", if not add name to the list, do the processing, LOOP

OK?
Fazza

Thanks for this.

I just got into work now, so ill spend all day playing on it.

Cheers

Mat