Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Importing text (2007)

    I have a spreadsheet that I import txt file data into. I also have a pivot table in another tab - this works well.

    However the problem I'm having is, I need to delete the txt file data from the spreadsheet (as I want to use spreadsheet as a template) for use at another locations, but when I delete the data from the spreadsheet I can't import new data as the "from text" option is grayed out. I have tried removing the files using the connections option, but that does not help either.

    I hope this make sense - Any suggestions?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text (2007)

    I think you need to leave at least one row intact. Maybe just the header row.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    I did try that and it will let me import from a file, but I need to import from numerous txt files and the cells below the imported txt still have the "from text" button grayed out.

    Any other suggestions

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can only import one textfile at the time into a data table. If you need to import multiple tables, you need some VBA code that does them one at the time and copies their results somewhere else.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks for the reply.

    I am only importing one txt file at a time. The problem is that the first cell after the txt has been imported is grayed out.

    I have attached an example. As detailed above I want to use this as a template, if I delete all the data imported from txt files and then try to re import txt from another source, starting at A3 - the "from txt" button is grayed out.

    So I need to know if there is away to remove all reference to the txt already imported to allow fresh data to be imported in its place - I have tried removing the txt file from the 'Connections" button, but without success.

    I hope this makes sense.
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='verada' post='762065' date='27-Feb-09 00:42']So I need to know if there is away to remove all reference to the txt already imported to allow fresh data to be imported in its place - I have tried removing the txt file from the 'Connections" button, but without success.[/quote]
    First, set the overwrite option: select the 4th row and click on the data properties button (forgot where it is in Excel 2007). Then select the option that says "Overwrite existing cells with new data, clear existing cells".
    Then you can just hit the refresh button and select a new file, the previously imported text will be overwritten.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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