Error Message usin code from Thread: Import Multiple Text Files into Excel - for beginners

[ATGM]

Hi - I am a total beginner so was very interested to see a solution to my requirement, including "for beginner" instructions in the thread at
http://www.excelforum.com/excel-prog...html?p=3107472 .

Code provided there is:

Please Login or Register  to view this content.

I copied the listing and pasted it in, every time I try to run it I get

Run-time error '1004':
Application-defined or object-defined error

in the debug screen a block of text is highlit yellow, it is not liking the text between the **'s:

If TextIndex > 0 Then **Range("C1").Resize(TextIndex).Value = Application.Transpose(arrText)**
i.e. flagged yellow in this section:

Please Login or Register  to view this content.

I am running Excel 2003, if that makes a difference.

My fingers are crossed that someone can point me in the right direction to fix this.

Andrew

[ATGM]

D'oh, please forgive "usin" in place of the more standard and correct "using" in the title.

[tigeravatar]

Andrew,

Welcome to the forum!
I am unable to reproduce that error. I tried with a directory that contained no text files, a single text file, and multiple text files and each time the code ran as intended.
Can you provide a sample text file and sample workbook that is experiencing the issue?
Also, is your workbook/worksheet protected?

[ATGM]

Thank you for your quick reply...and apologies for my slow one. I think I have found a thing or two that may explain my problem. The files I would like to import as a single column per file are actually comma separated. There are a LOT of entries for each column and, on re-reading the original request and trying some simple text files, the macro will import each file into a single cell in the spreadsheet. I am probably trying to overfill the cells. My excel sheet with the macro and desired files are available at this location:
<https://dl.dropbox.com/u/2489377/test_rapidasia.zip>

Please forgive my misunderstanding of the original intention of this macro. If it is possible to extend the macro to do the following "wish list" please let me know how to go about it:

Import a number of comma delimited CSV or TXT files while:
- placing the title of the original file in the top row
- putting each entry in a single row, stacking up in a column under the file name
- starting a new column for the next file

Thank you for your thoughts,

Andrew

[tigeravatar]

Andrew,

Awhile ago I wrote this code for a task very similar to this. The First code calls the ImportDelimitedTestFiles code, which will prompt you to select one (or more) .txt and/or .csv files. Each file will be imported into its own worksheet and the sheet name will be renamed to match the file name. Is that something you can work with?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[ATGM]

Hi - is the right process to enter these as module 1 and module 2, and the public sub does not show up in the macros list? If so, I get the Run-time error '1004' when I try to run the macro(s) on one or more of my test files except for the very short "simple_text_file_works.x" where x is either txt or csv. It also imports into long rows rather than columns, which I think may run out of cells sooner. Do you see this error when importing (e.g.) 1181FG8Q.MIC.csv from my zipped folder?

I had found some code, earlier, which I (brutalized) tweaked to have similar functionality; the importing into a single worksheet with one column per file would be really helpful for my application, since I will otherwise end up manually merging the worksheets together by copying and pasting...and probably transposing too. I did like the easy folder navigation and file selection in this code.

[tigeravatar]

Unfortunately, I am at work and dropbox is blocked for me so I am unable to test using the data you have there. Can you upload a zip file to this thread? To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

[ATGM]

test_rapidasia.zip

Hope this works. I wasn't in the advanced mode before so did not see how to attach files.

Andrew

[tigeravatar]

Andrew,

The reason the code I provided was failing was because the text files didn't actually have any carriage returns, they were just long strings of text. I have adjusted the code into a single sub to fit your situation and tested it using your provided text files. The code now puts all of the data in a single column with the top cell being the name of the text file. Give it a try:

Please Login or Register  to view this content.

[ATGM]

That is very, VERY cool to see in operation. Thank you very much indeed. I have rated this thread as excellent but really that is because I do not see a forum mechanism for rating your help, where the 5 star rating belongs.