extracting data

[skillfilluk]

Hi, I have over 800 members details in an excel file with name, address etc is there a way to extract each members details out and place them into a folder? so at the end of it I will have 800 folders with each members details in each folder. I'm using Excel 2003, Thank you for your time.

[foxguy]

Hi skillfilluk;

How are the members details arranged in Excel? Is all the data for 1 member in 1 row? Or maybe 1 sheet? Or how?

Excel can create a files in the folder, but what kind of files? Text files? Excel files with one sheet? Or What?

In all likely hood the answer is yes, but I would want to know more details than what you have given.

[skillfilluk]

All the data for 1 member in on 1 row, and I want them extracted to text files in each folder. Thanks for your help.

[foxguy]

Do you know how to write a macro?

[skillfilluk]

No sorry never used macro, but I guess I can give it a go?

[foxguy]

Well, you have picked a rather ambitious task to start writing macros on.
I'll write a basic one for you, but you will have to provide a sample workbook. Copy your workbook with the names, but take out any sensitive data. I just need to see how everything is arranged. Then upload it to the forum.

I'll look at it, and write a macro to get you started (maybe it will be everything you want). It's late for me, so I'll be back in a few hours, to look at it.

[skillfilluk]

Workbook example attached, would it be possible so when data is extracted the folders created are named with the members name (first and last name) thanks again.

[foxguy]

Hi skillfilluk;

First off, I'm not going to do the whole thing for you. At least not unless you want to pay me for my time. You will be doing a lot of the work. I'm going to be primarily teaching you how to do it yourself. I'm doing this for free because I've never done it before, and I'm interested in the challenge.

If that's OK with you, then let's get started.

Step 1) Open the VBE (Visual Basic Editor)
in your workbook, click Alt+F11. That opens the editor where you will write macros. Make sure that the window that opens has the name of your workbook at the top. It should say something like this "MyWorkbook.xls - Sheet1 (Code)"

Click Ctrl+R to open the Project Explorer
the highlighted line shows you where the 1st window is in relation to your whole workbook. Above the highlighted line you will see your workbook name like this "VBAProject(myWorkbook.xls)"

In the menus click Insert->Module.
This will create another module in your project named "Module 1". This is where you will write your macro.

Now cut & paste every thing that is in this code window into Module 1. Then put your cursor inside the macro (somewhere between the line "Sub myMacro()" and "End Sub") and click F8 (that will start the macro). Then just read what is there, it should be easier to read and I will be able to demonstrate things that I can't do here. This is just an extremely simple first step, just to see if either one of us is wasting his/her time.

Please Login or Register  to view this content.