none
multiple workbooks from single Ws with selective data RRS feed

  • Question

  • Hello all Gurus

    PFB link for file

    https://www.dropbox.com/sh/oq8kfxony0pamm5/AABioXWk3ngE_XuAVcOWho_Ca?dl=0

    RAW file contain total 492 rows/lines

    1: sheet(CONT) will extract from RAW file with total of 190 in each sheet and save in folder with name of CONT1,2,3 and so on in format 97-2003.xls[compatibility MODE] format. (190+190+112)

    2: & same things for CCportal sheet but this sheet should save in .CSV format it should also contain data 190 in each sheet, sheet info column B to E will take from (CONT) sheet & column A info will constant, sheet name will also CCPORTAL.(190+190+112)

    This part is little tricky.!

    3: In SECH sheet will generate with the name of SECH & save in 97,2003 .XlS[compatibility MODE] format with the workbook name SECH 1, 2,3 so on, every each sheet contain 190 numbers/line/rows. column C & D will be static/constant and column A info will be extracted from CONT sheet & column B (Equip code) will mention in macro code manually (these are actual 10) like 15346,25326,25257 ect ect

    a, in raw file you could see there is  492 lines(rows) are there. b, you could take it as one set. c, if i place 1 equip id in macro code than set will carry one equip id agiant each numner. d, if i will place two equip ids in macro code than 2 set will carry two equip ids.(each set with one equip id) c, similarly if i place the 3 equip id than each set will carry each equip id.

    if total rows(lines) are more and due to more equip ids rows are crossing total count 190 than remaining line/rows will go in other file (that would be SECH 2) similarly if in SECH 2 line reaches to 190 again then SECH 3 will generate accordingly. CONT and CCPORTAL files are always will same in total count but in case of SECH files it could be more depend on raw file lines and given equip ids,

    as per main RAW file there are 492 line(rows) & if I place 10 equip id in macro code means 492*10=4920 lines/rows

    each sheet will carry on 190 rows so, 4290/190=25.8

    means total 26 sheets will generate of SECH, 25 sheets will carry 190 rows total (4750)and last one will carry 170 rows/lines I have only made one SECH file in link just for final look

    Code also remove all leading and trailing spaces and extra spaces between word in sheet. wilds %^&* in columns S,X,W,Y in data remove auto and more than 50 characters in same columns delete extra characters form right side

    Remove dashes (-) in columns N,O,P.

    CONT and SECH files will with auto fit columns.

    Nabeel




    Tuesday, February 6, 2018 4:48 PM

All replies

  • Hello Nabeel Gondal,

    >>sheet(CONT) will extract from RAW file with total of 190 in each sheet and save in folder with name of CONT1,2,3 

    What do you mean extracting from RAW file With total of 190 in  each sheet? It seems that you want to extract correspondent data from Raw File. Which rows should be extracted and how to extract the rows? What do you mean 190+190+112?

    According to your description, you will do similar thing to CCPORTAL, right? However, it seems that you did another thing to SECH. We support one issue for one thread, I would suggest you post a new thread for this part so the thread topic will be more clear.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 3:24 AM
  • Hi Terry

    thanks for taking on this, as per subject i want to do this from one file(raw file) with single macro because destination path of all files are same & it will be placed in same portal, otherwise i have to use three VBA for this with three threads.!

    there is 492 rows in raw file, i want to get first 190 rows in one sheet(individual workbook),next again 190 in second sheet(individual workbook) and remaining in third sheet(individual workbook),

    this is only sample data, numbers of rows can be more but each new workbook(file) will contain only data 190 rows.

    as per raw data file, below are individual file

    CONT 1=190rows

    CONT 2=190rows

    CONT 3=112rows

    190rows+190rows+112rows=492rows

    i have made sample for this, 

    Nabeel Gondal

    Wednesday, February 7, 2018 5:21 AM
  • Hello,

    Do you want to export data from RAW file to existing CONT/CCPORTAL/SECH sheet and then save them as CONT1,CONT2,CONT3/CCPORTAL1,CCPORTAL2...? Or do you want to generate the CONT1,CONT2,CONT3/CCPORTAL1,CCPORTAL2... from RAW file directly without using other files?

    Sometimes you said specific column data will constant, such as fro CCPORTAL, "column A info will constant".

    Where does the data in column A come from?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 9, 2018 7:28 AM
  • Hi Terry

    sorry for misclarity

    i want to generate these files form raw file, raw file will remain as it is.!

    i mean to say "constant" that this data or column will not in raw file. macro will make this columns in sheets.other data will come from raw file.!

    in CCPORTAL file column A "CENTRAL-1" will auto come from macro similarly for SECH files column B,C,D,E will auto come through macro, this is not exist in raw file..!

    Nabeel

    Friday, February 9, 2018 10:06 AM
  • please any one can help me

    Nabeel 

    Wednesday, February 28, 2018 8:00 AM