Open another File and Capture some Columns

[Andrew.Trevayne]

Hi,
I would like to seek help on some Macro coding.
I need a Macro that will open another Excel File (for this example i created a dummy file called "dummy.xls").
However this Excel file contains more data and some columns I dont really need.
For this example I only need columns A,G,J,K,L,M,N,O,P,Q,R

Can you guys help me create a Macro where in it will only pull data's from the mentioned column?
Kindly advise, really need this one. As always

I have enclosed two documents.
One is my Dummy data (dummy.xls)
The other one is my MacroLab (where data will be placed, i added the data as how it should look like when data is collected)

Hope you guys can help.
Thank you very much.

[Andrew.Trevayne]

Hope you guys can help

[jaslake]

Hi Andrew.Trevayne

Do you want to "pick" a source file to be opened or should it be hard coded?

Do you want to "pick" a target file or should it be hard coded; does it need to be created?

Do (or will) both the source file and the target file exist in the same directory?

John

[Andrew.Trevayne]

Hi John,
Thank you for your reply. If I could get the chance to pick a source file would be better.
And maybe I can dump all source file on C:\Temp and choose from there.
All my raw data will have the same structure. But I only need those in column A,G,J,K,L,M,N,O,P,Q,R
The target file should be the same as where I will run my Macro. In this case the Macrolab.xls
I hope you could help me. Thank you in advance.

[jaslake]

Hi Andrew

I should think one of us can help. It's late here but I'll check back in the AM. If your issue isn't resolved, I'll look into it.

John

[Andrew.Trevayne]

Hi John,
Thank you very much. I'm looking forward to your help.

[jaslake]

Hi Andrew

The attached file includes this code

Please Login or Register  to view this content.

You'll need to change the line that's underlined to the path and folder name of your raw data.

Let me know of issues.

John

[Andrew.Trevayne]

Wow! John this is perfect. You're a genenius.
Thank you so much for this.
Just wanted to add and I hope Im not asking much.
During the import, is it possible that the contents on columns L to Z wont be cleared?
Cause I have some formulas on those columns and they get cleared during transfer.
About the formatting, Is there away to retain the original format of Macrolab Excel sheet after the data has been copied?
Thanks again John for being helpful. Appreciate it.

-Andr

[jaslake]

Hi Andrew

Didn't see any data (or formulas) in

columns L to Z

If you'll attach a file that has "real" data, I'll look at it.

John

[Andrew.Trevayne]

Hi again John,
I actually have formulas in there but I didn't put anymore.
The formula is actually going to calculate some columns on the imported data. ^^
I have attached the Macrolab so you will see the formula as well.
Thanks again for all your work.

-Andr

[jaslake]

Hi Andrew

I'm not certain what you're after. See if this works for you.

Please Login or Register  to view this content.

John

[Andrew.Trevayne]

Hi John,
Sorry for the reply. Been a bit busy on the Christmas Holidays.
Anyways I tried using the modified Macro and I'm surprised to see that you embedded the formula in there.
This is enhancement for me and Thanks so much.

Any ideas on the formatting on this? What can we add so the target worksheet does not copy the formatting from the source?
Maybe remove those background fill and set the headers(A4 to L4) to bold?

Thanks so much for all your help, hope you enjoy this Holiday season.

Andr

[jaslake]

Hi Andrew

I'll be happy to accommodate this if I can

Any ideas on the formatting on this? What can we add so the target worksheet does not copy the formatting from the source?
Maybe remove those background fill and set the headers(A4 to L4) to bold?

I added the background fill because that's what your sample file looked like. As it is, I now have no idea what you want...SHOW ME. Attach a file that

has "real" data

(before and after) so I can see what it is you're are looking for. Attach a source file (with it's actual formatting). Attach a final file (with it's formatting AND formulas). I'm fairly confident we can duplicate what you want. I simply don't know for sure what that is.

John

[Vincent.Eymard]

Hi John,
Thanks so much for your swift response.
I have attached a live source data. Some of the fields in it contains "Yellow" fill.
If this formatting will be carried over on my target sheet, it would not look very nice hehe!
Actually this are just cosmetics issues.
But if can be done would be great.


I have attached Documents, My Source (Source.Xls), Our Worklab (MacroLab.xls), Desired Display (Desired Output.xls)
.
Currently our Macrolab is working perfectly as it should. If we could somehow format it and appear as my Desired Display will be fantastic.
Sorry for giving you too much trouble on this. You helped me more than enough.

[jaslake]

Hi Andrew

I'll look at it tonight. May get back to you soon but probably in the AM.

John

PS: To whom am I talking...Andrew or Vincent????

[Vincent.Eymard]

Hi Andrew

I'll look at it tonight. May get back to you soon but probably in the AM.

John

PS: To whom am I talking...Andrew or Vincent????

Hi John,
Sorry I am working with my colleague on this. We are both learning Macro and I was also looking into this. Hope you could help us.
Many thanks again.

[jaslake]

Hi Vincent/Andrew

Try this code

Please Login or Register  to view this content.

I HOPE this is a work project and not a School assignment!!! If it's not, let me know of issues.

John

[Vincent.Eymard]

Hey John,
Thank you so much from Both of us. You have been very accomodating.
This is just fantastic.

Enjoy the Holidays.

Cheers,
Vincent/Andrew

[Andrew.Trevayne]

Thank you so much John. You're great!

[jaslake]

Vincent/Andrew

Merry Christmas and a Healthy, Happy New Year.

John