Extract data from file into cell?

[mikemeadeuk]

Hi Guys,

I have a little problem. I have a bunch of data in my spreadsheet, one of the column has an ID and then I have a bunch of files that are named the same as the ID. However I want to insert the data from the file into the excel cell. Any ideas how I can possible do that?

For example, in my column in excel I have 12345. I then have a text file called 12345.txt with text in it. I want to insert this text next to the cell in excel.

Any help would be greatly appreciated!

Thanks

Mike

[mrice]

Try this macro. Select the cells containing the file name. You will need to set the MyPath variable to match the location fo your files.

Please Login or Register  to view this content.

I've assumed that your text files may contain more than one line.

[mikemeadeuk]

Hi Mrice,

How do I use that macro, I have created a new macro using a shortcut key. But nothing happens when i use the shortcut. I have changed the directory. Not sure what to do?

Thanks

Mike

[mikemeadeuk]

Just realised that the top result doesn't actually have a file for that ID, so its not inserting anything. However the second line does but returns an error of "cannot execute code in break mode"

Thanks

[mikemeadeuk]

update: deleted the macro and started again and it works perfectly when selecting one line. However when selecting multiple cells it returns the error:

run time error 52
bad file name or number

Any ideas?

Thanks

[mrice]

Just check that you are doing the following..

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.


If all fails, can you post some examples of the files that you are trying to read in so that I can test?