Macro extract data from other workbook and worksheet

[madi1004]

Hello,

I need your help in extracting data (part no - column c) from the file "fbwls" (without duplicates) into the file "all" sheet Desired Results. Also, additional part no that are in the same file "all" Sheet "Transit" should also be added to the sheet "Desired results" under the parts retrieved from the file "FBWLS".


At the end, there should be no duplicates part numbers in the sheet "Desired results".

I have inputted the part no manually into the "Desired Results" sheet, as I want to have them at the end.

Is it possibil to do this with a macro or some formulas?

Thanks for your help!

Madi1004

[tfurnivall]

Sounds reasonably simple - the following is an outline which will accomplish this. As you might expect, there are lots of places where you will need to do some work, but this would be the outline from which to start.

HTH

Tony

Please Login or Register  to view this content.

[madi1004]

Hello,

Thank you for your quick reply.

I am new in VBA excel .... can you give me more details about this vba code .

Thanks for your help.

[tfurnivall]

So let's start by translating what you originally sent into a slightly more precise format:

I need your help in extracting data (part no - column c) from the file "fbwls" (without duplicates) into the file "all" sheet Desired Results. Also, additional part no that are in the same file "all" Sheet "Transit" should also be added to the sheet "Desired results" under the parts retrieved from the file "FBWLS".


At the end, there should be no duplicates part numbers in the sheet "Desired results".

I have inputted the part no manually into the "Desired Results" sheet, as I want to have them at the end.

So we seem to have a file called, memorably, "FBWLS". If we are going to use a file, then we need to Open it, Read it and then, when we have read everything form the file, we need to Close it.

Please Login or Register  to view this content.

The Open statement takes three arguments - the file name ("FWBLS"), the open-mode (input) and the file number (#1). To learn more about opening a file, use VBA help. Just copy that one line of code into the VBA editor, click on the word Open, and press F1.

Now we have to read the entries in the file:

Please Login or Register  to view this content.

Now, if you try this, you may well discover that the open statement fails (unless you are in the same directory as "FWBLS". This is where programming gets kind of fun. We [B][I]delay[B][I] naming the file, until we know what the name, and the path, and the extension and all of that low-level stuff, will be. So we create a variable called FileName (which will be a string), and use that instead:

Please Login or Register  to view this content.

Now, we really haven't changed that uch, except that we have separated the naming of the file from the opening of the file. That way, if you want to pick a different, but equally memorable name (say, "XYZZY"), you only have to change one line of code, and a pretty simple line at that:

Please Login or Register  to view this content.

Around about now we realize that you've buried a little piece of information about this file - it's really a spreadsheet (part no - column c). Which means that we now need to revisit what you originally sent, and change it!

I need your help in extracting data (part no - column c) from the {file} SPREADSHEET "fbwls.XLS" (without duplicates) into the file "all" sheet Desired Results. Also, additional part no that are in the same file "all" Sheet "Transit" should also be added to the sheet "Desired results" under the parts retrieved from the file "FBWLS".


At the end, there should be no duplicates part numbers in the sheet "Desired results".

I have inputted the part no manually into the "Desired Results" sheet, as I want to have them at the end.

You see what I did? I changed the word FILE to SPREADSHEET, and added the likeliest extension to the file name. To be really specific, we also want to know what worksheet inside the workbook contains your data.

This also changes how we will want to approach the whole business of reading the FBWLS spreadsheet.

OK - that's a whole load of stuff, just to determine that what you asked us for was pretty incomplete in just one very small way (the name of the 'file', which lacked an extension, and also a path to it).

Programming is like that - you'll seem to take 2 steps forward, and then 100 steps backwards. What I'd suggest is that you take a bit of time and rewrite you original request, trying to be as specific as possible about WHERE all of the data is. You might also want to consider if you KNOW that the FBWLS spreadsheet has no duplicates, or whether there's a possibility that there might be duplicates. This sort of scrutiny of every potential piece of data is important given that you require the finished result to have no duplicates (that's ZERO, not just 'an acceptably small number').

The definition of good code is that it matches the requirement specifications exactly - the definition of really good code is that it matches what the requirement specifications should have been.

Take a few minutes to do that rewriting, and let us have the new and improved requirements!

HTH

Tony