Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing/exporting text files in Excel 2000 (Excel/Word 2000)

    As part of a small office application, I am attempting to edit a few text files in VBA from Excel 2000. The text files are actually records of data, grouped in variable numbers of records, each group separated by a blank line/record. I read them into Excel because it's so easy to traverse each record, and also to remove blocks of records that make up individual groups.

    Unfortunately, I'm having a lot of problems with double quotes. Some of the individual records contain double quotes, eg {IF CMS = "Y"}, and although I can get these records into Excel like that, when I come to write the file out again, Excel places a pair of double quotes around the whole record, and doubles up the double quotes in the middle of the record! I can find no way to stop Excel doing this - naturally enough, the editted file doesn't work properly.

    I've tried to use Word to do the editing, but it is so dreadfully slow. Because I need to identify the groups of records (paragraphs in Word), I have to plod through the document paragraph by paragraph, and this seems to take forever, whereas Excel is very quick - just writes out incorrectly. I'd use ranges, but cannot think of any way to identify each group of records as a range.

    Can anyone help here? Preferably to help me stop Excel adding the double quotes when writing the file back (and it is just at that point - up to then the records look fine), but if that can't be done, can Word be speeded up? My VBA is reasonable, but I'm more at home in Excel; VBA in Word is more problematic for me. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing/exporting text files in Excel 2000 (Excel/Word 2000)

    You can use the old-fashioned BASIC file statements. Assuming that you have opened a text file and all data are in column A:

    Dim f As Integer
    Dim lngRow As Long
    f = FreeFile
    Open "C:ExcelTest.txt" For Output As #f
    For lngRow = 1 To Range("A65536").End(xlUp).Row
    Print #f, Cells(lngRow, 1)
    Next lngRow
    Close #f

    Print (as opposed to Write) will not put in extra quotes.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing/exporting text files in Excel 2000 (Excel/Word 2000)

    Thanks, Hans. That works a treat. I don't go back (in VBA programming terms only) to using the old BASIC statements, so didn't really think of it. Do you have any idea why the extra quotes are put in when using Excel statements? Thanks again, anyway.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing/exporting text files in Excel 2000 (Excel/Word 2000)

    the standard text export tries to ensure that text, numbers and dates are distinguishable, I guess.

Posting Permissions

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