Merge two excel files into another one

[chennai141]

Hi everybody,

I have two excel files please find attached documents
and also i am attaching the output file how it should look like.

i have a form in Access which accepts two files
book1 and book2
with a generate button. when i click generate button it should accept these two excel files and generate another excel file called output.

Thank you for the help

Regards,
chennai

[zmbd]

Most of us either can not or will not open un-requested attacments. This should not be taken personally. It is often due to the fact that we are at work PCs and our IT staff prohibits doing so, or, in fact, is part of the general "Best Safe Computing Practices."
Instead, please try to clearly state what your goal is, what you have tried (we like to see the work), what the results were along with the EXACT title, number, and text of any error messages you've received.

[chennai141]

Actually i am not a developer ...
i am looking for a program where i can get this task done. Because it is really annoying me to do manually.
so please help me.

Most of us either can not or will not open un-requested attacments. This should not be taken personally. It is often due to the fact that we are at work PCs and our IT staff prohibits doing so, or, in fact, is part of the general "Best Safe Computing Practices."
Instead, please try to clearly state what your goal is, what you have tried (we like to see the work), what the results were along with the EXACT title, number, and text of any error messages you've received.

[chennai141]

The main goal is:
in book1.xls i have a header row with the following columns:
Role
code
country
count
And in the book2.xls file have a header row with the following columns
country
location
Now i need to match country field in book1.xls and get the location field from book2.xls and place exactly right beside country field in book1.xls and this is what in the output file which i attached.

if any more information you need please let me know

Actually i am not a developer ...
i am looking for a program where i can get this task done. Because it is really annoying me to do manually.
so please help me.

[zmbd]

You should be able to do this without using Access.
Which version of Excel are you using?
Does the data in Book2 change?

Just so that I understand what you are currently doing:
you have been opening book1, inserting a column next to the "country" and entering "location" for the header. You are then by hand opening book2 and then, by hand, looking up the information from book2 as a cross reference and by hand entering that value, yes?

For this first part, the built in macro recorder can build your base code. As such would be normally available to you, the basic code will be:

Sub InsertColumnForLocation()
'
    Range("A1").Select
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("D1").FormulaR1C1 = "Location"
    Range("A1").Select
End Sub

Note that I am working on the concept that your headers are in row1. So the first thing I do in this code is send you to Home on the worksheet - not strictly needed; however, bad habit.
Then We slect the entire D column (same as clicking on the column-button) and then insert the column (there are other ways to do this; however, I've sometimes had deep cells not shift properly useing them whereas this method doesn't seem to fail) and the header text.
This is very plain, no formating etc...

The next step will be how to handle the data.
That may depend somewhat on the version of excel you are using.
What I would do is bring in your book2 data, sort it on the country field, assign it a range name, then use VBA to enter a VLOOKUP() formula into your location column.

-
Sorry, no, still not opening the attachments, still at a work PC.
Also as we progress thru this project, please keep in mind that Bytes is neither a code writing nor homework service.

[chennai141]

Hi thank you for the effort and helping me a lot..
but it is not the right one what i want...
if you could send me your id then i can send files to you..

Thank you for the help

[zmbd]

Descriptive Detail Please

chennai141

hi,
thank you for the effort and helping me a lot..
>>But it is not the right one what i want...
If you could send me your id then i can send files to you..

Thank you for the help

You need to explain that with a tad more descriptive detail - blame it on the children :). Is it that you are being required to use Access, the first and or second part of the solution doesn't work, or some other reason?

As for sending files via private messaging. The site rules generally prohibit the "Staff" from handling things via PM or offsite - exceptions can be made; however, I'd involve one of the site admins before doing so... The main reason is that if the solution is found, then the thread, and therefor the site and our memebers, do not benefit from the effort and the solution.


> How to ask "good" questions.
> FAQ = Asking Questions
> Posting Guidleings.

[chennai141]

Hi

I have a form in access in which i have two text boxes which accepts two excel files with a button click.
now when i click generate button an output excel file has to be generated or created based on the following conditions


In one excel file i have these data :

id code country count
t100 gb123 india 3123
t100 gh125 UK 1258
t123 ytr15 USA 1111
t123 gb123 Germany 100
t145 gh575 india 99
t458 yt777 USA 90


In another excel file i have these data

country location
India delhi
UK london
USA wallstreet
Germany frankfurt


The rows can be more than what i mentioned here ... now i want to merge them
according to the country. In book1 excel file for example wherever you find country india the location
field delhi has to be inserted right beside the country field and it has to be done for each and every country which i mentioned in book2 excel file
and the output file has to be sorted according to the count at last.

For example the output file should like this

id code country count Location
t100 gb123 india 3123 delhi
t100 gh125 UK 1258 london
t123 ytr15 USA 1111 wallstreet
t123 gb123 Germany 100 frankfrt
t145 gh575 india 99 delhi
t458 yt777 USA 90 wallstreet

[zmbd]

Is this a school project or an employment interview question?!
It certainly sounds like "How many quarters does it take on edge to equal the height of The Empower State Building?"
If you truly are stuck with the requirement that you must first import the files into MS Access, then you need to look at the transferspread sheet method.
I have a few threads onsite that have gone into some detail about this. It will take me a little bit to find them; however, I will post back the thread links in a little bit (I've got to find them :) ), or you can search the forum for them.
Transferworkshe et Method
One of about a dozen threads where I've covered this:

http://bytes.com/topic/access/answers/944237-multiple-queries-multiple-contacts-email-vba

[chennai141]

hahhaha its not a school project neither interview question .
its a part of self learning...Afte r getting this done i want to generate the output file with the graph....

If you find any other links please let me know ...
thank you