combining information from multiple excel files and exporting to a word document or pdf

[rgs80074]

hello everyone,

as the title mentions i need a lot of help with excel with the macro's and programing etc.

i have a few different things i am trying to accomplish

i first will apologize i am not at my normal pc so i don' t have the samples available i would have otherwise to help clarify what i want to do.

as mentioned i have a few different things i am trying to do and i am assuming the easiest and best is to have them all be separate from each other.

i also assume there would be a separate excel file used as one of the information files and this one would be used across any of them i am making.

this one info file would not change very often and would contain some basic information such as: vendor name, route number, drop, service, etc

there might even be another static info file that lists publications and service codes, not sure if that would be needed or not.

we are using a program/website called my district which produces or can export to excel files and these exports would be the second information file that would be used.


i probably have about half a dozen things or so i would like to do but i'll start with just one and with help i might be able to do most of the other stuff and just get occasional help here and there for things.

so basically you have a few excel files some that rarely change and one or so that changes on a daily basis, the macro/program then imports them i guess you'd say, sorts, queries, etc based on what macro is running and outputs whats needed to a word document or pdf, etc file for printing.



the first thing i am looking for right now is a multiple list and basically here is what i mean by that. any address that has either a draw of more than one or has more than one account would be on this list. it would use to separate excel files one for each of the districts for the information plus the other info excel files that alreayd contain both districts info on the contractors, routes, etc

the list would be sorted as follows.

each route/contractor

then it would list higher than 1 draw accounts

then it would list address's with more than 1 account

then it would have a page break and move onto the next contractor, if for some reason a route/contractor does not have any then it just skips them.

each contractor would then have a list that breaks down their multiple accounts and such by route and address.


i think this is the first thing i should start looking at. perhaps this will give me the info i need to work on creating the other basic ones i need and can work my way up to the more complex ones for stack out and such.

i'll try to secure some samples of what i am working with and post them later today.

perhaps someone can suggest what i am looking at to get started or have questions etc.

thanks

ryan

[arlu1201]

I guess we will really understand what you are trying to do if you upload some sample files.

[rgs80074]

i'll have to get back in a day or two with samples.


i have samples had them ready to upload and when i was explaining them i realized that they contained information such as the persons name and account numbers, not that the chances of anyone here would track them down i have to block that from the image files before i do that.

maybe the best option is to print them out black out the info i don't want shown and rescan them that way there's no imaging layers or whatever that can be removed to show the info again.

thanks

ryan

[arlu1201]

You do not need to take so much trouble.

All you can do is create a copy of your file and just remove the sensitive data.

[rgs80074]

hello

ok the samples and xls have been created. i just edited out the sensitive info on the pc.

not sure where to begin since the samples really include everything i am trying to do.

remember this all i hope to output to a word or pdf file.

i'll go sample by sample, i am assuming its easiest or best to create separate programs/macro's etc for each but if it could be a single form with buttons for each selected operation.

anyways i'll explain each sample.

the cis list: this is one of the source files and this is the one that changes daily. you'll notice the zip codes and account numbers have been removed. i just left the columns there becasue this is the actual output i didn't want to change anything other than removing the info.

vendor list: this is a list of the routes, vendor name, drop #, and service (depending on what i am doing depends on what is used on this)

the rest are jpegs

multi list: this is one of the first things i am looking at. you'll see that its got more info on it than what we are looking for which results in less address's per page with more info that needed. we want a setup like this but smaller font size and it only needs to have # to deliver, address, accounts below it, which only needs to have the pub and fod and draw. i am assuming we can get this down to having two columns and about 15 or so address's per column. i woudl also like to add one more thing to this which is not on the sample and thats the vendors name, maybe up by the date on it.


drop list: this one would probably need another info source as well, that indicates the drop number and address. this output also does not need the account, phone, or name (names are not supplied on the cis), it only needs the publications as you see bolded, address and number of copies, no need to have pub twice, a total for this pub would be nice too. the way this would work it you put in the generic address in this case for this sample its 250 s park ave, the program/macro, etc scans the cis list, pulls out any address we have listed on the drop list info source and outputs this 1 page (unless there is more address than can fit on one page), with the info listed plus the date and drop number. we don't want the vendor or anything like that because these are being left as drop locations.

the next sample is a complaint list: this one is kinda simple i think. it will use a complaint list (sorry no sample on it yet) and it just lists the complaints they received. there is no need for the name and phone columns.

image 1: is a load tag so to speak. as you can see this one is listed as service. this is one of the last things i am worried about right now. but basically what this does is add up customers for the said contractor (only the os) gets the more detailed results, everything else just gets a total and while you can't see it here on vendors with service it puts a small number (whatever the service is) right above all the numbers, except for the total os where it puts it below it. you also see the os gets more details such as bundle size which is in the box next to front bundles, then it populates the other boxes based on the total os draw, the number of bundles needed and then loose fronts needed to obtain the magic number.

samples not included:

a front stackout sheet, its basically the a one sheet listing of vendors which just the front totals (i will get a sample next time i can)

an alt stackout sheet: same as the front sheet only it lists all the alt publications for the vendor

then the last thing would be an insert stackout sheet but right now my district don't do inserts right now so its one of the last things.

now i am not expecting help with all these, if i get it fine but not expecting it, really i am hoping to get enough help to get me started on it. i am thinking once i get enough help or understanding on how to do one of them say the multi list the rest will fall in line but maybe i am not correct.

but as i think once i know how to get a macro/program to pull all the needed info from multiple source files (hopefully automatically so its just a run this or something like that) and it does what we want and it outputs it the way we want so i know how to edit the outputs to fit our needs i can figure out the rest.

its the setting one up that i don't under stand and the output design. once i get that done if i run into problems i am sure its a lot easier to ask how to do something when you can easily explain what your looking for.

i hope this has helped explain it some.

so if we can get started on how or what i need to do to get the multi list underway i would greatly appreciate it. any guidance and help or more questions will be helpful.

thanks

ryan

[rgs80074]

one last sample excel file

[rgs80074]

i guess i really should start in steps.

i guess the first thing would be to get the two sources into one thing

so i guess i need to get it to import one of the files, make the addition to the routes numbers listed

then import the second file and make the additions to its route numbers.

once i get that done i am work on getting it to sort the merged file by route then address

then work on it doing its queries i guess thats what it would be.

my problem is i don't know where to start which is why i am here looking for help.

[arlu1201]

Actually you have typed in so much, that i am lost

Can we go step by step ? You tell me what you require first and i can help you with it.

[rgs80074]

hello

yes i am sorry about that i tend to do that, something i am trying to work on.

if you take a look at the multi list sample, thats the first thing i am trying to do/ create.

i need to be able to take two different excel files

import one of them automatically (ie have the excel saved to a specific name and location) and take the route listed and add to that route and replace it (based on the saved excel file) and then do the same thing to the second one.

at this point we'd have 1 new excel file that has the edited two files merged.

i guess thats the first step in what i am trying.

then i need to get that to be sorted by the route and then address.

then something runs through that and looks for address's that are the same(or have a quantify of more than 1) an don the same route and i guess puts that in another temp file/excel

after its run through the whole excel file and copied all the address that are the same or have a quantity of more than 1 it takes that new temp file and exports it to a doc or pdf (i think a doc would probably be the preferred version).

the output would be broken down by route and list each address similar to how the sample is, but i am thinking we don't need all the info that's on that sheet only part of it so we can have two columns on each page of address's, if we could also combine multiple routes one after another if the vendor is the same would be another added bonus.


so if we want to take it in steps then the first thing is to import an excel file and edit/replace the route numbers listed based on which import it is.

we have two districts

example district 1 and district 2

both have the same route numbers

so we need it to import district 1 and add to the route numbers listed

it would have like route 101, 201, 301, etc

it would add to that to make them 1-101, 1-201,1-301 etc.

it would do the same thing to the second excel file for district 2 only the edited routes would be 2-101, 2-201, 2-301

then it sorts them by route and then address.

perhaps we need to have it automatically overwrite any output file it creates or have it automatically delete them when its done with a step.



i hope this helps and still isn't' too long, i fear it is but not sure how to make it less and still get the first step.

and i will appreciate any help as will a bunch of others but i would like to get more than just having someone do it i would like to know how and why. perhaps i can learn this as we go and i can figure out the other stuff with only minimal help.