Getting Type Mismatch, Runtime Error 13 With This Macro

[HowdeeDoodee]

This macro works on some text files but not others.

When the Type Mismatch, Runtime Error 13 message appears, the following line is highlighted

x = Application.Transpose(Split(txt, vbCrLf))

Please Login or Register  to view this content.

[abousetta]

Does this occur in files with only one line?

Also you can print the results to the sheet all at once instead of row by row.

[HowdeeDoodee]

Thank you for the comment. I am not a programmer, just a puzzled user.

I do not have any text files of one sentence. The text files are books from Gutenberg Project.

The macro above came from this thread: http://www.excelforum.com/excel-prog...45#post2999245

The macro worked fine for a day or two, but then the macro got quirky. Now I am getting this error message referred to in this thread title. When I first started using the macro, the contents of the opened file would print directly to the right of the file name in col A. Now, the file contents drop down below the file list and print at the bottom of the file file list in col A, going across the sheet from left to right.

I need each of the files listed in col A opened, then a sentence from each file put into a row, one sentence per cell, cell by cell going to the right across the sheet. After I get the sentences in cells going across, then I combine the sentences into one cell in a column. After the sentences are in a column, I extract the title from the txt file. The text files are from Gutenberg Project.

Thank you for the comments. Your help would be appreciated.

[HowdeeDoodee]

Could this be a work around of some type? Instead of breaking the text file up into pieces and putting each sentence into a cell as is now the case, can the lines imported from the text file all go into one cell? Would this correct the type mismatch issue?

[abousetta]

Could you tell me in words what the macro is supposed to do and I will see what I can do? I think I understand the concept but since you got it from another thread, it might not fit your situation perfectly.

[HowdeeDoodee]

At the top of this ebook txt file from Gutenberg Project the macro extracted the following words shown here in italics. The macro was set to extract 70 lines from the top of the text file. The 70 lines from the file named pg10129.txt are as follows:

The Project Gutenberg EBook of Excellent Women, by Various This eBook is for the use of anyone anywhere at no cost and with almost no restrictions whatsoever. You may copy it, give it away or re-use it under the terms of the Project Gutenberg License included with this eBook or online at www.gutenberg.net Title: Excellent Women Author: Various Release Date: November 19, 2003 [EBook #10129] Language: English
*** START OF THIS PROJECT GUTENBERG EBOOK EXCELLENT WOMEN *** Produced by Joel Erickson, Charlie Kirschner and the Online Distributed Proofreading Team. [Illustration: A STREET IN CAIRO. (See _Mary Louisa Whately_.)] EXCELLENT WOMEN. BY VARIOUS WRITERS. CONTENTS. ELIZABETH FRY. BY JAMES MACAULAY, M.A., M.D. SELINA, COUNTESS OF HUNTINGDON. BY REV. R. LOVETT, M.A. RACHEL, LADY RUSSELL. BY JAMES MACAULAY, M.A., M.D. FRANCES RIDLEY HAVERGAL. BY REV. J.P. HOBSON, M.A. HANNAH MORE. BY HENRY JOHNSON. SUSANNA WESLEY. BY REV. J. CUNNINGHAM, M.A. MRS. HEMANS. BY REV. S.F. HARRIS, M.A., B.C.L. MADAME GUYON. BY WILLIAM NICHOLS. ANN JUDSON. BY FRED. A. MCKENZIE. MARY LOUISA WHATELY. BY REV. W.R. BOWMAN. AGNES JONES. BY ELLEN L. COURTENAY. ELIZABETH, DUCHESS OF GORDON. BY REV. S.F. HARRIS, M.A., B.C.L. ELIZABETH FRY. I. BIRTH AND EARLY YEARS. Elizabeth Fry was born in Norwich, on the 21st of June, 1780. She was the third daughter of John Gurney, of Earlham, Norfolk, and Catherine Bell, daughter of Daniel Bell, merchant in London. Mrs. Bell was a descendant of the ancient family of the Barclays of Ury in Kincardineshire, and granddaughter of Robert Barclay, the well-known apologist of the Quakers. John Gurney of Earlham, born in 1749, was educated in the principles of the Society of Friends, but as he advanced in life, and associated with


Thank you for your reply.

[abousetta]

This should extract the first 70 lines of the all the text files in a specific folder placing them all in column A.

A few points of concern:

* Some of these lines are just paragraph breaks (ie. empty)
* The text still needs to be cleaned to get rid of non-printing characters and characters not useful within Excel
* what next... this seems to be part of a project but not the end result.

Please Login or Register  to view this content.

[HowdeeDoodee]

Thank you. This Sub routine is working but the file contents are printing down the first column A instead printing across the sheet from col A going 70 columns to the right. As I noted above "I need...a sentence from each file put into a row, one sentence per cell, cell by cell going to the right across the sheet." The macro in the O.P. did print the sentences from each file in cells going across the sheet with each file having its own row. Each file was to have its own row rather than having all sentences from all files print in col A. Is there any way to restore the action of printing the content of each file on its own row?

Thank you again for the reply.

[abousetta]

I have updated the code above but don't have a chance to test at the moment. The lines that were amended are now in red.

abousetta

[HowdeeDoodee]

Thank you, but all the rows and columns contain identical content which means something is not working. All the rows should be different because each row represents a different file.

[abousetta]

I had to clear the variable between each run. I have added it above in bold green:

Please Login or Register  to view this content.

I tested with two different files and seems to be working now.

abousetta

[HowdeeDoodee]

Not working for me. When I click on "Run" in Personal.xls the macro keeps taking me back to the \Backup directory. The macro never starts running but keeps wanting me to designate a directory which I do designate, but then the cycle starts all over again. How do you get the macro to run in this instance? When the macro was first made, the directory was specified in the code. When I need to move to another directory, I changed the directory in the macro code.

[abousetta]

If you want to hard code the folder (same folder every time) then just replace the folder picker code:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

[HowdeeDoodee]

Thank for the help.

[abousetta]

Does it give you the results you desire now? If so then please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

If not then it would be great if you could upload a text file as an example and what it would like after a manual import. That way I can compare the results from the macro.

Good luck.

abousetta

[HowdeeDoodee]

Well, the problem isn't solved but I can bear with it. I need a little more time. Thanks.

[abousetta]

OK, let us know if we can be of assistance.

Good luck.

abousetta