Extracting Excel data into new fileClosed

Question

Hello,

I have an Excel like below. Project Code, Site, Donor and Partner are the heading. 

Project Code     Site        Donor         Partner
ECON100          Jakarta     Worldbank     Yayasan Serasi
HEAL230          Medan       JICA          Yayasan Serasi
INFR371          Bandung     APBN          Intekbis ITB


I need to copy rows containing Partner: Yayasan Serasi into new file named yayasanserasi.xls, and row containing Partner: Intekbis ITB into another separate file named intekbisitb.xls. Such row or rows on the each of new files must be under the same the heading like the original table:

In yayasanserasi.xls

Project Code     Site        Donor         Partner
ECON100          Jakarta     Worldbank     Yayasan Serasi
HEAL230          Medan       JICA          Yayasan Serasi


and in intekbisitb.xls

Project Code     Site        Donor         Partner
INFR371          Bandung     APBN          Intekbis ITB


I have like 2700 rows, so that doing copy-pasting seems too overwhelming. Please help me with this matter. Thank you and my apologies for my bad English.

Regards,
Gloria

venkat1926 · Answer

you have two thousand rows. how many UNIQUE partners (that is how many partner files ) are necessary? the complexity or otherwise of  the macro depends upon this. 

also are the main file and partner files in the same path.

gloriaagustyaningrum · Answer

Dear venkat1926,

Unique partner is 240. There should be 240 new files created on the fly.

I got a macro that can be a similiar workaround at https://www.pcreview.co.uk/threads/split-data-to-different-excel-files.3828059/ (see post from someone named Joel). In my case, "Partner" (or "Department" in that example) is column G. I tried changing "F" in that example to "G" but no luck so far.

I checked my sheet and found that some of "Partner" are actually more than one, e.g. Intekbis ITB; Yayasan Nurani Dunia in a cell. I changed the ";" with "and" just to check if character ";" may be the problem but it didn't workout.

Yes, they are in same path.

Let me know what you think.

Thanks!
Gloria

venkat1926 · Answer

CONSIDER THESE POINTS

you have 240 unique names of partners. That means you have 240 workbooks in additon to the main database. If you keep all the destination files open the writing of the macro will be less complex
what the macro can do is 
1. find the unique names of partners in column D in the main database using advance filter  and park it somewhere 
2. auto filter the data for each of these 240 names and copy the filtered data.
3. open that workbooks of that name and sheet no. 1 and paste the filtered data
4. save the file and clsoe it 
5. loop it like this for all the 240 names. I do not know how long will it take because you are opening 240 files, copy paste data, save and close.

I do not know how long it will take. As far as I know copying to closed files is not possible. 

It is not the macro creation is complex but running th macro is complex.

instead of 240 workbooks will it be possible to have one worksheet for each of the names in the main database. Then you need open only one file the main database.
AS you are using excel 2007
It is better not to have two partners names in ONE cell. It will make the solution more complex.

Take into considerations in view and see whether you can design the main database
less complex.

post your comments

jatin · Answer

i have add text file data into sql and with add new column as add new text file data . how to do it into .net #

venkat1926 · Answer

sorry my knowledge of SQL is nil

Ravinder Singh Banga · Answer

Hi

Please use this code, this will help u . change code parameters according to your need.
Private Sub cmdreports_Click()
Dim wbNew As Workbook
Dim thiswb As Workbook

 
    'create folder to save files
    DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
    FolderName = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, 15) & " " & DateString
    MkDir FolderName
    i = 1
    For i = 1 To 3
    Set thiswb = ThisWorkbook
    Workbooks.Add
    ActiveSheet.Name = InputBox("Please Enter File Name for Report", "Report")
    ActiveWorkbook.SaveAs (FolderName + "\" + ActiveSheet.Name + ".xls")
    Set wbNew = ActiveWorkbook
    'Depending on file file name report is generated
    Select Case ActiveSheet.Name
    
    Case "Ravinder" -- change report name here
    thiswb.Sheets(1).Range("A1:c1").EntireColumn.Copy Destination:=wbNew.ActiveSheet.Range("A1")
    ActiveWorkbook.Close Savechanges:=True
  
    Case "Abc" ---change report name here
    thiswb.Sheets(1).Range("c1:d1").EntireColumn.Copy Destination:=ActiveSheet.Range("A1") ----- instead of entirecolumn type your range
    ActiveWorkbook.Close Savechanges:=True
  
    Case Else
    'This will delete the report when criteria is not matached
    a = ActiveSheet.Name
    MsgBox "This is not a valid Criteria for Generating Reports"
    wbNew.Close
    Kill (FolderName + "\" + a + ".xls")
     
    End Select
    Next i
    
    MsgBox " Your reports are saved in" + "  '" + FolderName + "'", vbOKOnly, "Thanks"
 

End Sub

Janib Soomro · Answer

If you guys are looking for extracting data from any website in csv/excel format, then contact me. I am a professional data miner. 

Regards, 

Janib Soomro

Extracting Excel data into new file

7 responses

Similar discussions

Subscribe To Our Newsletter!