+ Reply to Thread
Results 1 to 18 of 18

Remove return carriage from text file before import

  1. #1
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Post Remove return carriage from text file before import

    Hi everyone,

    I have put together a macro that will successfully loop through text files in a given folder and import all the data within each file into an excel sheet. The data comes from MS Word forms. The data from the forms are then saved comma-deliniated text files.

    The problem I face is that if anyone uses a return carriage when inputting their data in the Word form (before transformation into the text file) then during the import into excel at each return carriage a new row is begun. Therefore as a precautionary mesaure I am forced to open each text file, search for any return carriages and replace them with a space so that each text file will only occupy one row.

    Can anyone assist me in resolving this issue in vba? I have thought of two possible options: (1) open each text file, perform the replace and then save it again replacing the old file; (2) somehow indicating to import to ignore all return carriages. I don't know which is more feasible (or is a better approach). Also if anyone has any ideas on how to make the code more efficient I would happy to hear their thoughts.

    Thanks in advance.

    abousetta
    Attached Files Attached Files
    Last edited by abousetta; 06-20-2010 at 06:17 PM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    This is all you need:
    Please Login or Register  to view this content.
    Last edited by snb; 06-16-2010 at 10:49 AM.

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Thanks snb,

    This has been very helpful. One question though. Since you did not use the query table function there are no quotation marks around each entry. Is there a way to combine these two rows of code:

    Please Login or Register  to view this content.
    Thanks.

    abousetta

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    Do you want quotation marks in every cell ??

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Post Re: Remove return carriage from text file before import

    The quotations currently wrap all the data. So when I used the original data query, it recognized that they quotation marks are not user inputs but rather generated by Word and so removed them. I have attached two dummy files (one for the word form and one for the generated text file). So what I want to do is remove the quotations duirng the import into excel.

    Thanks.

    abousetta
    Attached Files Attached Files

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    As requested.
    If you understand the code, the next time you will be able to do this yourself. Have a look in the VBEditor's help.(replace, split)
    Please Login or Register  to view this content.

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Thanks snb. This should do it.

    abousetta

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Hi snb,

    Sorry, but I hit two snags that I can't seem to resolve. First is if the person filling the form used a comma then vba code reads it as a delimiter and goes the next column when in fact it should stay in the same column. The built-in QueryTables uses
    Please Login or Register  to view this content.
    to prevent this from happening.

    Second problem is that numbers are imported as text.

    Could you please help me modify your original code to resolve these issues?

    Thanks in advance.

    abousetta

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    Second problem is that numbers are imported as text
    You can change that easily with (if e.g. column E should contain numbers)

    columns(5).value=columns(5).value

  10. #10
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Thanks, but I don't know in advance of creating each word form which columns will get text and which will be numbers. In a recent form I made, there were over 2000 text boxes (and therefore columns in excel) used and so I hope that there is a uniform way of doing it instead of per column.

    Thanks in advance.

    abousetta

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    In that case you won't be able to use import textfile either.
    But there's a simple solution:

    usedrange.value=usedrange.value

    By the way '2000 Textboxes'; I can't imagine a sensible application.

    This you could have figured out ourself
    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Hi snb,

    Sorry the issue is still there. Let me clarify. If I wrote in the Word Form:

    John Smith, Jr

    it gets put in the text file as:

    "John Smith, Jr"

    The comma between Smith and Jr is an supposed to be a delimiter but excel will assume that it is. In the QueryTable function this doesn't happen because of the line of code:

    .TextFileTextQualifier = xlTextQualifierDoubleQuote

    I have spend a lot of time searching for an answer to resolve this and I just can't make it work but with no luck. I have even tried to backtrack and see if I could add the split/ replace function to the querytable but I cannot find a way to do this.

    Thanks again for your time and efforts in resolving this issue. It is highy appreciated.

    abousetta

    P.S. The forms are long and complex because they are used to document a large amount of data. They are a pain to make and imagine if this had to be transferred by hand from a paper form to an excel sheet.

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    That can't be a problem.
    Please Login or Register  to view this content.

  14. #14
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Thanks. This is much better. Sorry for all the trouble. I am just trying to incorporate your earlier suggestion on converting the cells with text to numbers.

    abousetta

  15. #15
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Hi snb,

    This has been extremely helpful. I have tweeked it further to convert the cells with numbers stored as text into numbers as you advised and tested it in several different situations (different folders). I have one problem that occurs with some folders and not others and I don't know what's causing it.

    I get an error message stating "Run-time error '1004' Application-defined or object-defined error" at this part of the code:

    Please Login or Register  to view this content.
    When I add On Error Resume Next in the line before it the error does not appear, but also nothing seems to happen. But when I put the macro in a new excel workbook everything worked. Could you please let me know how to resolve this?

    Thanks again for all your help.

    abousetta

  16. #16
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Remove return carriage from text file before import

    if ubound(sq)=-1 then this code won't work.

  17. #17
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Hi snb,

    Sorry I lost you and have to admit I don't have much experience with arrays. How can an empty excel sheet have ubound(sq)=-1? Wouldn't the upper bound of the array always be possitive?

    abousetta

  18. #18
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove return carriage from text file before import

    Hi snb,

    Well I think I figured out the problem. When the macro is in an .xls file then the error occurs, but when it is in an .xlsm file then it works fine. So I am work with this.

    Thanks.

    abousetta

+ 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