• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1154
  • Last Modified:

excel macro to extract data from files in a directory

I have number of excel files in a folder. each excel file contains 2 sheets, open issue sheet and closed issue sheet. the sheets contain following data start date / end date /issue number.

i would like to know if using an excel macro,
1. combine all excel files => a final new workbook with 2 sheets (opne/closed issue sheet).
2. the sheets should only contain issue numbers,start date, end date( present date will be end date for open issues)...and Number of days/issue opened, in a separate column. the issue information/decription shouldnt been extracted...only issue number's/sart.emd date will be extracted from all excel sheets.

i will add 2 files for sample excel sheets that i will be puting in the folder.
links to the files
View all files for Question ID: 22714894
https://filedb.experts-exchange.com/incoming/ee-stuff/4209-Book1.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/4117-week1.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/4203-Book1.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/4117-week1.zip 
0
ark989
Asked:
ark989
  • 5
  • 5
1 Solution
 
inthedarkCommented:
This looks like a not very good use of Excel, when perhaps you should have been working from one place to start with.  One golden rule of systems is to only have one working set of live data.

It yould almost be quicker to write a little app. that puts the issues into a single database. So much cleaner and so much more powerful. Sorry this is not an answer to your question but it is the right way to go; and don'yt listen to anybody else who would argue otherwise.
0
 
nedfineCommented:
if you are willing to use a perl scrpt i can provide one.
0
 
ark989Author Commented:
i would like to use perl scripting if this can be done using it.
thanks,
ark989
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
nedfineCommented:
your requirements as i have understood. please correct me if i am wrong.

1) you need to open all the excel files in a folder and combine them into one file with two sheets
2) should pull columns A,C and H from sheet1 and sheet2 of all files.

you mentioned something about the number of days, could you please explain that?
0
 
ark989Author Commented:
No of days(the issue has been opened) for  each issue should be calculated (=end date- start date) and the value should be  put in a different column in each sheet. (column title = no of days)
thanks
0
 
nedfineCommented:
please try this script and see if this is what you want. .(the number of days calculation not done.)
I am looking for a way to find the number of days.
Please change the directory name where the excel files are present.

#The ouptut will be in my documents
$req = "D:/Excel/files"; #where the excel file are present
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Time::Local;
 @dayofweek = (qw(Sunday Monday Tuesday Wednesday Thursday Friday Saturday));
 @monthnames = (qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec));
 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday);
 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = localtime();
 $year += 1900;
 $sys_date =  "$wday\/$mday\/$year";
  $Win32::OLE::Warn = 3;                                
 #get already active Excel application or open new
  $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  
$Book = $Excel->Workbooks->Add();
$row=1;
$Sheet = $Book->Worksheets(1);
$Sheet2 = $Book->Worksheets(2);

$Sheet->Range("A1:E1")->Font->{ColorIndex} =5;
$Sheet->Cells($row,1)->{'Value'} = "ISSUE NUMBER";
$Sheet->Cells($row,2)->{'Value'} = "DATE";
$Sheet->Cells($row,3)->{'Value'} = "CLOSURE DATE";


$Sheet2->Range("A1:E1")->Font->{ColorIndex} =5;
$Sheet2->Cells($row,1)->{'Value'} = "ISSUE NUMBER";
$Sheet2->Cells($row,2)->{'Value'} = "DATE";
$Sheet2->Cells($row,3)->{'Value'} = "CLOSURE DATE";
$row++;
opendir(DIR ,"$req") or die "cannot open $req";
 while (defined($doc = readdir(DIR)))
 {
      chdir($req);
      if($doc eq "\." || $doc eq "\..")
      {
                 #do nothing;
      }
      else
      {
           if ($doc =~/\.xls/)
           {
             print "\n$doc";
            $Book3 = $Excel->Workbooks->Open("$req/$doc");
            $LastRow = $Sheet->UsedRange->Find({What=>"*",SearchDirection=>xlPrevious,SearchOrder=>xlByRows})->{Row};
             print "\n $LastRow";
           $Sheet3 = $Book3->Worksheets(1);
           $Sheet4 = $Book3->Worksheets(2);
           $row1=5;
           while($row1<100)
           {
             $no = $Sheet3->Cells($row1,1)->{'Value'};
             if($no=~/\S+/)
             {
              $Sheet->Cells($row,1)->{'Value'} = $Sheet3->Cells($row1,1)->{'Value'};        
              $Sheet->Cells($row,2)->{'Value'} = $Sheet3->Cells($row1,3)->{'Value'};        
              $Sheet->Cells($row,3)->{'Value'} = $sys_date;
             }
             $no = $Sheet4->Cells($row1,1)->{'Value'};
             #if($no=~/\S+/)
             {
              $Sheet2->Cells($row,1)->{'Value'} = $Sheet4->Cells($row1,1)->{'Value'};        
              $Sheet2->Cells($row,2)->{'Value'} = $Sheet4->Cells($row1,3)->{'Value'};        
              $Sheet2->Cells($row,3)->{'Value'} = $Sheet4->Cells($row1,8)->{'Value'};        
              $row1++;
              $row++;
             }
              
           }
           $Book3->Close;
          }
          else
          {    
           #do nothing;
          }
      }
     
  }
  $Book->SaveAs('wk.xls');
  $Book->Close;

0
 
ark989Author Commented:
Nedfine, Thanks very much .....i get the following errors when i rn the script. i changed the directory to location of the excel files. can you please debug the errors. i copy pasted the compilation errors below.i havent changed any part of the code except the directory of excel files.
------errors---------
Scalar found where operator expected at script.pl line 11, near "$sys_date =  "$
wday"
  (Might be a runaway multi-line "" string starting on line 2)
        (Missing operator before $wday?)
Backslash found where operator expected at script.pl line 11, near "$wday\"
        (Missing operator before \?)
Backslash found where operator expected at script.pl line 42, near "if ($doc =~/
\"
  (Might be a runaway multi-line // string starting on line 11)
        (Do you need to predeclare if?)
syntax error at script.pl line 11, near "$sys_date =  "$wday"
syntax error at script.pl line 72, near "}"
Execution of script.pl aborted due to compilation errors.
0
 
nedfineCommented:
i copied the same script and when i execute its working :(
can you paste the code that you tried to run ..  so that i can have a look..
0
 
ark989Author Commented:
code:

#The ouptut will be in my documents
$req = "C:\Documents and Settings\arkovvur\Desktop\week1\"; #where the excel file are present
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Time::Local;
 @dayofweek = (qw(Sunday Monday Tuesday Wednesday Thursday Friday Saturday));
 @monthnames = (qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec));
 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday);
 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = localtime();
 $year += 1900;
 $sys_date =  "$wday\/$mday\/$year";
  $Win32::OLE::Warn = 3;                                
 #get already active Excel application or open new
  $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  
$Book = $Excel->Workbooks->Add();
$row=1;
$Sheet = $Book->Worksheets(1);
$Sheet2 = $Book->Worksheets(2);

$Sheet->Range("A1:E1")->Font->{ColorIndex} =5;
$Sheet->Cells($row,1)->{'Value'} = "ISSUE NUMBER";
$Sheet->Cells($row,2)->{'Value'} = "DATE";
$Sheet->Cells($row,3)->{'Value'} = "CLOSURE DATE";


$Sheet2->Range("A1:E1")->Font->{ColorIndex} =5;
$Sheet2->Cells($row,1)->{'Value'} = "ISSUE NUMBER";
$Sheet2->Cells($row,2)->{'Value'} = "DATE";
$Sheet2->Cells($row,3)->{'Value'} = "CLOSURE DATE";
$row++;
opendir(DIR ,"$req") or die "cannot open $req";
 while (defined($doc = readdir(DIR)))
 {
      chdir($req);
      if($doc eq "\." || $doc eq "\..")
      {
                 #do nothing;
      }
      else
      {
           if ($doc =~/\.xls/)
           {
             print "\n$doc";
            $Book3 = $Excel->Workbooks->Open("$req/$doc");
            $LastRow = $Sheet->UsedRange->Find({What=>"*",SearchDirection=>xlPrevious,SearchOrder=>xlByRows})->{Row};
             print "\n $LastRow";
           $Sheet3 = $Book3->Worksheets(1);
           $Sheet4 = $Book3->Worksheets(2);
           $row1=5;
           while($row1<100)
           {
             $no = $Sheet3->Cells($row1,1)->{'Value'};
             if($no=~/\S+/)
             {
              $Sheet->Cells($row,1)->{'Value'} = $Sheet3->Cells($row1,1)->{'Value'};        
              $Sheet->Cells($row,2)->{'Value'} = $Sheet3->Cells($row1,3)->{'Value'};        
              $Sheet->Cells($row,3)->{'Value'} = $sys_date;
             }
             $no = $Sheet4->Cells($row1,1)->{'Value'};
             #if($no=~/\S+/)
             {
              $Sheet2->Cells($row,1)->{'Value'} = $Sheet4->Cells($row1,1)->{'Value'};        
              $Sheet2->Cells($row,2)->{'Value'} = $Sheet4->Cells($row1,3)->{'Value'};        
              $Sheet2->Cells($row,3)->{'Value'} = $Sheet4->Cells($row1,8)->{'Value'};        
              $row1++;
              $row++;
             }
             
           }
           $Book3->Close;
          }
          else
          {    
           #do nothing;
          }
      }
     
  }
  $Book->SaveAs('wk.xls');
  $Book->Close;
0
 
nedfineCommented:
change the \  to / in the path.  i think that is the problem.
0
 
ark989Author Commented:
Thanks Nedfine itworks:)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now