+ Reply to Thread
Results 1 to 3 of 3

Extracting Multi-sheet Common Range To (stacked) .txt

  1. #1
    Registered User
    Join Date
    06-20-2008
    Posts
    3

    Question Extracting Multi-sheet Common Range To (stacked) .txt

    EXTRACTING MULTI-SHEET COMMON RANGE TO (STACKED) .TXT

    Excel 2003
    Across 25 consecutive sheets I wish to File:Save:"extract" the identical range Z3..AF64 in each sheet and save this 3D range to one grand (stacked) text file (ie the range extracted from each sheet is stacked vertically in the output .txt)

    NB In Lotus 123 you simply highlight the 3D range and "Save the Selected Range" as .TXT if the File|Save Dialogue.

    Below is LotusScript code that automates this task in 123

    ' ----------------------------------------------------- AdjEdit_dump
    [Window 1].Activate
    [UR_:Z3].Select
    Selection.Reshape [UR_:Z3..CPIFD_:AF64]
    [<<C:\data\WS3\RES_AVE8_EIU.123>>].TextCodePage = $DOS
    Selection.RangeExtract "C:\clients\EIU\RES_dmp\EIU_AdjEdit_dump.TXT",,"Text (TXT)",False,"",$ExtractValuesAndFormulas
    ' -------------------------------------------------------

    Any thoughts on how to achieve the above in Excel 2003 appreciated.
    Are there any add-ins to achieve this (simple !) task in Excel or will it require VBA code ?

  2. #2
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762

    macro

    Hi
    Try the following macro codes
    Please Login or Register  to view this content.
    run the macro. It inserts a sheet called consolidated and collates data from Z3:AF64 of all sheets and saves it as text file
    Ravi

  3. #3
    Registered User
    Join Date
    06-20-2008
    Posts
    3

    Run Time Error !

    Ravi

    Most Kind

    Seems to be a Run Time error 1004 on following line :

    Worksheets("consolidated").SaveAs Filename:="C:\DATA\WS3\AdjEdit_Ravi.txt", FileFormat:=
    _xlText, CreateBackup:=False

    Anything obvious ?

    Its a bit problematic inserting a new sheet:
    -can it be inserted as the first (or last) sheet ?
    - can this new sheet be deleted after .TXT file dumped (as need a clean txt dump everytime to completely overwrite the previous dump)

    Not entirely sure if the text file will be stacked properly !
    ie need to ensure the rectangular shape/range is manintained in the txt dump and that it doesnt just dump each cell on top of the last cell in one "continuous column" text file !

+ 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