How can I convert an Excel 2007 cell into a filename?

I have an Excel spreadsheet with 4000 filenames in it (all in individual cells) that we extracted in a report from our legacy DM system.

Please note the cell contents are text and not actual embedded files. They look roughly something like this:

-DVComparison_AMR-#madeupref-v1-Bid_-_SACE,_replace_with_gibberish,_replace_with_gibberish_-_big_acme_project_(peas,_July_2009-AMR-#madeupref-v2-Bid
-Letter to Made up Person re: Senior term facilities and multicurrency revolving facility agreement dated on 3 December 2012 entered into by, Made up Company S.A. as borrower, Made up Agent, Ltd as agent, et al.
-Letter to As requested, please find attached a hard-copy of the closing HAllaghuah for the Toy Story transaction that closed on December 30, 2010.
-Kramer_V_Kramer -- Cover Letter to FisherPrice at Acme Toy Company to accompany Declarations of Trust and Letters of Instruction for LOTS OF NUMBERS 345354 8034i5345 123424

I have to test if the files can be manually migrated into our new DM system without error.

The thing that worries me is the lenght / format of some of the filenames so I came up with the idea to migrate them into the test system to confirm if there will be a problem or not.
(I am just worried about the filenames at this point as we are confident from other testing that the file contents will be migrated in ok)

It would take me a LONG time to convert each of the cells into a file for testing so I wondered if there was a macro way of doing it.

Something that would take the text in the cell "D2" and turn it into a file called "D2.txt" in the location "C:\Mytestdocs" (or .doc or .anything - the extension is not importaint as long as it is a supported MS format as this has all been tested) I just need to get the 4000 cells converted into files with the title the same as the file contents so I can point our migration tool at it and see where any potential issues lie with the existing filenames.

Is this possible and how would I go about doing it?

What would be the content of the files?

Hi and welcome to the forum

Im not exactly sure what you are trying to do, but try these...

you could use an & to combine things (D2 contains D2.txt)
="C:\Mytestdocs\"&D2

or you could try playing around with indirect()

If you actually want to create files from the names in column D you could try something like this.

Only creates empty files though.

Please Login or Register  to view this content.

Can you clarify exactly what you want to do?

The code I posted will create empty files using column D for the filenames.

It could be easily adapted to add content and/or not take the filenames from column D.

That looks like what I am trying to do but it is throwing an error Runtime 53, file not found
(I don't need the files to have content, the file names are the thing I am testing)

I pasted the code into notepad, edited the location I wanted the new files saved to and it saved as a .bas file

I opened the xls file with the 4000 file names in it

I opened up the VB editor

I imported in the .bas file

I ran the file (error 53)

So I ran it in debug and it is telling me the issue is with the line
'Open strPath & "\" & strFileName For Output As #1'

(the file was not created)

Do you know what the issue might be?

How exactly did you change the code I posted?

Does it create any of the files?

Do any of the cells have obviously invalid filenames, or blanks?