+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    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

  2. #2
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

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

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

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    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?
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    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

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    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.

  6. #6
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    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.

  7. #7
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    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.

  8. #8
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    test_rapidasia.zip

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

    Andrew

  9. #9
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    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.

  10. #10
    Registered User
    Join Date
    01-30-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1