Export text into text file

[ExcelAteMyHomework]

Hi:

I have data in columns A and B. In column B the cells display text. I'd like to export each individual cell of text into it's own text file and save it as the name of that text.

Is that possible?

Workbook attached. random_text.xlsx

Thanks

[Domenic]

Assuming that the sheet containing the data is the active sheet, try...

Please Login or Register  to view this content.

Note that a date/time stamp will be added to the file name, if it already exists. By the way, did you really mean that the file should be named according to the value in Column B? Or should it be named according to the value in Column A?

[ExcelAteMyHomework]

I know just enough VBA to go in and change where and how I want the file name to save so I actually meant for it to save exactly how you made it. Adding the date and time stamp was fantastic. Domenic thank you very much.

[ExcelAteMyHomework]

I did notice that it only saved 8 files. It seems that after it saves the duplicate file name it skips the other duplicates and goes to the next non-duplicate cell. Any idea how to fix that?

[ExcelAteMyHomework]

And as it turns out I dont know quite enough VBA to change this code to save as the ID field lol. Don't suppose you would mind showing me how to do that as well? That way I can compare the two and learn!

[ExcelAteMyHomework]

I am getting a debug error here:

Please Login or Register  to view this content.

[Domenic]

I did notice that it only saved 8 files. It seems that after it saves the duplicate file name it skips the other duplicates and goes to the next non-duplicate cell. Any idea how to fix that?

Actually, I think what's happening is that because it takes the code a split second to run the date/time stamp that is added to each duplicate file is the same. As such, the existing file is overwritten. Try the following instead...

Please Login or Register  to view this content.

Here, for duplicate files, a date and file number (starting with 1) is added.

[Domenic]

And as it turns out I dont know quite enough VBA to change this code to save as the ID field lol. Don't suppose you would mind showing me how to do that as well? That way I can compare the two and learn!

Try replacing...

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[Domenic]

I am getting a debug error here:

Please Login or Register  to view this content.

Are you still getting an error?

[ExcelAteMyHomework]

Still getting the error and the debug takes me to that line of code. However, when I go check my folder all of the text did export. Very strange.

The error says:
Runime error '52':
Bad file name or

EDIT: It exported 342 of 400. Then I guess errored out.

[ExcelAteMyHomework]

Here is the actual file I am working with.

Thanks master.xlsm

[ExcelAteMyHomework]

I figured it out! One of the cells had "" around the text. VBA didn't like that. I removed the quotes and the macro ran fine.

Thanks Domenic!

[Domenic]

That's great. I'm glad you were able to figure it out.

Cheers!

[ExcelAteMyHomework]

Hi guys:

Trying to incorporate this macro into an Excel version on MacBook Pro but I am getting the following error message:

Run-time error '68':
Device Unable

The attached screenshot is what the debug shows me.

Any thoughts/suggestions??

Thanks!

EDIT: My screenshot didn't upload correctly. Code that the debug goes to is below:

Please Login or Register  to view this content.

Any help is appreciated. Trying to get this to work on my gf's MacBook Pro.

Thanks

[ExcelAteMyHomework]

Any help is greatly appreciated.

[Domenic]

If the variable strFullName has been assigned a valid path and filename, the line of code should not cause an error. What string value has the variable been assigned when you get the error?

[ExcelAteMyHomework]

If the variable strFullName has been assigned a valid path and filename, the line of code should not cause an error. What string value has the variable been assigned when you get the error?

Please Login or Register  to view this content.

[Domenic]

I have an older Mac version of Excel, which uses a colon ( : ) as a path separator. If the newer Mac versions of Excel use a forward slash ( / ), try replacing...

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Otherwise, try...

Please Login or Register  to view this content.

Change the username, accordingly.

[ExcelAteMyHomework]

It seems like the colon is the correct separator as I started getting an Error "78" instead of "68". However when I changed the filepath using the colon as a separator I still received error "68". Debugger keeps taking me to this line of code:

Please Login or Register  to view this content.

Not sure what to do... tried everything I could think of.

[ExcelAteMyHomework]

At this point I don't even mind if it just saves each file to the desktop...

Is the .txt file type valid on Mac?

[Domenic]

It seems like the colon is the correct separator as I started getting an Error "78" instead of "68". However when I changed the filepath using the colon as a separator I still received error "68". Debugger keeps taking me to this line of code:

Please Login or Register  to view this content.

Not sure what to do... tried everything I could think of.

To confirm the path separator, try entering the following code in the Immediate Window (Command + G)...

Please Login or Register  to view this content.

Is the .txt file type valid on Mac?

Yes it is...

Can you post the exact code that you're using? I'm just about to log off for the day, so I probably won't get a chance to look at it until tomorrow morning. In the meantime, maybe some else will be able to help you.

[ExcelAteMyHomework]

Thanks for helping Domenic.

The current code is below. This is for a MacBook Pro. Excel for Mac 2011 on Mac OS X Version 10.6.8

I've tried every file path I could. Using colon separators, backslash, etc. When I open the "browser" I see Mac HD. I tried using that. Also tried using Macintosh HD: etc.

Please Login or Register  to view this content.

insert user in <UserName>

[Domenic]

I've tested the code in my Mac version of Excel (Excel X) and it works fine. Actually, my Mac version of Excel doesn't support the Replace function. I'm assuming that your newer Mac version of Excel supports it, otherwise you would have received a compile error.

Please Login or Register  to view this content.

If this still does not help, run the following in the Immediate Window and tell me what they return (you can, of course, change the username to protect your privacy)...

Please Login or Register  to view this content.

Also, if you still get the error, what does the variable strFullName contain when the error occurs? I know I asked you this before, but I only noticed now that when you replied you gave me the contents of strDestFolder instead.

[ExcelAteMyHomework]

I'm just going to run the macro on my PC. She can send me the excel file and I'll just dump it into my macro workbook. Question though... If the text string in column B is long and has multiple breaks (i.e. ,) will that mess with the code? I'm now trying to run the macro in a new file with significantly longer text strings and I am getting a Runtime Error "53" File not Found.

Bizarre... same line of code...

Please Login or Register  to view this content.

Working in PC again. Not a Mac fan anyway...

---------- Post added at 03:38 PM ---------- Previous post was at 02:51 PM ----------

I'm just going to run the macro on my PC. She can send me the excel file and I'll just dump it into my macro workbook. Question though... If the text string in column B is long and has multiple breaks (i.e. ,) will that mess with the code? I'm now trying to run the macro in a new file with significantly longer text strings and I am getting a Runtime Error "53" File not Found.

Bizarre... same line of code...

Please Login or Register  to view this content.

Working in PC again. Not a Mac fan anyway...

I think I figured out my own question with a quick Google search and a little formatting. Windows doesn't like file names over a certain character length. When I re-ran the macro on a single cell with fewer characters (regardless of the characters), it worked fine.

I'm going to try and figure out how to save each text string as a number. No specific identifier just 1, 2, 3, 4, etc. As long as the text string is within the body of the .txt file it works for my purpose.

---------- Post added at 03:51 PM ---------- Previous post was at 03:38 PM ----------

Domenic, just wanted to say thank you again for all your help and patience with me and this project. I have learned a little bit along the way.

[Domenic]

Yeah, the file/path name cannot exceed 218 characters. If that's the case, how about extracting just the first 20 or so characters from the description in Column B? If so, you would replace...

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Would that work for you?

[ExcelAteMyHomework]

Would that work for you?

That's actually very cool! Thanks for the tip. What I did is used the code you showed me earlier in this thread to point to column A. So each file name was the unique identifier in column A.

I'm going to play around with it some more and use this tip you just posted.

Thanks Domenic!