Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    File Import (2003 SP2)

    I have data in MHT files (Microsoft's format for saving a browser's display of a website in one file).

    Excel opens these very well.

    But, I have a lot of files (50+). My plan is to get them into ranges of worksheets and then gather the data I want into a final worksheet.

    My main sticking point is finding out if Excel can open multiple MHT files in the same worksheet - say the first one in A1.H50, a second in A51.H100, and so on.

    I am also looking for general advice and strategies for doing this (it seems a lot easier than writing a script in something like PERL to parse out only the data I need).

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Import (2003 SP2)

    A less elegant - but perhaps quicker - strategy could be to assemble a workbook linking to each of the 50 files. Then, apply Edit|Paste Special|Values to remove the Links. HTH
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File Import (2003 SP2)

    Hi Boobounder

    I use Cognos reporting that creates mht files for output from a contiguous range. I found that due to the type of data extract the data can be londer than the 65535 records and so I have had to think of alternatives. You can use:

    1) Data | Import External Data | Import data but that may be a bit arduous with 50 files
    2) Do the same above and use a piece of VB to "grab" them from a folder and create a contiguous list
    3) Get the file and have one long html file, copy and paste (done that works fine)


    Just ideas
    Jerry

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Import (2003 SP2)

    I have never used an MHT file, so I don't know exactly what Excel does with them. However, based on experience with other file types, Excel is probably going to open each one in a separate workbook and you would need VBA code to copy and paste the data where you want it. If you could attach one of the files to a messeage, then we could take a look at helping you do that. Change the .MHT extension to .TXT if the Lounge software won't let you upload a .MHT file.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Import (2003 SP2)

    "Apply Edit|Paste Special|Values| to what exactly?

    I have a test worksheet with links in it, but I can't see what to do.

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Import (2003 SP2)

    My files are smaller, and fairly regularly sized.

    I'm going in this direction because I think, if I can get them into one workbook, the rest will be easy.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Import (2003 SP2)

    Aargh. I see now that it opens everything into a workbook not a worksheet. That's a problem.

    A sample MHT file is attached.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Import (2003 SP2)

    How are you opening that file so that it is useful. If I open it in Excel 2000, I just get it opened like a text file with nothing much useful. Maybe someone with Excel 2003 will have to give it a try.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File Import (2003 SP2)

    I may be missing something here but this file will not open in Excel....it seems to have MIME headers. How are you recieving this data file?
    Jerry

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

    Re: File Import (2003 SP2)

    To Legare and Jezza:

    For security reasons, the Lounge software replaced all instances of < in the attachment with <!
    It also inserted a few lines of comments at the top of the file.
    You'll have to open the file in Notepad or another text editor, remove the inserted lines and replace all instances of <! with < to make it a valid .mht file again.

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Import (2003 SP2)

    >I can't see what to do

    I should have been more explicit: Edit|Copy the area with the Links, then apply Edit|Paste Special|Values to precisely the same area.

    To Link a large number of cells across a Range:<UL><LI>Link one cell<LI>select the same cell<LI>Tap F2 once<LI>Tap F4 three times. The Link Formula will now no longer have any $ signs - making the Link "Relative" instead of "Absolute" (the hard-coded default).<LI>Position the cursor over the bottom right hand corner of the cell until a small + appears<LI>Drag the + sign horizontally as wide as you wish - the relevant horizontal cells will now Link across the Range<LI>Repeat the dragging vertically - as required[/list]HTH
    Gre

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Import (2003 SP2)

    I still can't get it to open in Excel 2K. First, I can't find .MHT as a supported file type in the Files of Type list in the Open dialog, so I have to change to All files. Then Excel goes to the delimited text file dialog box which puts garbage in the worksheet. Was .MHT file support added in a later version?
    Legare Coleman

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

    Re: File Import (2003 SP2)

    > Was .MHT file support added in a later version?

    Apparently. In Excel 2002, it is one of the default file types in "All Microsoft Excel Files" in the File | Open dialog - see screenshot below.

  14. #14
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Import (2003 SP2)

    I am still working on the first part.

    Your first post was clear, but I couldn't get it to work. I still can't.

    I have attached a spreadsheet with links to 2 MHT files. The links are live. But ... the copy and paste special doesn't seem to.

    Your suggestion seems simple, but I am missing something:

    1) I click in the cell, but not on the hyperlink.
    2) I do CTRL-C to copy
    3) I do Alt-E-S-V to paste values ... and I get nothing.

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Import (2003 SP2)

    Its not there for me, although I can't find a way to see that far to the right in the types list. Someone else will have to take a look at this.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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