Keith Joubert logo - home.

Home
Contact

Binary Search
Sort Multiple Columns
Filing Functions
Extracting Excel data
Read and Write .csv files
Show Progress Messages
Send Automatic Email
Running under .hta
Setting Folder Attributes
Long-script warnings
Desktop Shortcut
ADODB & SQL queries


 

 
EXTRACTING  Excel  DATA

If you need to extract data from Excel files/sheets, this can be carried out  with ActiveX and Javascript.  Excel needs to be on the system (PC)  on which the program is running. 

First, a short example of how to extract a single sheet, the first one, from a single .xls file:


function GetXLS(fromName,toName){
  var xl=new ActiveXObject("Excel.Application");
  xl.Visible = false; // this results in Excel running in the background
  xl.DisplayAlerts=false; // this eliminates Excel alerts.
  xl.Workbooks.Open(fromName);
  xl.ActiveWorkbook.SaveAs(toName,6); // 6 is for comma-delimited file [ ,"" ]
  xl.ActiveWorkbook.Close;
  xl.DisplayAlerts=true;
  xl=null;
}

If you call the above function many times in order to read a number of sheets, you will note that considerable time is taken.  This is because Excel is loaded each time.  To obviate this, you need to minimise opening Excel.

The following example shows how to extract data from two .xls files.  Each file has several sheets, of which four have data we need.  Note, for clarity, the file paths are not shown in the code below.

//Declare an input Array: 
var fsNames=new Array(['f-1.xls','Sales'],['f-1.xls','Customers'],['f-2.xls','Invoices'],['f-2.xls','Statements']);

//Declare an output Array: 
var outNames-new Array('out-1.csv','out-2.csv','out-3.csv','out-4.csv');

//Each sheet is read and saved as a .csv file.  This is then read into an array by GetPlainData() - see Read/Write .csv files

 
function GetXLS(fsNames,outNames){
   var len=OutNames.length;
   var xl=new ActiveXObject("Excel.Application");
   xl.Visible = false;
   xl.DisplayAlerts=false;
   xl.Workbooks.Open(fsNames[0][0]);

   var wb=xl.ActiveWorkBook.WorkSheets(fsNames[0][1]);
   wb.Activate;
   gpath=outNames[0];DeleteFile(); // see Filing Functions (NB: gpath is global)
   xl.ActiveWorkbook.SaveAs(gpath,6);
  
   wb=xl.ActiveWorkBook.WorkSheets(fsNames[1][1]);
   wb.Activate;
   gpath=outNames[1];DeleteFile();
   xl.ActiveWorkbook.SaveAs(gpath,6);

   xl.ActiveWorkbook.Close; // close the first file
   xl.Workbooks.Open(fsNames[2][0]);
  
   wb=xl.ActiveWorkBook.WorkSheets(fsNames[2][1]);
   wb.Activate;
   gpath=outNames[2];DeleteFile();
   xl.ActiveWorkbook.SaveAs(gpath,6);
  
   wb=xl.ActiveWorkBook.WorkSheets(fsNames[3][1]);
   wb.Activate;
   gpath=outNames[3];DeleteFile();
   xl.ActiveWorkBook.SaveAs(gpath,6);
  
   xl.ActiveWorkbook.Close;
   xl.DisplayAlerts=true;
   xl=null;
}

The data from the four sheets from two files is now in the outNames files.  These can be read by GetPlainData(), and can be manipulated as desired.

Note:  one can read Excel directly into arrays, but, unless one knows the structure of each sheet, one has to use large multidimensional arrays, which will take time to fill.   For most purposes, the .csv file saving will be beneficial, in any event.

TopTop